G
Guest
Guest
I've created a userform in ExcelXP-VBA to enter data. There is some code in the userform module to control some behavior. I need to pass the result of a MsgBox to the main worksheet module for further processing. The following are the key code snippets:
Userform Module
Private Sub
WriteDataReponse = MsgBox("blah blah", vbYesNo)
UserForm1.hide
End Sub
Worksheet Module
Public WriteDataResponse
Private Sub
WriteResponseData = vbNo
Userform1.Show
If WriteDataResponse = vbYes Then
WriteData1 = Userform1.TextBox1.Text
WriteData2 = Userform1.TextBox2.Text
End If
End Sub
I thought a Public variable would be avaiable in all modules and its value preserved. I've set a couple of breakpoints and monitored WriteResponseData in the Locals window.
WriteResponseData sets to vbYes (value 6) in the Userform module as it should. When I check the Locals window in the Worksheet module, WriteResponseData is still set to vbNo (value 7). It stays this way when the Userform subroutine ends and control is returned to the worksheet module.
I don't get it!!
Help!
TIA,
...Eric
Userform Module
Private Sub
WriteDataReponse = MsgBox("blah blah", vbYesNo)
UserForm1.hide
End Sub
Worksheet Module
Public WriteDataResponse
Private Sub
WriteResponseData = vbNo
Userform1.Show
If WriteDataResponse = vbYes Then
WriteData1 = Userform1.TextBox1.Text
WriteData2 = Userform1.TextBox2.Text
End If
End Sub
I thought a Public variable would be avaiable in all modules and its value preserved. I've set a couple of breakpoints and monitored WriteResponseData in the Locals window.
WriteResponseData sets to vbYes (value 6) in the Userform module as it should. When I check the Locals window in the Worksheet module, WriteResponseData is still set to vbNo (value 7). It stays this way when the Userform subroutine ends and control is returned to the worksheet module.
I don't get it!!
Help!
TIA,
...Eric