Setting Global Variable From A UserForm

Jul 31, 2009

I have a UserForm that runs when my excel project starts that prompts the user for two pieces of data: a username and a password (these are not for logging into the file itself, I need to use them to call web queries later). I want the UserForm to store both of these data in global variables, so that macros that are run in the future can refer to them and read their values. Unfortunately, I am very new to VBA and I cannot figure out what code I need and where exactly I need to put it. Here is my current code, where "authentication" is the name of the UserForm object, and the textboxes I use for entry are named "user" and "pass": In "This Workbook"

View 4 Replies


Setting A Global Variable

May 27, 2008

If I want to set a global variable when I open my workbook which will be used in code on the individual spreadsheets, how would I do this? I want to set the time the workbook is opened to a variable (constant) and then compare that time to current time on each calculation in the worksheets.

I'm using Excel 2000.

View 10 Replies View Related

Global Variable For UserForm

Jan 1, 2008

I maked a userform who use global parameters (to let the user decide where write things) and later i need to use this parameter in a module (who is the main program). I try to resolve this problem put this global parameters like global parameters in the module and later in the both sides (in the module and in the useform), but it can't work. How i can resolve that?, i.e., How i can use a global parameter in a userform and the same global parameter in the modulo with the same data?

View 6 Replies View Related

Global Variables Setting A Date

Jul 28, 2014

i have a variable that will be the same in every module within a workbook. i have tried playing with Global, Public, Conts, etc. but get compile errors.

essentially this is what i want... i just want to set the dte globally so i dont have to set it in each module.


Global dte As Date
If Weekday(Now()) = vbSunday Then
dte = Date - 2
dte = Date - 1
End If

View 3 Replies View Related

Global Variable Or Pass Variable Between Sub-routines

Jan 26, 2007

The first goes through a directory and opens all the files.. after it opens a given file it goes off into a sub-routine to process the data in that file.

I am trying to create a counter in the first sub-routine and then pass that value into the second sub-routine to tell it to put the values out on the next row down.. so the first time through it puts the values out on row 1, next time it puts them out on row 2, etc.

View 9 Replies View Related

Make Variable In VBA Global

Dec 13, 2008

I would like have a input box in which a user enter a number and then I would like to be able to use that number in other worksheets within the same workbook. How do I declare the variable for use with other sheets. I know I would first use

Dim intRows As Interger
But now how do I make it global.

View 9 Replies View Related

Global Vs. Public Variable

Oct 28, 2009

explain the difference between declaring a variable as global or as public. Aren't they both available to the entire project including forms?

View 9 Replies View Related

Declaring A Global Variable

Oct 20, 2006

I have a worksheet that has 2 sheets. I want to declare a variable that can be accessed for reading and/or overwriting by both of those sheets. Where do I declare it and how do I access it from Sheet1 for example?

View 4 Replies View Related

Global Variable For Workbook

Dec 3, 2007

how to set up a global varable over a workbook? Incognito439

View 5 Replies View Related

Global Variable Declaration

Dec 25, 2007

I have a smattering of experience within various programming languages, but am still coming to terms with the basics of VBA. I am trying to declare a global variable, assign it a value, then use that global variable. Within 'ThisWorkbook' I have the following...

Public myText As String
Private Sub Workbook_Open()
myText = "Hi There"
End Sub

...and in the Microsoft Worksheet Object Sheet1 (Sheet1) I have...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox myText
End Sub

Now, my understanding of this code is that upon opening the workbook, myText variable will be declared, and then assigned the value "Hi There". Then, once I have clicked anywhere on Sheet1, a message box will appear stating "Hi There". Problem is, the message box is blank. This is all fine, except I want the message box to state "Hi There". What am I doing wrong? Is the variable declared (publically) correctly? Am I assigning the public variable the value correctly?
Am I referencing the public variable correctly in the Worksheet_SelectionChange procedure correctly?

View 3 Replies View Related

A Variable Which Is Used By Several Subs In A Worksheet But Is Not Global?

Dec 7, 2008

I am working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple. Some of my variables are local to the subs they're used in. Some are global as they're used by subs in several sheets. A third type of variable is used by several subs all belonging to the same sheet. Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?

View 4 Replies View Related

Declare & Initialize A Variable As Global Using Vba

Feb 10, 2009

how to declare&initialize a variable as Global in vba?

I have a variable ,
now i am using this variable for 3 different functions. so what i am doing is wrote the same code to 3 functions, so how i can declare&initialize this variable as global and access to all functions.

View 9 Replies View Related

