Global variables that can be used in worksheets!
Global variables that can be used in worksheets!
(OP)
Dear forum
I have a problem that I hope that you can help me with. I am using VBA UserForms in Excel to determine several options that is made by the user. For instance the user can choose between to options button (T-support or pi-support) and depending on that option a cell in my work sheet1 shall display the text T-support or pi-support. Now I want these option buttons to be connected to a global variable. This variable should be 1 if the T-support is selected and 2 if pi-support is selected. This shall not be don in my work sheet but in my VBA code. Is this possible by the use of some kind of global variable that then can be used in my worksheet?
I would really appreciate you’re help. Thanks in advance Markus
I have a problem that I hope that you can help me with. I am using VBA UserForms in Excel to determine several options that is made by the user. For instance the user can choose between to options button (T-support or pi-support) and depending on that option a cell in my work sheet1 shall display the text T-support or pi-support. Now I want these option buttons to be connected to a global variable. This variable should be 1 if the T-support is selected and 2 if pi-support is selected. This shall not be don in my work sheet but in my VBA code. Is this possible by the use of some kind of global variable that then can be used in my worksheet?
I would really appreciate you’re help. Thanks in advance Markus





RE: Global variables that can be used in worksheets!
In a module:
CODE
HTH
Todd
RE: Global variables that can be used in worksheets!
First of all it seams only to be working in a module while i am working in UserForms and her I can’t get it to work. On the other hand i can get the code
Dim Support As Integer
Support = 1
to work in my UserForm but with this i cant use Support in Sheet1. That is what I want.
Is there any way to declare such a variable and if yes how do I use it in the excel data sheet? Do I just write
=Support*E65
to multiply the value in E65 with Support or do I need something else?
Pleas help me. This would really ease my work.
RE: Global variables that can be used in worksheets!
Sorry, didn't realize you were using forms, do this:
In your module:
CODE
Public Sub Main()
UserForm1.Show
End Sub
HTH
Todd
RE: Global variables that can be used in worksheets!
Are you trying to access a VB variable in a worksheet?
Regards,
Regg
RE: Global variables that can be used in worksheets!
Do you know if it is?
RE: Global variables that can be used in worksheets!
myVariable = ActiveSheet.Range("A23")
or you can programmatically control the target location with the Cells object:
myVariable = ActiveSheet.Cells(23,1)
or to set values on the worksheet:
For a = 1 to 3
ActiveSheet.Cells(a, 2) = myVariable
Next a
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Global variables that can be used in worksheets!
Regards,
Regg
RE: Global variables that can be used in worksheets!
The VBA code would (presumably) run in one of the worksheet events, and would then be transparent to the user. Open the VB Editor (ALT-F11), open Project Explorer window (CTRL-R) and double-click on one of the sheet objects. That will bring up the Worksheet code window. Selecting the right-hand drop-down will then dispaly the available events.
You can also find them in the Object Browser (F2), under Worksheet
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Global variables that can be used in worksheets!
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.