Toggle between True and False confuses me

[Curious]

I want to see the msgbox only once when I first activate the sheet. I
do not want to be bothered again and again. But the msgbox keeps
popping up every time I go to the sheet.. What is wrong with the
following two codes?

Option Explicit
Dim ABC
Sub Workbook_Open()
ABC = False
End Sub

Sub Worksheet_Activate()
If IsNull(ABC) Then
ABC = False
End If
If ABC = False Then
MsgBox "Please pay attention to this formula."
ABC = True
End If
End Sub

Thanks in advance

H.Z.

 

[meh2030]

I want to see the msgbox only once when I first activate the sheet. I
do not want to be bothered again and again. But the msgbox keeps
popping up every time I go to the sheet.. What is wrong with the
following two codes?

Option Explicit
Dim ABC
Sub Workbook_Open()
ABC = False
End Sub

Sub Worksheet_Activate()
If IsNull(ABC) Then
    ABC = False
End If
If ABC = False Then
    MsgBox "Please pay attention to this formula."
    ABC = True
End If
End Sub

Thanks in advance

H.Z.

H.Z.

See below for a solution. As a side note, when a Boolean variable is
initialized, it's always FALSE unless you switch it to TRUE. If you
switch it to TRUE and want it to be FALSE again, then you have to
"force" it to be FALSE.

Best,

Matthew Herbert

Create a module (i.e. Insert | Module) and place the following in the
declarations section:

Public ABC As Boolean

Then place the following in your Worksheet_Activate event:

Private Sub Worksheet_Activate()
If ABC = False Then
MsgBox "Please pay attention to this formula."
ABC = True
End If
End Sub

 

[Bernie Deitrick]

H.Z.,

You're doing too much with the worksheet activate event. Try it this way.

Dim ABC As Boolean
Sub Workbook_Open()
ABC = True
End Sub

Sub Worksheet_Activate()
If ABC Then
MsgBox "Please pay attention to this formula."
ABC = False
End If
End Sub


HTH,
Bernie
MS Excel MVP

 

[HONG]

H.Z.

See below for a solution.  As a side note, when a Boolean variable is
initialized, it's always FALSE unless you switch it to TRUE.  If you
switch it to TRUE and want it to be FALSE again, then you have to
"force" it to be FALSE.

Best,

Matthew Herbert

Create a module (i.e. Insert | Module) and place the following in the
declarations section:

Public ABC As Boolean

Then place the following in your Worksheet_Activate event:

Private Sub Worksheet_Activate()
If ABC = False Then
    MsgBox "Please pay attention to this formula."
    ABC = True
End If
End Sub- Hide quoted text -

- Show quoted text -

Thank you Bernie and Matthew for your promp response. Matthew's tip
works very well. Two key points worth mentioning: 1. As a side note,
when a Boolean variable is initialized, it's always FALSE
2. Create a public variable that will be used by all sheets in this
workbook

H.Z.