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

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

"John" wrote in message
...
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

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]





On Fri, 8 Jan 2010 10:18:04 -0500, "John" wrote:

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

Hi Chip
Where is that name located. not exactly the same as Bob, I can't find yours.
Regards
John
"Chip Pearson" wrote in message
...
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]





On Fri, 8 Jan 2010 10:18:04 -0500, "John" wrote:

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

Chip adds a third parameter to the Add method, False, which is setting its
visible property, so he is hiding it (as I mentioned in an earlier post).

HTH

Bob

"John" wrote in message
...
Hi Chip
Where is that name located. not exactly the same as Bob, I can't find
yours.
Regards
John
"Chip Pearson" wrote in message
...
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]





On Fri, 8 Jan 2010 10:18:04 -0500, "John" wrote:

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

Thanks again Bob.
I've got a new problem under different post " Locking the VBA Project"
Maybe you can help me with that one too.
I appreciate all the help you guys are giving.
Regards
John
"Bob Phillips" wrote in message
...
Chip adds a third parameter to the Add method, False, which is setting its
visible property, so he is hiding it (as I mentioned in an earlier post).

HTH

Bob

"John" wrote in message
...
Hi Chip
Where is that name located. not exactly the same as Bob, I can't find yours.
Regards
John
"Chip Pearson" wrote in message
...
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]





On Fri, 8 Jan 2010 10:18:04 -0500, "John" wrote:

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

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
--
Cheers,
Ryan


"John" wrote:

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

.

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.



John wrote:

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

--

Dave Peterson

Good point Dave. How do you check the users directory?
--
Cheers,
Ryan


"Dave Peterson" wrote:

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.



John wrote:

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

--

Dave Peterson
.

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

HTH

Bob

"Ryan H" wrote in message
...
Good point Dave. How do you check the users directory?
--
Cheers,
Ryan


"Dave Peterson" wrote:

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.



John wrote:

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

--

Dave Peterson
.

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?
--
Cheers,
Ryan


"Bob Phillips" wrote:

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

HTH

Bob

"Ryan H" wrote in message
...
Good point Dave. How do you check the users directory?
--
Cheers,
Ryan


"Dave Peterson" wrote:

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.



John wrote:

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

--

Dave Peterson
.



.

If I was 40 years younger, I would also go in to Computer Science. Love this
Stuff.
John
"Ryan H" wrote in message
...
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?
--
Cheers,
Ryan


"Bob Phillips" wrote:

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

HTH

Bob

"Ryan H" wrote in message
...
Good point Dave. How do you check the users directory?
--
Cheers,
Ryan


"Dave Peterson" wrote:

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.



John wrote:

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

--

Dave Peterson
.



.

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

"Ryan H" wrote in message
...
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?
--
Cheers,
Ryan


"Bob Phillips" wrote:

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

HTH

Bob

"Ryan H" wrote in message
...
Good point Dave. How do you check the users directory?
--
Cheers,
Ryan


"Dave Peterson" wrote:

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.



John wrote:

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

--

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 wrote:

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?
--
Cheers,
Ryan

"Bob Phillips" wrote:

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

HTH

Bob

"Ryan H" wrote in message
...
Good point Dave. How do you check the users directory?
--
Cheers,
Ryan


"Dave Peterson" wrote:

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.



John wrote:

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

--

Dave Peterson
.



.


--

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

Ryan H wrote:

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?
--
Cheers,
Ryan

"Bob Phillips" wrote:

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

HTH

Bob

"Ryan H" wrote in message
...
Good point Dave. How do you check the users directory?
--
Cheers,
Ryan


"Dave Peterson" wrote:

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.



John wrote:

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

--

Dave Peterson
.



.


--

Dave Peterson

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.

--
Rick (MVP - Excel)


"John" wrote in message
...
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

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

"John" wrote in message
...
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

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
"John" wrote in message
...
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

Yes, you can see them.

In Excel, just goto InsertNameDefine..., and it will be there.

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

HTH

Bob

"John" wrote in message
...
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
"John" wrote in message
...
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

Thank you Bob
John
"Bob Phillips" wrote in message
...
Yes, you can see them.

In Excel, just goto InsertNameDefine..., and it will be there.

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

HTH

Bob

"John" wrote in message
...
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
"John" wrote in message
...
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

Hi Again
I will use Bob's code, but will keep them all, you never know when you'll need
them.
Thank you ALL again
Regards
John
"John" wrote in message
...
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