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]

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

 

[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 Autpen 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 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

 

[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




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]

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

 

[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



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]

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