MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Beginner's problems with Workbook_Open


Posted by Richie on June 30, 2001 12:56 AM

Dear all,

I've just written my first spreadsheet that makes use of macros (I told you it was a beginner's problem!).

One of the macros is set-up to display a message box when the workbook is first opened. The macro works fine when the workbook is opened. However, my problem is that once that workbook is closed, if I open another workbook the macro is executed again! The only way around this is to close Excel and start again. The macro is stored in the 'ThisWorkbook' section and I selected 'Workbook' from the drop-down menu in the VBE (which then automatically provided the basis for the Workbook_Open sub.

I've looked in the help section and the previous posts on this board but can't find the answer. Any ideas?

Thanks in advance. Richie.


Posted by Cory on June 30, 2001 7:44 AM

Are you saying that when you open "any" workbook this macro is run? Not just when you open the workbook you made the macro in?

I just replicated what you described and the macro only ran when I opened that particular workbook. Do you not want this to happen?

Posted by Richie on June 30, 2001 8:32 AM

Cory,

Thanks for the prompt response. Yes, that's exactly what I'm saying.

I only have a limited amount of VBA knowledge at the moment, but I know that it should work just on the specific workbook containing the macro. Unfortunately it works on any workbook opened after that one as well.

It's driving me crazy! Any ideas?

Richie

Posted by Joe Was on June 30, 2001 8:34 AM

Auto Open macro call

Some code has things in it that gives some of the Auto abilities of Excel a hard time. In these cases you can try to use a Tab Sheet - View code macro to call a different macro from the module (Macro - Macros).

Try Right clicking the sheet tab of your application, select View code, then copy/paste this:

Private Sub Workbook_Open()
Run("your macro name")
End Sub

Then in the regular macro section (Macro - Macros)
put your Msg box macro, name it the name you used in the "your macro name" above.

Now when you open your application, hopefully the Open Event will call your macro and work. This will make your macro only available to the application the real macro is in. JSW

Posted by Joseph S. Was on June 30, 2001 8:45 AM

If this is not a help post your code, seeing really helps!

If your code is still opening in other application tell us where you put the code and show us the code!
You may have put the code in or as a Workbooks level code?

Or, your code is referenced at a file level, some times just changing the way you reference something else will affect the macro. Once again we need to see the code!

To copy your code to this note:
Open your macro in edit, Block and copy the code then jump back to this note, and select edit paste from the browser to past the cursor location in the comments box.

Posted by Richie on June 30, 2001 10:20 AM

Thanks Joe & Cory

Joe & Cory,

Thank you both for your input - I've now made some progress and should be able to resist swearing at my computer for the rest of the day!

What I excluded from my original message (sorry I didn't realise that it was relevant) was that the macro calling the messagebox routine also called a routine that prevented the Page_down key from working (a routine that I cut and pasted from the Web). I have now removed the Page-down routine and everything works as it should.

I list below the relevant routine in case you are able to explain why it was having such an odd effect. If you can't, don't worry - I'm just glad the rest of the stuff is now OK. Once again, thank you both very much for your help - isn't this message-board great?


Sub Set_PageDown()
'
' Trap Page Down key
'
Application.OnSheetActivate = "TrapKey"
End Sub

Sub TrapKey()
If ActiveWorkbook Is ThisWorkbook Then _
Application.OnKey "{PgDn}", "DownOne" Else _
Application.OnKey "{PgDn}"
End Sub

Sub DownOne()
If Not ActiveCell.Offset(1, 0).EntireRow.Hidden Then _
ActiveCell.Offset(1, 0).Activate
End Sub


Posted by Ivan F Moala on June 30, 2001 4:53 PM

Re: Thanks Joe & Cory

Richie
No need to delete the routine....especially if
you need it.....just reset the onkey function
before closing the workbook. eg

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{PgDn}"=""
End Sub

Ivan

Trap Page Down key