Global variables that can be used in worksheets!

[MarkusLAndersson]

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

 

[TCARPENTER1]

Hi Markus,

In a module:

Global intSupport as Integer

HTH
Todd

 

[MarkusLAndersson]

Thanks but I cant get it to work.

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.

 

[TCARPENTER1]

Hi Markus,

Sorry, didn't realize you were using forms, do this:

In your module:

Public intSupport as Integer

Public Sub Main()
  UserForm1.Show
End Sub

HTH
Todd

 

[Regg]

Markus,

Are you trying to access a VB variable in a worksheet?

Regards,

Regg

 

[MarkusLAndersson]

Yes if that is possible Regg.

Do you know if it is?

 

[johnwm]

In your VBA if the cell will be fixed use the Range object:

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:

http://www.essexsteam.co.uk

 

[Regg]

Not to my knowledge but johnwm shows a way to indirectly do it (you have to run the VBA code first).

Regards,

Regg

 

[johnwm]

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:

http://www.essexsteam.co.uk

 

[joerd]

Markus is using a UserForm, so as soon as the variable value has been changed, you can update it via the UserForm event codes.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.