If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Travelling Toolbars and hidden macro sheets
Hi all,
I have created a custom toolbar that I want to automatically display but only when a specific workbook is opened. I don't want it there for anything else. Any ideas?? Secondly, I have created a macro driven workbook. There are public and private macros that refer to specific sheets in the workbook and react to specific events such as save, change or open. I want to split the macros out and store them in another book - similar to an XLA. But I don't want to use an XLA as once an addin is active, it applies to all active workbooks and this can't happen. So I want to create a hidden workbook that is automatically opened by the host and is (to all intents and purposes) invisible to the novice user. I know about the XLStart folder but wondered if there was a more elegant way. Grateful for any help ... Best regards, Balsak |
#2
|
|||
|
|||
Hi
not sure why not to use an add-in? Depends on the macros you want to use. -- Regards Frank Kabel Frankfurt, Germany "Balsak" schrieb im Newsbeitrag ... Hi all, I have created a custom toolbar that I want to automatically display but only when a specific workbook is opened. I don't want it there for anything else. Any ideas?? Secondly, I have created a macro driven workbook. There are public and private macros that refer to specific sheets in the workbook and react to specific events such as save, change or open. I want to split the macros out and store them in another book - similar to an XLA. But I don't want to use an XLA as once an addin is active, it applies to all active workbooks and this can't happen. So I want to create a hidden workbook that is automatically opened by the host and is (to all intents and purposes) invisible to the novice user. I know about the XLStart folder but wondered if there was a more elegant way. Grateful for any help ... Best regards, Balsak |
#3
|
|||
|
|||
Hi,
Because an add-in would execute the event macros on every open workbook not just the one I want it to work on. Thanks. Regards, Balsak -----Original Message----- Hi not sure why not to use an add-in? Depends on the macros you want to use. -- Regards Frank Kabel Frankfurt, Germany "Balsak" schrieb im Newsbeitrag ... Hi all, I have created a custom toolbar that I want to automatically display but only when a specific workbook is opened. I don't want it there for anything else. Any ideas?? Secondly, I have created a macro driven workbook. There are public and private macros that refer to specific sheets in the workbook and react to specific events such as save, change or open. I want to split the macros out and store them in another book - similar to an XLA. But I don't want to use an XLA as once an addin is active, it applies to all active workbooks and this can't happen. So I want to create a hidden workbook that is automatically opened by the host and is (to all intents and purposes) invisible to the novice user. I know about the XLStart folder but wondered if there was a more elegant way. Grateful for any help ... Best regards, Balsak . |
#4
|
|||
|
|||
Hi
you could place the processing logic in the add-in. You have to course these macros in the workbook events of the desired workbook. The add-in events will only be called if you open the addin for example -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag ... Hi, Because an add-in would execute the event macros on every open workbook not just the one I want it to work on. Thanks. Regards, Balsak -----Original Message----- Hi not sure why not to use an add-in? Depends on the macros you want to use. -- Regards Frank Kabel Frankfurt, Germany "Balsak" schrieb im Newsbeitrag ... Hi all, I have created a custom toolbar that I want to automatically display but only when a specific workbook is opened. I don't want it there for anything else. Any ideas?? Secondly, I have created a macro driven workbook. There are public and private macros that refer to specific sheets in the workbook and react to specific events such as save, change or open. I want to split the macros out and store them in another book - similar to an XLA. But I don't want to use an XLA as once an addin is active, it applies to all active workbooks and this can't happen. So I want to create a hidden workbook that is automatically opened by the host and is (to all intents and purposes) invisible to the novice user. I know about the XLStart folder but wondered if there was a more elegant way. Grateful for any help ... Best regards, Balsak . |
#5
|
|||
|
|||
Balsak,
You could open another file using the workbook open event, and run macros from that workbook by using the Call command. As for the toolbars, simply read the message below that I've posted a few dozen times.... HTH, Bernie MS Excel MVP Here's my standard instructions/reply: The best option is to create the commandbar on the fly, when the workbook is opened, and delete the commandbar when the workbook is closed. Follow these instructions and example code. In the workbook's Thisworkbook object code module, place the following code: Private Sub Workbook_BeforeClose(Cancel As Boolean) DeleteCommandbar End Sub Private Sub Workbook_Open() CreateCommandbar End Sub Private Sub Workbook_WindowActivate(ByVal Wn As Window) On Error GoTo NotThere Application.CommandBars("My Bar").Visible = True Exit Sub NotThe CreateCommandbar End Sub Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) On Error Resume Next Application.CommandBars("My Bar").Visible = False End Sub In a regular code module, place the following: Dim myBar As CommandBar Dim myButton As CommandBarButton Sub CreateCommandbar() On Error Resume Next DeleteCommandBar Set myBar = Application.CommandBars.Add("My Bar") With myBar .Position = msoBarTop .Visible = True .Enabled = True Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23) With myButton .Caption = "Hello" .Style = msoButtonIcon .FaceId = 137 .Enabled = True .OnAction = "SayHello" End With End With End Sub Sub DeleteCommandBar() 'Delete the commandbar if it already exists On Error Resume Next Application.CommandBars("My Bar").Delete End Sub Sub SayHello() MsgBox "Hello there" End Sub You can add as many buttons or other menu items as you like. HTH, Bernie MS Excel MVP "Balsak" wrote in message ... Hi all, I have created a custom toolbar that I want to automatically display but only when a specific workbook is opened. I don't want it there for anything else. Any ideas?? Secondly, I have created a macro driven workbook. There are public and private macros that refer to specific sheets in the workbook and react to specific events such as save, change or open. I want to split the macros out and store them in another book - similar to an XLA. But I don't want to use an XLA as once an addin is active, it applies to all active workbooks and this can't happen. So I want to create a hidden workbook that is automatically opened by the host and is (to all intents and purposes) invisible to the novice user. I know about the XLStart folder but wondered if there was a more elegant way. Grateful for any help ... Best regards, Balsak |
#6
|
|||
|
|||
Bernie,
many many thanks for your help!!! It all worked a treat. Thank you!! Gratefully yours, Balsak -----Original Message----- Balsak, You could open another file using the workbook open event, and run macros from that workbook by using the Call command. As for the toolbars, simply read the message below that I've posted a few dozen times.... HTH, Bernie MS Excel MVP Here's my standard instructions/reply: The best option is to create the commandbar on the fly, when the workbook is opened, and delete the commandbar when the workbook is closed. Follow these instructions and example code. In the workbook's Thisworkbook object code module, place the following code: Private Sub Workbook_BeforeClose(Cancel As Boolean) DeleteCommandbar End Sub Private Sub Workbook_Open() CreateCommandbar End Sub Private Sub Workbook_WindowActivate(ByVal Wn As Window) On Error GoTo NotThere Application.CommandBars("My Bar").Visible = True Exit Sub NotThe CreateCommandbar End Sub Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) On Error Resume Next Application.CommandBars("My Bar").Visible = False End Sub In a regular code module, place the following: Dim myBar As CommandBar Dim myButton As CommandBarButton Sub CreateCommandbar() On Error Resume Next DeleteCommandBar Set myBar = Application.CommandBars.Add("My Bar") With myBar .Position = msoBarTop .Visible = True .Enabled = True Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23) With myButton .Caption = "Hello" .Style = msoButtonIcon .FaceId = 137 .Enabled = True .OnAction = "SayHello" End With End With End Sub Sub DeleteCommandBar() 'Delete the commandbar if it already exists On Error Resume Next Application.CommandBars("My Bar").Delete End Sub Sub SayHello() MsgBox "Hello there" End Sub You can add as many buttons or other menu items as you like. HTH, Bernie MS Excel MVP "Balsak" wrote in message ... Hi all, I have created a custom toolbar that I want to automatically display but only when a specific workbook is opened. I don't want it there for anything else. Any ideas?? Secondly, I have created a macro driven workbook. There are public and private macros that refer to specific sheets in the workbook and react to specific events such as save, change or open. I want to split the macros out and store them in another book - similar to an XLA. But I don't want to use an XLA as once an addin is active, it applies to all active workbooks and this can't happen. So I want to create a hidden workbook that is automatically opened by the host and is (to all intents and purposes) invisible to the novice user. I know about the XLStart folder but wondered if there was a more elegant way. Grateful for any help ... Best regards, Balsak . |
Thread Tools | |
Display Modes | |
|
|