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

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" <(E-Mail Removed)> wrote in message
news:28ACF9F3-244D-4E51-AE58-(E-Mail Removed)...
> 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
>

Thx Tim.

-- Steve


"Tim Zych" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:28ACF9F3-244D-4E51-AE58-(E-Mail Removed)...
> > 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
> >
>
>
>

Thanks Simon.

- Steve


"Simon Lloyd" wrote:

>
> 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
> --------------------
>
>
> Steve Jacobs;240211 Wrote:
> > 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
>
>
> --
> Simon Lloyd
>
> Regards,
> Simon Lloyd
> 'The Code Cage' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
> View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67059
>
>