VBA-Passing a variable between modules

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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
I don't think I understand the difference. The modules I seem to have available are:

VBAProject.Sheet1
VBAProject.Userform1

Is there something else I should be doing? I've got the nagging feeling I'm doing/not doing something fairly stupid for this to be fighting me so much!!

TIA,

...Eric
 
Upvote 0
Yes, you should put it in a Module1, not in the Sheet's module, why ? because Sheet's module are Private Modules. Standard modules aren't (Unless you make them)
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top