Depress Icon Faces and not the Caption on CommandBarButton
 

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

Depress Icon Faces and not the Caption on CommandBarButton
 

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