Macro button not appearing when workbook opened?

[Simon Lloyd]

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").OnAction = "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

[Jim Cone]

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").OnAction =
"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]

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

[Jim Cone]

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

[Simon Lloyd]

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

[Jim Cone]

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" <[email protected]> wrote in message
news:[email protected]...

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

[Simon Lloyd]

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

[Jim Cone]

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

[Simon Lloyd]

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]

Jim, Thanks for your help it worked a treat, dont know why i didnt think of renaming it!

Cheers,
Simon