Controls in Sheets (Hide etc.) in Format Menu Not appearing

[Varne]

Hi

Could someone help on this please.

I have accidently deleted Hide, Unhide, Rename and other controls in
Format>Sheets menu. I like to reinstate them through VBA. I tried through
Tools>Built In Menus but it is still empty.

Thank You
M Varnendra

 

[Varne]

Hi

I found these codes from a book but it has a few problems.

Sub ListFirstLevelControls()

Dim cbctl As CommandBar
Dim cbBar As CommandBar
Dim i As Integer
On Error Resume Next

i = 2

-----If Not isemptyworksheet(ActiveSheet) Then Exit Sub
For Each cbBar In CommandBars
Application.StatusBar = "Processing Bar" & cbBar.Name
Cells(i, 1) = cbBar.Name
i = i + 1
For Each cbctl In cbBar.Controls
------Cells(i, 2) = cbctl.Caption
cb.ctl.CopyFace
If Err.Number = 0 Then
ActiveSheet.Paste Cells(i, 3)
------Cells(i, 3) = cbctl.FaceId
End If
Cells(i, 4) = cbctl.ID
Err.Clear
i = i + 1
Next cbctl
Next cbBar


End Sub

When I try to run these codes 'sub or function not defined' coming up. I
have shown the problem lines above by -------.

How to get around this?

Could someone help?

Thank You
M Varnendra

 

[Dave Peterson]

I'd try:

Tools|Customize|Toolbars tab
select the Worksheet Menu Bar
click reset

If you've customized this toolbar, you'll have to do it again.

 

[Varne]

Hi Dave

I tried but I am not able to bring back what I have deleted.

By the way;

I have been able to run the codes I put forward for error detection. ('But
If Not isemptyworksheet(ActiveSheet) Then Exit Sub is still wrong so I took
it out)

It produced some results but there is no 'Sheet'.

Instead it has 'Ply' underwhich I found items like Rename which belong to
sheet menu.

However I like to know the codes to insert '&Rename' into sheet menu.

Do you know?

Thanks

M Varnendra

 

[Varne]

Got it.

Application.CommandBars("Sheet").Controls.Add Type:=msoControlButton, ID:= _
889, Before:=1
That is for 'Rename'

But I do not have the ID for 'Hide'

Can somebody give me the ID please?

Thanks.

M Varnendra

 

[Varne]

Hide and unhide are 890 and 891 but they did not appear in the list - my
second query - created. Vould an expert answer?

Thank You.

M Varnendra

 

[Dave Peterson]

I don't know what isemptyworksheet() does, so I don't have a guess for that.

The Ply commandbar is the dialog you see when you rightclick on a sheet tab.

This kind of line will reset the Format popup under the worksheet menu bar:
Application.CommandBars("worksheet menu bar").Controls("format").Reset

I used this to find the ids:

Option Explicit
Sub testme()

Dim ctrl As CommandBarControl
Dim cc As CommandBarControl
Set ctrl = Application.CommandBars("worksheet menu bar").Controls("format")

For Each cc In ctrl.Controls
Debug.Print cc.Caption & "--" & cc.ID
Next cc

End Sub

I use xl2003 and got these id's:

C&ells...--855
&Row--30024
&Column--30025
S&heet--30026
&AutoFormat...--786
Con&ditional Formatting...--3058
&Style...--254
Phone&tic Guide--30136

 

[Dave Peterson]

I should have gone one level deeper:

Option Explicit
Sub testme()

Dim ctrl As CommandBarControl
Dim cc As CommandBarControl
Set ctrl = Application.CommandBars("worksheet menu bar") _
.Controls("format").Controls("sheet")

For Each cc In ctrl.Controls
Debug.Print cc.Caption & "--" & cc.ID
Next cc

End Sub


&Rename--889
&Hide--890
&Unhide...--891
&Background...--952
&Tab Color...--5747

 

[Varne]

Hi Dave

Let me reply on Monday.

Thank you for your response.

M Varnendra