Declare Global Variable In Application Level

Sep 17, 2009

I need to declare a global variable in Application level not Module level.

I have declare a variable in Module1 and then Module2 with same name then complile it and get success. That means there has two variable with same name in different Module. I think this was not a proper global variable declaration by which I can allow to declare only one variable in all Module, Class every where. I have used code as below -

View 9 Replies View Related

Variable Amount For Global Functions/Formulas

Jun 22, 2008

How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.

View 5 Replies View Related

Global Code For UserForm Controls

Dec 13, 2006

Im using the following code on a userform to link the spinner and a textbox:

Private Sub Marketing_spn1_Change()
'ensures the text box changes when spinner is changed
Marketing_txt1.Text = Marketing_spn1.Value
End Sub

Private Sub Marketing_txt1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' ensure value typed in is valid
newvalue = Round(Val(Marketing_txt1.Text), 0)
If newvalue < Marketing_spn1.Min Then newvalue = Marketing_spn1.Min
If newvalue > Marketing_spn1.Max Then newvalue = Marketing_spn1.Max
' set the spinner value to keep in step with new value
Marketing_spn1.Value = newvalue
' set the textbox to the appropriate value
Marketing_txt1.Value = newvalue
End Sub

I have many spinners and textboxes with the same name (marketing_txt2, marketing_spn3 etc) on both this userform and on other userforms I shall be doing.
Is there any way of just writing this code once, rather than copying and pasting it for each spinner/textbox?

View 3 Replies View Related

Excel 2010 :: How To Match Worksheet Names Against Global Variable

Jun 10, 2013

I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.

I am not sure of two things:

1. how to find the already existing tab?

2. Once I find out how do I programatically delete it, so the code can continue

The code below works with the exception of the last section (trying to achieve the questions stated above).

I am using Excel 2010.

Private Sub CommandButton1_Click()
If TextBox1.Value = blank Then 'Need name for processing
MsgBox ("Name must not be blank.")
Exit Sub
End If
If Len(TextBox1.Value) > 12 Then


View 5 Replies View Related

Setting Variable For Next Statements?

Sep 3, 2013

I am trying to do the following

If Range(DataType) = 1 Then
For SubSet = 1 To 3
ElseIf Range(DataType) = 2 Then


Now I realise the code is wrong but what I can do in order to get a variable For statement to work. Or am I just missing the obvious.

View 3 Replies View Related

Setting Formula With Variable?

Nov 6, 2013

Trying to Figure out the syntax for setting range properties with a variable. Is this possible?

Sub Testing()
.Range("B7").Formula="=Average(A & SomeVariable:C & SomeVariable)"
End Sub

View 2 Replies View Related

Setting A Variable To A Sheet Name In VB

Apr 27, 2007

How do I set a variable to the active sheet name in VB.

Hypothetical example here:

Dim Sht as Name
Sht = Activesheet.Name

View 9 Replies View Related

Setting Variable Names In Vba

Aug 10, 2009

I am working on a macro for excel which has a userform where the user puts in the number of samples they have, then takes them to a new userform to input the weights of the samples. I want to be able to set the sample mass variable dynamically based on the number of samples they have…



x=number of samples

This number will be different each time so I am stumped at how to do this since I have just started in vba and do not know too much yet.

View 9 Replies View Related

Setting Variable As Data Range?

Dec 12, 2011

I am attempting to set a variable as a range of data that sits on a non-activated worksheet. However, I am getting a runtime error of 13 (data mismatch).

Is there something in my code that may be incorrect? Here is my code:

Dim rRange As Range
Dim sRange As String
Dim tblName As String


View 4 Replies View Related

Incorrect Syntax For Setting Value Of Variable

Nov 26, 2013

I'm not sure where my syntax is wrong, and I can't think of what to google to learn more about the possible options I have when creating and declaring the value of variables.

I am simply trying to define the variable aWB as a workbook, whose name is found in cell B6 on the worksheet named "Start" in the workbook named "DW1". This seems to make sense to me, but I am not typing it correctly.

Dim aWB As Workbook
Set aWB = Workbooks("DW1.xlsm").Worksheets("Start").Cells("B6").Value

View 9 Replies View Related

Setting A Range Using String Variable

Jan 8, 2014

I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.

The value is returned to CurrentRange, but it's the last line that's giving me the problem.

Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String
CurrentRange = Range("AG3").Value MsgBox (CurrentRange)
'This returns the value [N7:N75], which is what I want
Set UtilizationRange = Range("CurrentRange")End Sub

