MsgBox that will open only once.

[John]

Hi Everyone
Is it possible to have a popup window ( Msgbox ) that will open only one time.
The first time the workbook will be open, I would like to show the message
"Please read the instruction first".
I don't want the message to keep opening every time the workbook is open.
Regards
John

 

[Bob Phillips]

Private Sub Workbook_Open()
Dim ReRun As Boolean

With ThisWorkbook

On Error Resume Next
ReRun = Application.Evaluate(.Names("_reRun").RefersTo)
On Error GoTo 0
If Not ReRun Then

MsgBox "Please read the instruction first", vbOKOnly +
vbInformation, "MyApp"
.Names.Add Name:="_reRun", RefersTo:="=" & True
End If
End With

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


HTH

Bob

 

[Chip Pearson]

In the ThisWorkbook code module, paste the following code:

Private Sub Workbook_Open()
Dim S As String
On Error Resume Next
S = ThisWorkbook.Names("HasBeenOpened").RefersTo
If S = vbNullString Then
MsgBox "First Time"
End If
ThisWorkbook.Names.Add "HasBeenOpened", "True", False
End Sub

The code looks for a name called "HasBeenOpened". If the name not
found, S will be empty and so the MsgBox will be displayed. If the
name does exist, S is not empty so the MsgBox is not displayed.
Finally, the name is added so that the MsgBox will not be displayed on
subsequent openings.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Ryan H]

There is know way for Excel to remember you have shown your message box.
What you will have to do is store a piece of data in a cell. For example, if
the message box hasn't been shown yet, cell A1 will equal FALSE and when the
workbook opens it will check that cell to determine if the msgbox needs to be
shown. Hope this helps! If so, let me know, click "YES" below.

Private Sub Workbook_Open()

If Sheets("Sheet1").Range("A1").Value = False Then
MsgBox "Please read the instruction first.", vbInformation
Sheets("Sheet1").Range("A1").Value = True
End If

End Sub

 

[Dave Peterson]

Both Bob and Chip gave you code that creates a new name in the workbook. But if
the workbook doesn't get saved, then the next time it's opened, the msgbox will
appear again.

You could add this line to either procedure -- right above the "End Sub" line:

ThisWorkbook.Save

ps. Remember that if 100's of people will be opening the workbook, then this
technique will only show the msgbox to the first person who opens that workbook
without the name.

After the name is created, then all the other users won't see the msgbox even
once.

One way around this is to store a value in the user's registry and check for
that using SaveSetting and GetSetting.

 

[Rick Rothstein]

A possible way to do this (only display the warning message one time for the
entire life of the workbook) is to add a Sheet (at the end of your existing
sheets is fine), put your message on that sheet along with an OK button and
then hide all the rest of the sheets. Put the code for the OK button on this
"added message" sheet and have it unhide all the hidden sheets, then
activate one of those now unhidden sheets, then have that code delete the
sheet with the message on it and, finally, save the workbook so the
"message" sheet and the OK button code are gone forever.

 

[John]

Thank you ALL
I will try it out and let you know how I did.
Regards
John

 

[Ryan H]

Good point Dave. How do you check the users directory?

 

[Bob Phillips]

He said registry not directory. Every user has their own registry.

HTH

Bob

 

[John]

Hi Everyone
I've tried them all and they all work fine.I would like to know how Bob and
Chip's code work. Dave said they create a new name in the workbook.
Where is that name stored ? Can I see it ?
Would love more explanation on how those codes work.
Regards
John

 

[Ryan H]

Ha ha, I had a "Freudian Slip". Since I have taken an interest in
programming I wish I would have got my B.S. in Computer Science instead of
Physics. Isn't it kinda of dangerous to mess with peoples registries? Is it
easy or very involved to access someones registery to do what John is needing
to do?

 

[Bob Phillips]

Yes, you can see them.

In Excel, just goto Insert>Name>Define..., and it will be there.

If you don't want them seen, they can be hidden.

HTH

Bob

 

[John]

If I was 40 years younger, I would also go in to Computer Science. Love this
Stuff.
John

 

[John]

Thank you Bob
John

Yes, you can see them.

In Excel, just goto Insert>Name>Define..., and it will be there.

If you don't want them seen, they can be hidden.

HTH

Bob

 

[John]

Hi Chip
Where is that name located. not exactly the same as Bob, I can't find yours.
Regards
John

In the ThisWorkbook code module, paste the following code:

Private Sub Workbook_Open()
Dim S As String
On Error Resume Next
S = ThisWorkbook.Names("HasBeenOpened").RefersTo
If S = vbNullString Then
MsgBox "First Time"
End If
ThisWorkbook.Names.Add "HasBeenOpened", "True", False
End Sub

The code looks for a name called "HasBeenOpened". If the name not
found, S will be empty and so the MsgBox will be displayed. If the
name does exist, S is not empty so the MsgBox is not displayed.
Finally, the name is added so that the MsgBox will not be displayed on
subsequent openings.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hi Everyone
Is it possible to have a popup window ( Msgbox ) that will open only one time.
The first time the workbook will be open, I would like to show the message
"Please read the instruction first".
I don't want the message to keep opening every time the workbook is open.
Regards
John

 

[Bob Phillips]

No, there is far too much hype about changing the registry IMO. Anyone who
would mess up the registry can mess their machine so much simpler without
the registry.

Using GetSetting and SaveSetting as Dave suggested is easy from VBA and
safe.

HTH

Bob

 

[Dave Peterson]

It depends on what you do and how you do it.

That SaveSetting and GetSetting stuff built into VBA limits how much damage you
can do. (There are other ways to lots more damage.)

VBAs help for each of them shows examples.

Basically, something like this:

 

[Ryan H]

I'd like to be 10 years younger. This year is the big 30 year old B-day. I
still have time to learn this stuff, lol.

 

[Dave Peterson]

It depends on what you do and how you do it.

That SaveSetting and GetSetting stuff built into VBA limits how much damage you
can do. (There are other ways to lots more damage.)

VBAs help for each of them shows examples.

Basically, something like this:

Option Explicit
Private Sub Workbook_Open()
Dim TestValue As Long

TestValue = GetSetting(appname:=Me.Name, _
section:="StartUp", _
Key:="ShowMsg", _
Default:=0)

If TestValue = 0 Then
'change the registry
SaveSetting appname:=Me.Name, _
section:="StartUp", _
Key:="ShowMsg", _
setting:=1

MsgBox "Please read the instructions!"
End If
End Sub

There's also a DeleteSetting that you can use to delete your entry in the
registry--nice to clean up while you're testing.

This is commented, but you can add it to your code.

' DeleteSetting appname:=Me.Name, _
' section:="StartUp", _
' Key:="ShowMsg"

(It only deletes the key--not the branch.)

But if you're snooping (and be careful!). Any change you make to the registry
is immediate. There are no "are you sure" prompts.

Windows start button|Run
Regedit
traverse to:
HKCU\software\VB and VBA Program Settings\appname\section\key
And you'll see how/where it's stored.

=========
JE McGimpsey has some more options for keeping track of stuff like this:
http://mcgimpsey.com/excel/udfs/sequentialnums.html

 

[Bob Phillips]

Oh that's good. Lead them along the path, then abandon them <g>

Bob