×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Global variables that can be used in worksheets!

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

RE: Global variables that can be used in worksheets!

Hi Markus,

In a module:

CODE

Global intSupport as Integer

HTH
Todd

RE: Global variables that can be used in worksheets!

(OP)
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.

RE: Global variables that can be used in worksheets!

Hi Markus,

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

In your module:

CODE

Public intSupport as Integer

Public Sub Main()
  UserForm1.Show
End Sub

HTH
Todd

RE: Global variables that can be used in worksheets!

Markus,

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

Regards,

Regg

RE: Global variables that can be used in worksheets!

(OP)
Yes if that is possible Regg.

Do you know if it is?

RE: Global variables that can be used in worksheets!

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: www.essexsteam.co.uk

RE: Global variables that can be used in worksheets!

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

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!

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources