public variables lose value after userform
Posted by Katrina on August 03, 2000 4:37 AM
I posted a version of this as a follow up to "Variable scope", but Ada suggested I repost as a new message...
Using Excel 97.
I have two modules in my project. "PubVar" has the line:
Public iClick As Integer
"Module1" has:
Public Sub ShowModify()
iClick = iClick + 5
frmModify.Show
MsgBox "Return from show: " & iClick
End Sub
There is a button on my worksheet. ShowModify is attached to it (I've tried this as a Form control and as an ActiveX control, same result). I put a breakpoint on the End Sub of ShowModify and a watch on iClick.
The form (frmModify) is barebones. It has one button. The click routine is:
Private Sub CommandButton1_Click()
Me.Hide
End Sub
I click on the button on the spreadsheet, the form comes up, I click the button on the form, a msgbox comes up that says the iClick is 5. So far so good. The code hits the break point, and I check the watch. iClick still has a value of 5. I hit F8 to advance a line (execute the End Sub of the worksheet button macro), and iClick reverts to 0.
If you comment out the frmModify.Show line, then the value does not get lost when you hit the end of the routine.
Any brilliant suggestions? I'd rather not have to resort to saving variable values on the spreadsheet.
Thanks in advance.