AutoOpen and Workbook_Open not running VBA code - please help

[Tom Joseph]

I am trying to load a floating command bar when I open a workbook using Sub
AutoOpen() in Excel 2003. Also, Private Sub Workbook_Open() does not seem to
work either. It works when I manually run the code.

Can someone please help. Thanks.

Sub AutoOpen()

Dim c As CommandBar
Dim cb As CommandBarButton

Set c = Application.CommandBars.Add("Dashboard Controls", _
msoBarFloating, False, True)
c.Enabled = True
c.Visible = True

Set cb = c.Controls.Add(msoControlButton, 2)
cb.Style = msoButtonCaption
cb.Caption = "caption button with a long caption"

Set cb = c.Controls.Add(msoControlButton, 3)
cb.Style = msoButtonIcon
cb.Caption = "icon button"

Set cb = c.Controls.Add(msoControlButton, 4)
cb.Style = msoButtonIconAndCaption
cb.Caption = "icon and caption button"

End Sub

 

[JLGWhiz]

Try it with the delay added in. Sometimes the compiler is
too efficient. If it still does not work with a half second
delay, then change the 0.5 to 1 and try it.

Sub AutoOpen()

Dim c As CommandBar
Dim cb As CommandBarButton

Set c = Application.CommandBars.Add("Dashboard Controls", _
msoBarFloating, False, True)
s = Timer + 0.5
Do While Timer < s
DoEvents
Loop
c.Enabled = True
c.Visible = True

Set cb = c.Controls.Add(msoControlButton, 2)
cb.Style = msoButtonCaption
cb.Caption = "caption button with a long caption"

Set cb = c.Controls.Add(msoControlButton, 3)
cb.Style = msoButtonIcon
cb.Caption = "icon button"

Set cb = c.Controls.Add(msoControlButton, 4)
cb.Style = msoButtonIconAndCaption
cb.Caption = "icon and caption button"

End Sub

 

[Mike H]

Hi,

Put the code in the workbook open event and it will work, but it will only
work once.

On a second ot subsequent run it will error out becuase it will try to
create a commandbar that already exists so I would suggest you delete the
command bar on exit.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Dashboard Controls").Delete
End Sub

Mike

 

[Tom Joseph]

Hi Mike,

I tried that code in the following, which I believe is the workbook open
event and nothing was loaded. I could manually rerun it and open it, so I
know it didnt open the first time. Any thoughts or is this not the workbook
open event you mention?

Private Sub Workbook_Open()

 

[Tom Joseph]

Thanks. I wil try that.

Try it with the delay added in. Sometimes the compiler is
too efficient. If it still does not work with a half second
delay, then change the 0.5 to 1 and try it.

Sub AutoOpen()

Dim c As CommandBar
Dim cb As CommandBarButton

Set c = Application.CommandBars.Add("Dashboard Controls", _
msoBarFloating, False, True)
s = Timer + 0.5
Do While Timer < s
DoEvents
Loop
c.Enabled = True
c.Visible = True

Set cb = c.Controls.Add(msoControlButton, 2)
cb.Style = msoButtonCaption
cb.Caption = "caption button with a long caption"

Set cb = c.Controls.Add(msoControlButton, 3)
cb.Style = msoButtonIcon
cb.Caption = "icon button"

Set cb = c.Controls.Add(msoControlButton, 4)
cb.Style = msoButtonIconAndCaption
cb.Caption = "icon and caption button"

End Sub

 

[Tom Joseph]

Hi. It didnt work, even at 2 seconds. Would this problem have anything to
do with an AutoOpen sub in my personal.xls?

 

[Mike H]

Hi,

If you used my version of the code and it didn't work then I suspect you put
it in the wrong place.

Alt+F11 to open VB editor. double click 'ThisWorkbook' and paste it in on
the right.

Mike

 

[Tom Joseph]

It is working now.

I was using AutoOpen() and the correct form was Auto_Open()

Thanks for the help.

 

[Chip Pearson]

AutoOpen needs to be Auto_Open.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)