Keeping Values in Variables
 

Hi,
I am trying to write code for the user to ensure that they have checked for
certain criteria before they protect the sheet. My code is as below. However,
if a criteria is not met then the user exits the sub routine, corrects the
criteria and presses the submit button again. However, I do not wish the
message boxes that have already been dealt with to show again. Hence If test1
is not zero then do not invoke the message box and carry on . The problem is
when the sub is exited the variables loose their value and you have to start
the routine from scratch. How can I get over this?

Public Sub CommandButton1_Click()
Dim Reply As Integer
Dim Test1 As Integer
Dim Test2 As Integer
Dim Test3 As Integer

If Test1 = 0 Then
Reply = MsgBox("Test 1", vbYesNo, "Title")
If Reply = vbYes Then
Test1 = 1
Else
Test1 = 1
Exit Sub
End If
End If

If Test2 = 0 Then
Reply = MsgBox("Test 2", vbYesNo, "Title")
If Reply = vbYes Then
Test2 = 1
Else
Test2 = 1
Exit Sub
End If
End If

If Test3 = 0 Then
Reply = MsgBox("Test 3", vbYesNo, "Title")
If Reply = vbYes Then
Test3 = 1
Else
Test3 = 1
Exit Sub
End If
End If

End sub

Keeping Values in Variables
 

Just Dim the variables in a public or static location, in the module above
the Sub statement.
--
Gary's Student


"Jurrasicway" wrote:

Hi,
I am trying to write code for the user to ensure that they have checked for
certain criteria before they protect the sheet. My code is as below. However,
if a criteria is not met then the user exits the sub routine, corrects the
criteria and presses the submit button again. However, I do not wish the
message boxes that have already been dealt with to show again. Hence If test1
is not zero then do not invoke the message box and carry on . The problem is
when the sub is exited the variables loose their value and you have to start
the routine from scratch. How can I get over this?

Public Sub CommandButton1_Click()
Dim Reply As Integer
Dim Test1 As Integer
Dim Test2 As Integer
Dim Test3 As Integer

If Test1 = 0 Then
Reply = MsgBox("Test 1", vbYesNo, "Title")
If Reply = vbYes Then
Test1 = 1
Else
Test1 = 1
Exit Sub
End If
End If

If Test2 = 0 Then
Reply = MsgBox("Test 2", vbYesNo, "Title")
If Reply = vbYes Then
Test2 = 1
Else
Test2 = 1
Exit Sub
End If
End If

If Test3 = 0 Then
Reply = MsgBox("Test 3", vbYesNo, "Title")
If Reply = vbYes Then
Test3 = 1
Else
Test3 = 1
Exit Sub
End If
End If

End sub

Keeping Values in Variables
 

Thanks Gary. Just needed pointing in the right directon. Works great now

Graeme.

"Gary''s Student" wrote:

Just Dim the variables in a public or static location, in the module above
the Sub statement.
--
Gary's Student


"Jurrasicway" wrote:

Hi,
I am trying to write code for the user to ensure that they have checked for
certain criteria before they protect the sheet. My code is as below. However,
if a criteria is not met then the user exits the sub routine, corrects the
criteria and presses the submit button again. However, I do not wish the
message boxes that have already been dealt with to show again. Hence If test1
is not zero then do not invoke the message box and carry on . The problem is
when the sub is exited the variables loose their value and you have to start
the routine from scratch. How can I get over this?

Public Sub CommandButton1_Click()
Dim Reply As Integer
Dim Test1 As Integer
Dim Test2 As Integer
Dim Test3 As Integer

If Test1 = 0 Then
Reply = MsgBox("Test 1", vbYesNo, "Title")
If Reply = vbYes Then
Test1 = 1
Else
Test1 = 1
Exit Sub
End If
End If

If Test2 = 0 Then
Reply = MsgBox("Test 2", vbYesNo, "Title")
If Reply = vbYes Then
Test2 = 1
Else
Test2 = 1
Exit Sub
End If
End If

If Test3 = 0 Then
Reply = MsgBox("Test 3", vbYesNo, "Title")
If Reply = vbYes Then
Test3 = 1
Else
Test3 = 1
Exit Sub
End If
End If

End sub