How can I make Excel display a message (dialog box) upon opening a spreadsheet?

[Phill]

Is it possble to make a dialog box pop-up when someone
opens a spreadsheet?

 

[JulieD]

Hi Phill

yes, you can get a message box (for a message) or an inputbox (if you want
to ask a question) or a customised userform to display when a workbook is
opened by putting worksheet_open code into the ThisWorkbook module of the
workbook. Note, however, they need to have their security settings set to
medium (or lower) and choose to "enable" macros (if it appears) when opening
the workbook.

sample Workbook_Open code would be as follows:
---
Private Sub Workbook_Open()
MsgBox "Hi " & InputBox("what is your name")
End Sub
---

if you'll like additional help please post back with more details

Cheers
JulieD

 

[Guest]

Thanks Julie,

Where do I go for this? (IE File, Edit, Etc) Am I somehow
making a macro run upon opening? I can't find my Que book
on Excel which would probably have this info in it. It's a
BIG book. (I think for something such as excel) Are there
links for this?

Thanks again.

 

[JulieD]

Hi

yes, basically you're making a macro to run when the workbook opens ... to
get to the ThisWorkbook module, right mouse click on one of the sheet tabs
in the workbook and choose view code - down the left hand side of the VBE
Window you should see the Project Explorer .... and your filename should be
listed in bold and brackets - if you can't see the project explorer window
choose view / project explorer
then amongs the objects under your workbook's name you'll see ThisWorkbook -
double click on it and then on the right hand side of the screen choose
Workbook from the left most drop down box and you'll get a workbook_open
procedure created for you with

sub workbook_open()

end sub

the code needs to go in here.

i'm not sure what references to give you - Chip Pearson has a good article
on events http://www.cpearson.com/excel/events.htm and a general basic
reference (but doesn't mention workbook_open macros)
http://web.archive.org/web/20031204...cid=/support/excel/content/vba101/default.asp

but if you'ld like to post back with what you want to happen when the
workbook opens we can help you with the code.

Cheers
JulieD

 

[Phil...Bob]

I made 13 cds for my nephews with about 700 song samples on
them. (I didn't think it would THAT involved!) So now I
have exported the CD track info to txt files and I am
putting them in excel and I will email that to him. (I
guess text isn't good enuf for me) I am putting links in
the "Group" column. At least for the ones I can find
online. (some are OOP) I just want to display a dialog box
stating there are links in this column, so I just need this?)

Private Sub Workbook_Open()
MsgBox "Click on group name to see the CD cover label"
End Sub

thanks

 

[JulieD]

Hi

well all you need to do is open the workbook - press ALT & F11 to display
the VBE Window, on the left find the workbook name in bold and under this
you'll see ThisWorkbook
if you can't see the workbook name in bold you'll need to choose view /
project explorer from the menu
double click on ThisWorkbook and copy & paste exactly what you have written
below, save and close the VBE window and the workbook and open it again to
test

note, however, you'll have to have your security settings (tools / macros /
security) set to medium (or less - not recommended) ... and choose "enable
macros" if prompted.

let us know how you go.

Cheers
JulieD

 

[Guest]

Thanks, that worked. What is the default setting for
security in Excel? Does it always prompt you for having
macros in them?

 

[JulieD]

Hi

glad to hear that it worked - for ver 2000 the default setting was medium ,
for ver 2002 / 2003 the default setting is high - which means that the
macros simply do not run and you don't get the prompt to enable them

Cheers
JulieD

 

[Gord Dibben]

And for Excel 97 the setting was "on" or "off".

No levels between.


Gord Dibben Excel MVP