Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Hi all, I have created a custom toolbar on which i have created some macro buttons they all work fine except the last one, when the workbook is opened which is stored on a network the last custom button "Area Leaders" does not appear and causes a runtime error 5, the button only appears when the workbook is opened on the machine it was created on! Hope you can help, Simon. P.S below is my auto open code Sub Auto_open() Dim msgentry As String ActiveSheet.Protect With Application CommandBars("Reviewing").Visible = False CommandBars.ActiveMenuBar.Enabled = False CommandBars("Formatting").Visible = False CommandBars("Standard").Visible = False CommandBars("matrix").Visible = True CommandBars("Menu1").Visible = True CommandBars("matrix").Controls("Date view").OnAction = "dateview" CommandBars("matrix").Controls("skill view").OnAction = "skillview" CommandBars("matrix").Controls("update current").OnAction = "enter" CommandBars("matrix").Controls("view lock").OnAction = "viewlock" CommandBars("matrix").Controls("autofilter toggle").OnAction = "autofiltertoggle" CommandBars("matrix").Controls("administration").O nAction = "admin" CommandBars("matrix").Controls("Deactivate/Activate").OnAction = "enevents" CommandBars("matrix").Controls("Area Leaders").OnAction = "ALbutt" DisplayFullScreen = False DisplayFormulaBar = False DisplayStatusBar = False End With msgentry = "Welcome to Plant Matrix" & vbCrLf & _ "Any Problems or Queries, Contact Training Department" MsgBox msgentry, vbOKOnly, "Plant Matrix" End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=501430 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Simon,
Does the problem line have a "dot" as the first character? Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Simon Lloyd" wrote in message Hi all, I have created a custom toolbar on which i have created some macro buttons they all work fine except the last one, when the workbook is opened which is stored on a network the last custom button "Area Leaders" does not appear and causes a runtime error 5, the button only appears when the workbook is opened on the machine it was created on! Hope you can help, Simon. P.S below is my auto open code Sub Auto_open() Dim msgentry As String ActiveSheet.Protect With Application CommandBars("Reviewing").Visible = False CommandBars.ActiveMenuBar.Enabled = False CommandBars("Formatting").Visible = False CommandBars("Standard").Visible = False CommandBars("matrix").Visible = True CommandBars("Menu1").Visible = True CommandBars("matrix").Controls("Date view").OnAction = "dateview" CommandBars("matrix").Controls("skill view").OnAction = "skillview" CommandBars("matrix").Controls("update current").OnAction = "enter" CommandBars("matrix").Controls("view lock").OnAction = "viewlock" CommandBars("matrix").Controls("autofilter toggle").OnAction = "autofiltertoggle" CommandBars("matrix").Controls("administration").O nAction = "admin" CommandBars("matrix").Controls("Deactivate/Activate").OnAction = "enevents" CommandBars("matrix").Controls("Area Leaders").OnAction = "ALbutt" DisplayFullScreen = False DisplayFormulaBar = False DisplayStatusBar = False End With msgentry = "Welcome to Plant Matrix" & vbCrLf & _ "Any Problems or Queries, Contact Training Department" MsgBox msgentry, vbOKOnly, "Plant Matrix" End Sub -- Simon Lloyd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Hi and thanks for replying, The code doesnt break normally, when the workbook opens it goes throug the auto open but then stops with a Runtime 5 fault the only butto available on the pop up box to click is the End button, the code i stopping at the last button in my Auto Ope CommandBars("matrix").Controls("Area Leaders").OnAction ="ALbutt" if i open the workbook on a computer that has never opened the workboo before the (i.e before the extra button was added) then all the button i created appear, but if i open the workbook on a computer that ha opened it it comes up with the runtime error and does not create th button but all other buttons are there as normal and operate fine. Its Driving me mad because i cant get round it and i keep getting phon callssaying the program isnt working properly so any guidance or hel you can give will be greatly appreciated. Regards, Simo -- Simon Lloy ----------------------------------------------------------------------- Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=50143 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Simon,
You didn't answer my question... The code you posted does not show any dots in front of the code lines within the With statement. "CommandBars" will not work in Class modules. You must use "Application.CommandBars". The Auto_Open command should be used in a standard module not a Class module (ThisWorkbook is a class module). Regards, Jim Cone San Francisco, USA "Simon Lloyd" wrote in message Hi and thanks for replying, The code doesnt break normally, when the workbook opens it goes through the auto open but then stops with a Runtime 5 fault the only button available on the pop up box to click is the End button, the code is stopping at the last button in my Auto Open CommandBars("matrix").Controls("Area Leaders").OnAction ="ALbutt" if i open the workbook on a computer that has never opened the workbook before the (i.e before the extra button was added) then all the buttons i created appear, but if i open the workbook on a computer that has opened it it comes up with the runtime error and does not create the button but all other buttons are there as normal and operate fine. Its Driving me mad because i cant get round it and i keep getting phone callssaying the program isnt working properly so any guidance or help you can give will be greatly appreciated. Regards, Simon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Hi Jim, I have the Auto Open in a standard module that i have called mAuto an yes the lines do have a dot before them so the offending line woul look like this if everything else was removed Wit Application.CommandBars("matrix").Controls("Area Leaders").OnAction "ALbutt", i'm not back at work until tomorrow (i'm in the UK) so can work on the workbook until then, but it is strange how it works on th PC you create the buttone on but then not on others unless they hav never opened the workbook! Thanks, Simo -- Simon Lloy ----------------------------------------------------------------------- Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=50143 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Simon,
1. If you manually create a toolbar and attach it to a workbook then, you only need to make it visible when the workbook is opened (plus maybe position it) and delete it when the workbook is closed. 2. If you are creating a toolbar each time the workbook is opened then make sure you delete it (using on error resume next) just before you create it again. Also, delete it when the workbook is closed. 3. Your code shows the OnAction assignment for the buttons but nothing referring to the creation of the toolbar? Is it an attached toolbar or do you create it each time? Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Simon Lloyd" wrote in message ... Hi Jim, I have the Auto Open in a standard module that i have called mAuto and yes the lines do have a dot before them so the offending line would look like this if everything else was removed With Application.CommandBars("matrix").Controls("Area Leaders").OnAction = "ALbutt", i'm not back at work until tomorrow (i'm in the UK) so cant work on the workbook until then, but it is strange how it works on the PC you create the buttone on but then not on others unless they have never opened the workbook! Thanks, Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=501430 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Hi Jim, The toolbar i created is attatched (using tools, customise, attatch click the toolbar i created an clicked copy) so that when the workboo is opened its always there (funny thing is i just opened a blan workbook to check the above sequence i used to attatch the toolbar an the option to attatch the toolbar were discussing is there, shouldnt i only be available in the workbook its attatched to? also the workbook i stored on a network drive not C drive),i have created custom icons fo the buttons and have no idea how to create these or construct a toolba on workbook open (auto open), i do have some vba knowledge but its no in-depth. I can mail you the workbook if you like....it's 4meg! so you can se whats happening, but when you first open it the toolbar will appea with all the buttons because you have never opened it before, i fin the problem only exists if the user has previously opened the workboo before i added the extra button to the toolbar!. Regards, Simon e-mail address simonwlloydATmsnDOTcom I will be at home at 21:00 GM -- Simon Lloy ----------------------------------------------------------------------- Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=50143 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Hello Simon,
When creating and attaching a custom toolbar to a workbook, you only get one try. If you modify the toolbar, you must detach the old toolbar and attach the modified one. The attached toolbar cannot be modified. I suggest starting over by... 1. detaching the toolbar 2. maybe saving the workbook 3. making sure the toolbar is exactly what you want (including specifying the macro that each button runs) 4. attach the toolbar and save the workbook. In the ThisWorkbook code module, the only code you need then is... Private Sub Workbook_Open Application.CommandBars("MyCustomName").Visible = True End Sub Private Sub Workbook_BeforeClose (Cancel As Boolean) Application.CommandBars("MyCustomName").Delete 'Yes delete End Sub Regards, Jim Cone San Francisco, USA "Simon Lloyd" wrote in message Hi Jim, The toolbar i created is attatched (using tools, customise, attatch, click the toolbar i created an clicked copy) so that when the workbook is opened its always there (funny thing is i just opened a blank workbook to check the above sequence i used to attatch the toolbar and the option to attatch the toolbar were discussing is there, shouldnt it only be available in the workbook its attatched to? also the workbook is stored on a network drive not C drive),i have created custom icons for the buttons and have no idea how to create these or construct a toolbar on workbook open (auto open), i do have some vba knowledge but its not in-depth. I can mail you the workbook if you like....it's 4meg! so you can see whats happening, but when you first open it the toolbar will appear with all the buttons because you have never opened it before, i find the problem only exists if the user has previously opened the workbook before i added the extra button to the toolbar!. Regards, Simon e-mail address simonwlloydATmsnDOTcom I will be at home at 21:00 GMT -- Simon Lloyd |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Thanks again Jim, I will try that when i get back to work, it sounds logical now you have put it that way. Thanks for your help, i will post back tomorow when done! Regards, Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=501430 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro button not appearing when workbook opened?
Jim, Thanks for your help it worked a treat, dont know why i didnt think of renaming it! Cheers, Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=501430 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to run a macro as soon a a workbook (.xls) is opened? | Excel Worksheet Functions | |||
Add-in to run macro when ANY workbook is opened? | Excel Programming | |||
Looking for a way to run a macro when the workbook is opened | Excel Programming | |||
Run a macro when a workbook is opened | Excel Programming | |||
Run a macro when a workbook is opened | Excel Programming |