I have the same custom commandbar in two different spreadsheets. In
spreadsheet #40, in a submenu, the icon face (and not its caption)
displays as depressed or not depending on whether the sheet is visible
or not. I like that look - seems neater. In #39, which doesn't have a
submenu, the icon face AND caption displays as depressed or not. For
#39 I want to know how to just depress the icon face and not the
caption also. Is this possible or is it just a pecularity of a
submenu?

#40
Set IncludeExcludeSubMenu = MyBar.Controls.Add(msoControlPopup, , ,
Temporary:=True)
With IncludeExcludeSubMenu
.Caption = "Include / Exclude Pages"
End With

MakeButton ("My Sheet Name") '"This is the name on the tab

Private Sub MakeButton(SheetName)
With IncludeExcludeSubMenu.Controls.Add(msoControlButto n, , , ,
True)
.Style = msoButtonIconAndCaption
If Worksheets(SheetName).Visible < -1 Then
.Caption = "Include '" & SheetName & "'"
.FaceId = 170
.State = msoButtonDown
Else
.Caption = "Do Not Include '" & SheetName & "'"
.FaceId = 171
.State = msoButtonUp
End If
.OnAction = "'Toggle_Sheet """ & SheetName & """'"
End With
End Sub

#39
MakeButton ("My Sheet Name")

Private Sub MakeButton(SheetName)
Set SaveButton = MyBar.Controls.Add(Type:=msoControlButton)
With SaveButton
.Style = msoButtonIconAndCaption
If Worksheets(SheetName).Visible < -1 Then
.Caption = "Include '" & SheetName & "'"
.FaceId = 170
.State = msoButtonDown
Else
.Caption = "Do Not Include '" & SheetName & "'"
.FaceId = 171
.State = msoButtonUp
End If
.OnAction = "'Toggle_Sheet """ & SheetName & """'"
End With
End Sub

There is a lot more to the macro than just this code, but this is the
relevant part (I think).

Thank you

Mark
in Atlanta

Mark

I believe it is all or none.

I think your only other option would be to have two different bitmaps that
you create yourself and swap out the bitmaps.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Mark" wrote in message
om...
I have the same custom commandbar in two different spreadsheets. In
spreadsheet #40, in a submenu, the icon face (and not its caption)
displays as depressed or not depending on whether the sheet is visible
or not. I like that look - seems neater. In #39, which doesn't have a
submenu, the icon face AND caption displays as depressed or not. For
#39 I want to know how to just depress the icon face and not the
caption also. Is this possible or is it just a pecularity of a
submenu?

#40
Set IncludeExcludeSubMenu = MyBar.Controls.Add(msoControlPopup, , ,
Temporary:=True)
With IncludeExcludeSubMenu
.Caption = "Include / Exclude Pages"
End With

MakeButton ("My Sheet Name") '"This is the name on the tab

Private Sub MakeButton(SheetName)
With IncludeExcludeSubMenu.Controls.Add(msoControlButto n, , , ,
True)
.Style = msoButtonIconAndCaption
If Worksheets(SheetName).Visible < -1 Then
.Caption = "Include '" & SheetName & "'"
.FaceId = 170
.State = msoButtonDown
Else
.Caption = "Do Not Include '" & SheetName & "'"
.FaceId = 171
.State = msoButtonUp
End If
.OnAction = "'Toggle_Sheet """ & SheetName & """'"
End With
End Sub

#39
MakeButton ("My Sheet Name")

Private Sub MakeButton(SheetName)
Set SaveButton = MyBar.Controls.Add(Type:=msoControlButton)
With SaveButton
.Style = msoButtonIconAndCaption
If Worksheets(SheetName).Visible < -1 Then
.Caption = "Include '" & SheetName & "'"
.FaceId = 170
.State = msoButtonDown
Else
.Caption = "Do Not Include '" & SheetName & "'"
.FaceId = 171
.State = msoButtonUp
End If
.OnAction = "'Toggle_Sheet """ & SheetName & """'"
End With
End Sub

There is a lot more to the macro than just this code, but this is the
relevant part (I think).

Thank you

Mark
in Atlanta