View 1 Replies View Related

Error Setting Range Variable

Aug 8, 2006

i have compiled a multipage using some borrowed code and some code i have written myself. most of it works, but i have a problem populating listbox2.the error is in Private subCmbFindAllJobNo_Click(). i have put h1 tags around the line of code which shows the error when i debug. this code works ok as a stand alone, so i suspect i have done something wrong in the userform initialise.

Option Explicit
Dim rng As Range
Const FirstRow As Long = 2
Dim r As Long
Dim ans As Variant
Dim MyArray(100, 4)

Public MyData As Range, c, d As Range

Private Sub cmbAmend_Click()

Application. ScreenUpdating = False
Set c = Worksheets("ENTRY SHEET").Range("b4").End(xlUp).Offset(3, 0)
c.Value = Me.DTPicker1.Value
c.Offset(1, 0).Value = Me.TextBox1.Value
c.Offset(2, 0).Value = Me.TextBox2.Value ..........

View 9 Replies View Related

Setting A Range Object Variable's Address

Apr 1, 2009

Here is my code

Dim LastCell As Range
Set LastCell = Cells(EndRow, LastCol).Address

I get a "Object Required" when this I try to set the address. I know this is easy.

View 9 Replies View Related

Run-Time Error Setting Worksheet Variable

Aug 30, 2007

The user is asked for one piece of information "Enter the ID Number.

What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.

Set Wks2 = Worksheets(strWks)

7 rows from the bottom.

My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.

Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.

I get a Time Run Error 9.

It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.

Sub Macro01C_Auto_Resign()
Dim Wks1 As Worksheet, Wks2 As Worksheet
Dim strFind As String, rngFound As Range
Dim lngRow As Long, rngUnion As Range, strWks As String ............................

View 9 Replies View Related

Setting Column Chart Data Using VBA For Variable Ranges

Mar 10, 2014

I have some code to plot a column chart of data but it isn't working as expecting at the moment. The code is below. The variable binCounter is a count of how many cells in a range that I want to plot on the chart.

However, what I am finding is that the first couple of cells in the range appear as the series name with the rest appearing as the data in the chart. Secondly, the chart appears with the axis labels 1,2,3 etc when I have some custom ones I would prefer to use. How do I go about setting this property, as I can only find options on setting the axis title There is a lot of stuff on XY charts on Google but I can't find much on column charts unfortunately .

'activate sheet and chart
Worksheets("Home Page").Activate

'set variables for chart
With ActiveChart

[Code] .....

View 1 Replies View Related

Setting Variable, Discontinuous Print Area In A Macro

May 29, 2009

I need to write a macro to set the print area to the first two columns (A & B), and the last 12 columns (the last column may change). In both cases I need to print all rows (start is row 1, last row is variable). I am trying to achieve something like the "Freeze Pane" effect with the printer. The first two rows contain column headers (dates). The first two columns contain information that needs to be included on the printout, whilst the last 12 columns contain the most recent data.
Various cells withing the selected ranges may be blank, but no row or column will be entirely blank.

View 2 Replies View Related

Invalid Qualifer Error -setting Variable Using Getpivotdata

Mar 22, 2007

We have a very long macro that at the end sends an email to each training coordinator. Within the body of the email, we want to autopopulate the completion status from a pivot table using getpivotdata.

We first try to set the variable but get an error: [compile error:invalid qualifier].

Dim BDCompletion As String
BDCompletion = Application.PivotTableSelection.GetPivotData(A3, "Business Dev Plan Found")

And this is how we plan to incorporate the variable into the body of the email:

With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = CurrentSheet.Name & " Training Plan Status as of " & Format(Now, "dd-mmm-yy")
.Body = "BD is " & BDCompletion & " complete for 2007 Training Plans as of the date of this email."
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C: est.txt")
.Send 'or use .Display
End With

View 9 Replies View Related

Setting Values In A Variable Range Occurence Query

Jul 18, 2006

I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;

1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively
2)define range to be searched by how many contiguous values there are in column D
3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on
4)define new range by next set of contiguous values in column C
5) step 3
6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter
7)finish when all combinations are searched for

Sub CountComboOccurence()
Dim ListCounter As String
Dim ProgCounter As String
Dim Counter As Integer
Dim ComboValue1 As String
Dim ComboValue2 As String
Dim Rng As Range
Application. ScreenUpdating = False
ListCounter = 0
ProgCounter = 0
Counter = 0
ComboValue1 = 0.......................

View 2 Replies View Related

Copyrights 2005-15, All rights reserved