Solved: Auto_Open/Auto_Close

[mpaneitz]

I'm having problems understanding why the code that I've got doesn't run at either open or close.

This code is attached to the sheet.
____________________________________

Option Explicit
Dim oCmdPopup As CommandBarPopup
Dim oCmdButton As CommandBarButton
Dim oCmdCtrl As CommandBarControl

Sub Auto_Open()
Set oCmdPopup = CommandBars("Worksheet Menu Bar").Controls("File")
Set oCmdButton = oCmdPopup.Controls.Add(Before:=15)
With oCmdButton
.Caption = "Change Column Widths"
.FaceId = 59
.OnAction = "ShowForm"
End With
End Sub

Sub ShowForm()
frmChangeWidth.Show
End Sub
Sub Auto_Close()
Set oCmdPopup = CommandBars("Worksheet Menu Bar").Controls("File")
For Each oCmdCtrl In oCmdPopup.Controls
If oCmdCtrl.Caption = "Change Column Widths" Then
oCmdCtrl.Delete
Exit For
End If
Next oCmdCtrl
End Sub

_______________________________________
If I run the code from the view code window it does what it's supposed to do. This is exactly the code the instructor gave us for this project. What am I missing?

[lucas]

Try putting your code the the workbook open event and place it in the thisworkbook module:
Private Sub Workbook_Open()
'your code
End Sub

[lucas]

If it's a lesson you may wish to try auto open but it still needs to be in the thisworkbook module...can you post the instructions?

[mpaneitz]

Can you create Sub Auto_Open() within Private Sub Workbook_Open()?

[Bob Phillips]

Where did you store this code, which code module?

[lucas]

What version of Excel are you using...if it's a later version you are recommended to use Workbook_Open instead of Auto_Open. From the Help File version 2003

RunAutoMacros Method
Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. For new Visual Basic code, you should use the Open, Close, Activate and Deactivate events instead of these macros.

[Bob Phillips]

What version of Excel are you using...if it's a later version you are recommended to use Workbook_Open instead of Auto_Open. From the Help File version 2003

But it should still work, except in AUtomation.

[mpaneitz]

Office 2002

[mpaneitz]

It in the worksheet not the workbook

[lucas]

I agree Bob but I get this error no matter how I try to run it: object variable or with block variable not set

[johnske]

Auto macros need to be in a standard code module, not the worksheet module

[Bob Phillips]

It in the worksheet not the workbook

You can only put worksheet event procedures, and procxedures called by such procedures, in a worksheet code module. For anything els, Excel will look in a standard code module, so that is where you must store them.

[mpaneitz]

So there were 2 issues:
1) the code I posted originally has to be in a module, not workbook or worksheet.
2) I was trying to put the menu item befor #15 in the file menu. That might have been too low. I set it at before #4 and it worked consistently.

Thank you all for the messages.