Toggle custom button caption

[Tummy]

Hi group,

Using XL 97
I have created the custom menu which has a few buttons and
one of them is to show the full file path.

‘ ********************
'e) Show Full File Path
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Show Full File Path"
.FaceId = 1446
.OnAction = "Where_amI"
End With
‘ ********************

Sub Where_amI()
'*** toggles title bar between document name and full path
If ActiveWindow.Caption = ActiveWorkbook.Name Then
ActiveWindow.Caption = ActiveWorkbook.FullName
Else: ActiveWindow.Caption = ActiveWorkbook.Name
End If
End Sub

How would I toggle the caption of the button from “Show
Full File Path” to “Hide Full File Path?”


Question #2. Is there a way to make it interactive? I
don’t think there’s an After Save routine…

Thanks in advance.

Tummy

 

[Tom Ogilvy]

Sub Where_amI()
'*** toggles title bar between document name and full path
If ActiveWindow.Caption = ActiveWorkbook.Name Then
application.Commmandbars.Actioncontrol.Caption = "Hide Full File Path"
ActiveWindow.Caption = ActiveWorkbook.FullName
Else: ActiveWindow.Caption = ActiveWorkbook.Name
application.Commandbars.Actioncontrol.Caption = "Show Full File Path"
End If
End Sub

since this is a button on a commandbar, it seems like it already is
interactive.

 

[Bob Phillips]

Hi Tummy,

Q1.

sShow = "Show Full File Path"
sHide = "Hide Full File Path"
Set cbcCutomMenu = Application.CommandBars("Worksheet Menu
Bar").Controls("Tools")
With cbcCutomMenu
On Error GoTo show_it
.Controls(sShow).Caption = "&" & sHide
GoTo all_ok
show_it:
.Controls(sHide).Caption = "&" & sShow
all_ok:
On Error GoTo 0
End With

Q2.

What do you mean by make it interactive?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tummy]

Tom,


Thanks for your reply, but I am getting an error.
I should have posted the entire code. Here it is.
Add menu is in workbook.Activate
and deleteMenu - in workbook.deactivate


Sub AddMenu()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

'(1)Delete any existing one. We must use On Error Resume
next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls
("&New_Menu").Delete
On Error GoTo 0

'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")

'(3)Return the Index number of the Help menu. We can then
use _
this to place a custom menu before.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index

'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)

'(5)Give the control a caption
cbcCutomMenu.Caption = "&New_Model"

'(6)Working with our new Control, add a sub control and _
give it a Caption and tell it which macro to run
(OnAction).


'e) Show Full File Path
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Show Full File Path"
.FaceId = 1446
.OnAction = "Where_amI"
End With



End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls
("&New _Model").Delete
On Error GoTo 0
End Sub


Sub Where_amI()
'*** toggles title bar between document name and full path
If ActiveWindow.Caption = ActiveWorkbook.Name Then
' Application.Commmandbars.ActionControl.Caption = "Hide
Full File Path"
ActiveWindow.Caption = ActiveWorkbook.FullName
Else: ActiveWindow.Caption = ActiveWorkbook.Name
Application.CommandBars.ActionControl.Caption = "Show
Full File Path"
End If
End Sub

Thank you,
Greg

 

[Tom Ogilvy]

When your menu triggers its onaction macro, Who_AmI, the
CommandBars.ActionControl holds a reference to the button that was clicked
and triggered the macro. Therefore, the addition I suggested should provide
the functionality requested. It really doesn't have anything to do with
where and when the menu is created.


Change

Public Sub Who_AmI()

msgbox CommandBars.ActionControl.Caption
End Sub

and you should see the caption of the button.

I assigned this code to a commandbar button

Sub Who_AmI()
With Application.CommandBars
If .ActionControl.Caption = "Item1" Then
.ActionControl.Caption = "Item2"
ElseIf .ActionControl.Caption = "Item2" Then
.ActionControl.Caption = "Item1"
End If
End With
End Sub


and it worked fine. Each time I clicked the button, the caption changed.

So I don't know why you are getting errors.