Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refer to a custom toolbar button?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refer to a custom toolbar button?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refer to a custom toolbar button?
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, befo=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refer to a custom toolbar button?
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I refer to a custom toolbar button?
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, befo=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding custom images to a button on custom toolbar | Excel Programming | |||
Custom button on toolbar | Excel Programming | |||
single button on a custom toolbar | Excel Programming | |||
Delete a custom button by holding down the ALT key and dragging the button off the toolbar | Excel Programming | |||
Can't delete custom button and toolbar | Excel Programming |