Toggle custom button caption
 

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

Toggle custom button caption
 

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.

--
Regards,
Tom Ogilvy


"Tummy" wrote in message
...
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

Toggle custom button caption
 

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" wrote in message
...
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

Toggle custom button caption
 

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, _
Befo=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




-----Original Message-----
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.

--
Regards,
Tom Ogilvy


"Tummy" wrote in
message
...
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



.

Toggle custom button caption
 

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.

--

Regards,
Tom Ogilvy



"Tummy" wrote in message
...
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, _
Befo=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




-----Original Message-----
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.

--
Regards,
Tom Ogilvy


"Tummy" wrote in
message
...
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



.