Auto run dialog box? Please Help!

[Jamie13]

Hi there,

I have created a new dialog box worksheet which I want to use as an
index point for multiple worksheets in the same book. I have created
the various macros for each click of a different button.

The PROBLEM!......

How do I get this dialog box to automatically pop up and run when
someone opens the excel workbook or returns to that particular
worksheet? I dont want to press the run button everytime someone opens
it. I know it proberly a really simple solution but I haven't got a
clue.

Many Thanks

J

 

[Norman Jones]

Hi Jamie,

Try:

'=============>>
Private Sub Workbook_Open()
Me.Sheets("iMySheet1").Select '<<==== CHANGE
End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)

Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

Change MySheet to reflect the name of the sheet of interest.

 

[Jamie13]

Hey Norman Thanks, I have done this however it still doesn't auto load
the "run the dialog box" when the excel workbook is opened. This is my
main aim! Any more help would be greatly appreciated!

Thank you!

 

[Norman Jones]

Hi Jamie,

I am not sure what "run the dialog box" means!

If you want to activate a specific sheet each time that the workbook is
opened, try the suggested code.

If you want to run a specific macro each time that the workbook is opened,
try something like:

'=============>>
Private Sub Workbook_Open()
Call MyMacro '<<==== CHANGE
End Sub
'<<=============

If this does not assist, please explain in more detail your intentions.

 

[Norman Jones]

Hi Jamie,

If you have inserted an Excel 5 dialog sheet, try:

'=============>>
Private Sub Workbook_Open()

DialogSheets("Dialog1").Show
End Sub
'<<=============

Change Dialog1 to the name of the dialog sheet.

 

[Jamie13]

Hi Norman thanks for your quick response,

I am using excel 2003 and after right clicking on a tab at the botto
of the screen I have inserted an MS EXCEL 5.0 Dialog box, inserted som
buttons and some text and assigned macros to the buttons telling it t
change pages on each click. I want this dialog box to "run" in rea
time when the workbook has been opened.

It is on a seperate sheet and currently only opens in a design vie
meaing that who ever views this worksheet will have to press the "ru
dialog box" in the forms panel

 

[Norman Jones]

Hi Jamie,

Try;

'=============>>
Private Sub Workbook_Open()
DialogSheets("Dialog1").Show
End Sub
'<<=============

Change Dialog1 to the name of the dialog sheet.

 

[Jamie13]

Hey Norman cheers mate,
That's worked brilliant! The only thing is when I go back to the
"contents page" it doesn't pop up again!

 

[Norman Jones]

Hi Jamie,

That's worked brilliant! The only thing is when I go back to the
"contents page" it doesn't pop up again!

In the ThisWorkbook module, paste this additional procedure:

'=============>>
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Dialog1" Then
DialogSheets("Dialog1").Show
End If
End Sub
'<<=============

Again, change "Dialog1" to the name that you use.