How do I refer to a custom toolbar button?

[Steve Jacobs]

I'm just beginning to enter the world of VBA programming, trying to do
something in Excel.

I created a Forms-control button on a worksheet, and wrote a macro for it
within a module. (I.e., the button_click event is within a module.)

I then decided I wanted the button to be accessible and visible from every
worksheet in the workbook. Not seeing any easy or obvious way to have Excel
automatically keep the button on all worksheets (other than to manually add
it to every sheet myself, and make sure it was added programmatically if the
user added a new sheet), I created a toolbar button and associated the
toolbar button with the same macro.

Here's where I'm stumped:
If I'm in the module that contains the macro, I can enable or disable the
button by writing button1.enabled = true (or false).

From the workbook or any other sheet or module, I cannot figure out how to
identify or reference the button.

I tried creating Public functions within the module that handle the
enable/disable, but they still fail with an 'Object required' error when
called from outside of the module.

Anybody here know how I can get this working?

Thanks

 

[Tim Zych]

For an ActiveX button:

SheetObj.CommandButton1...

where SheetObj is Sheet1 (internal sheet name), Worksheets("Sheet1"), or
ActiveSheet

For a Forms button:

SheetObj.Buttons("Button1")...

 

[Simon Lloyd]

Rather than add a button which can get messy when you attach/unattac
it, you would be better off adding a neat menu item next to _F_ile, it
done like this
This code goes in the Thisworkbook module
Code
-------------------
Option Explici
Private Sub Workbook_BeforeClose(Cancel As Boolean
Application.CommandBars("worksheet Menu Bar").Controls("Run My Macro").Delet
End Su
Private Sub Workbook_Open(
With Applicatio
.CommandBars.ActiveMenuBar.Enabled = Tru
For Each c In .CommandBars("Worksheet menu Bar").Control
'deletes the menu item if it exist
If c.Caption = "Run My Macro" Then c.Delet
Next
'sets where to add the contro
Set cb = .CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, temporary:=True, ID:=2950, before:=1
'the control tex
cb.Caption = "Run My Macro
'mouse over tool ti
cb.TooltipText = "Runs my code when clicked
'your macro nam
cb.OnAction = ThisWorkbook.Name & ("!MyMacro"
cb.Style = msoButtonCaptio
End Wit
End Su
-------------------

I'm just beginning to enter the world of VBA programming, trying to d
something in Excel

I created a Forms-control button on a worksheet, and wrote a macro fo
i
within a module. (I.e., the button_click event is within a module.

I then decided I wanted the button to be accessible and visible fro
ever
worksheet in the workbook. Not seeing any easy or obvious way to hav
Exce
automatically keep the button on all worksheets (other than to manuall
ad
it to every sheet myself, and make sure it was added programmaticall
if th
user added a new sheet), I created a toolbar button and associated th
toolbar button with the same macro

Here's where I'm stumped
If I'm in the module that contains the macro, I can enable or disabl
th
button by writing button1.enabled = true (or false)

From the workbook or any other sheet or module, I cannot figure out ho
t
identify or reference the button

I tried creating Public functions within the module that handle th
enable/disable, but they still fail with an 'Object required' erro
whe
called from outside of the module

Anybody here know how I can get this working

Thank

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Steve Jacobs]

Thx Tim.

-- Steve

For an ActiveX button:

SheetObj.CommandButton1...

where SheetObj is Sheet1 (internal sheet name), Worksheets("Sheet1"), or
ActiveSheet

For a Forms button:

SheetObj.Buttons("Button1")...


--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison
Free & Pro versions

 

[Steve Jacobs]

Thanks Simon.

- Steve

Rather than add a button which can get messy when you attach/unattach
it, you would be better off adding a neat menu item next to _F_ile, its
done like this:
This code goes in the Thisworkbook module.
Code:
--------------------
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("worksheet Menu Bar").Controls("Run My Macro").Delete
End Sub
Private Sub Workbook_Open()
With Application
.CommandBars.ActiveMenuBar.Enabled = True
For Each c In .CommandBars("Worksheet menu Bar").Controls
'deletes the menu item if it exists
If c.Caption = "Run My Macro" Then c.Delete
Next c
'sets where to add the control
Set cb = .CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, temporary:=True, ID:=2950, before:=1)
'the control text
cb.Caption = "Run My Macro"
'mouse over tool tip
cb.TooltipText = "Runs my code when clicked"
'your macro name
cb.OnAction = ThisWorkbook.Name & ("!MyMacro")
cb.Style = msoButtonCaption
End With
End Sub
--------------------




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)