Auto run macro

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

Yes i know there are extensive posts on this but it must
because i'm using excel 2003.

I'm trying to get my macros to auto run when i open. My
thisworkbook page looks like this:

Private Sub Workbook_Open()
ComboBox1
End Sub

But all i get is a compile error. Any ideas?

Thanks in advance,

Jon
 
Jon,

One thing jumps off the page (so to speak).
What is ComboBox1???
Is it a sub??
If it is, try renaming it (you should shy away from
using reserved names).

John
 
Jon,

I think it's the ComboBox1 statement.

Try this:

Private Sub Workbook_Open()
MSgBox "This Worked"
End Sub

One last thought....
Are you sure you're in the Workbook code page??
Right click on the little Excel icon directly to the left of
"File" on the menu (not the icon in the very upper left).
Select View Code
That's where this code should be.

John
 
John,

Ok the script you gave me works but the other macro
doesn't do anything. Basically its a combobox and when the
excel file is opened the combobox is blank. the only way i
can get it t activate is by viewing the code and pressing
play. When i go back to the sheet with the combobox works
fine. This is really frustrating

Any ideas.

Thanks for the help i'm very greatful
Regards,

Jon
 
Jon,

Getting back to what I wrote in the first post....
What are you trying to do with the ComboBox????

If you want, send me the file directly and I'll take a look at it.
(include an explanation of what you want the combobox to do)

John
 
Jon,,

Rec'd the file, corrected it and am sending it back to you.

Explanation.......
The code that you had to populate the ComboBox was in
the Worksheet_Activate Event. Events are different in that
they run only when an "event" occurred. In your original
workbook, if you selected sheet2 and then reselected
sheet1, the ComboBox would have worked (because you
would have fired the Sheet1 Activate event).

I changed the name of that original sub from:
Private Sub Worksheet_Activate()
to
Sub SetCombo()

I then changed your WorkBook_Open event as follows:

Private Sub Workbook_Open()
Sheet1.SetCombo
End Sub

If you have any other questions or problems with it, post
back here in the ng.

John
 
John,

Thanks very much for the help. Your corrections work
exactly how i want the macro to run. However, when i apply
the same changes to the macro i get the following run time
error.

Private Sub Workbook_Open()
PramName.SetCombo
End Sub

PramName is the sheet name of where the macro is running
from.

Any ideas?

Thanks again,

Jon
 
Jon,

To fix this, you need to change:
PramName.SetCombo
to
Worksheets("PramName").SetCombo

but.......

That whole "SetCombo" macro doesn't have to
be on the sheet code. It would be better to place it
in it's own module. Makes it easier to test (you can
run it from Tools/Macro/Macros Run) and it's easier
to call from another procedure (without having to specify
the sheet name).

I've sent you another version of your workbook with these
changes.

Your original sub:
Sub SetCombo()
With Me.ComboBox1
' yada,yada,yada
End Sub

Is moved to a separate module and the first line of code
was changed to:

Sub SetCombo()
With Worksheets("PramName").ComboBox1
' yada,yada,yada
End Sub

The coding in your Workbook_Open event to run this sub:

Private Sub Workbook_Open()
SetCombo
End Sub

John
 
Brilliant.

It works a treat. Looks complicated but does the trip well.

I just hope it doesn't get more complicated when i add
drop down boxes to each cell. I prosume i dont have to add
new modules for each one? I must be able to reuse some of
the code for each combobox.

Thanks for the help,

REgards,

john
 
Back
Top