Userform Not Passing Through 1 Particular Public Variable?

Aug 20, 2014

I have a userform that is called within a sub in module. I declared a public string, "divisonb", in the module. When the userform's ok button is clicked, I define the public string through a "select case" method.

At the end of the private sub for the ok button click I have this:

[Code].....

a message box comes up with the correct string for divisonb. After the sub ends and it returns to the module I have the following:

[Code] ....

When this message box pops up, it is blank. Somehow, divisonb was redifined as blank within that 2 lines of code. All my other public strings are returned to the module with their correct values.

View 3 Replies


ADVERTISEMENT

Losing Public Variable When Submitting Userform

Jan 29, 2009

I have an Excel 07 spreadsheet containing multiple tabs, modules, and userforms.
In Module1, I have a public variable declared as boolean - will call it X. When X is selected from a combo box in Userform 1, X is set to TRUE. However I've noticed that when the user enters Userform 2, X is somehow set to FALSE.

I can't set it back to TRUE at that point because more often than not, it SHOULD be FALSE, thereby sending the macro down a different path. Any idea how I can retain the "TRUE" value for X. I've tried changing the Public Variables to Global, but am still having the problem.

View 2 Replies View Related

Passing Variable From Userform

Jan 30, 2010

I am using the following code to open a userform. Once the userform is open, the user has the option to choose a continue button or a cancel button. If the user is choosing continue, the code is not working (it always skips to the "else" portion of my "if" statement. I can't figure out why it is doing this.

View 4 Replies View Related

Set Public Variable

Feb 12, 2008

Setting the Public Statement does not work. These are my codes:

Option Explicit
Public Ankis_makro As Boolean
Sub Makro2_Ankis_veckor()
On Error Resume Next
Application. ScreenUpdating = False
Sheets("Listan").Activate
Selection. AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Range(Range("J2"), Range("I65536").End(xlUp).Offset(0, 1)).FormulaR1C1 = "=TEXT(RC[-9],""ลลลล"")" ...................

What ever I do I can't get the Ankis_makro set to True.

View 2 Replies View Related

Macro Can't See Public Variable?

May 26, 2014

I have declared one variable outside of Sub. And in a Sub I gave it a value. Then I run a macro, which should take that value and print it to the cell. However, it is printing a blank cell, not even "0" (I run a macro and anything in that cell is removed).

[Code].....

and then my macro:

[Code]......

How to make so macro see my variable declared outside of this macro?

View 8 Replies View Related

Keep Getting Error With Public Variable

Jul 8, 2008

I have a number of userforms that all reference the same worksheet. I wanted to create a public variable for that worksheet so I don't have to keep referencing it in each userform/commandbuttons etc. So I inserted a module and placed this declaration.

Option Explicit
Public MySh As Worksheet
Set MySh = ActiveWorkbook.Sheets("sheetName")
And I referenced it in a UserForm commandbutton

Private Sub CommandButton2_Click()
MySh.Range("i4") = Me.ComboBox1
MySh.Range("i5") = Me.TextBox1
End Sub

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

Public Variable Not Being Retained

Feb 22, 2010

I've got several proccedures all inside a single module where I declare 7 variables as Public before the start of the first proccedure.

My structure is something like this:

Public v1 as Variant
Public v2 as Variant
.
.
.
Public v7 as Variant

Sub Proc1
Call Proc2 (defines v2 as name of a file opened by a user; inside Proc1)
Call Proc3 (inside Proc1)
Call Proc4 (inside Proc3)
Call Proc5 (inside Proc5)
End Sub

Sub Proc6
Here I use v2
call Proc7 (inside Proc6)
End Sub

Variable v2 is uniquely named and is only used to hold a file's name so that I can call upon that file in various procedures. However, after Proc1 ends, Proc 1 retains the variable names of some of my Public variables, but not all, including v2, which I need!

I've tried going through this all step-by-step, no joy/nothing apparently obvious. I've also turned on Tools-Options-General-Notify Before State Loss but this isn't generating anything either.

View 9 Replies View Related

Scope Of Public Variable

Feb 10, 2007

I have a few lines of code as follows:

Private Sub Workbook_Open()
fileName = ThisWorkbook. Name
fileLocation = ThisWorkbook.Path
Dim strFound As Boolean, pos As Long
If Not fileName = "" Then
strFound = False
pos = 1
Do While strFound = False
stringFound = Mid(fileName, pos, 1)
If stringFound = "-" Then
productName = Mid(fileName, 1, pos - 1)
MsgBox "Name of Product Is: " & productName
strFound = True
Else
pos = pos + 1
End If
Loop
End If
End Sub

It would return "Test" if the file's name is "Test-Part1.xls". I then declare the productName variable as Public (Public productName As String) in one of the modules. But the productName only holds the value for a certain amount of time. After a while, it's empty.

View 4 Replies View Related

Public Variable Not Work

Feb 17, 2007

I read the Excel VBA Variables Scope and Lifetime info, but I still don't understand b/c of the statement "It's value is retained unless the Workbook closes or the End Statement is used." Which "End Statement"?

I want to fill an array in a form module, then later use those values in a different form module. If I declare the public variable in a standard module, I know any of the modules can access it, but will it retain its value after form1 is no longer running?

If not, do I need to involve a class somehow? I know nothing about classes except that their variables retain their values as long as any of the members is running.

View 9 Replies View Related

Dynamic Array As Public Variable

Mar 6, 2010

How can I make a dynamic array public?
I have to Dim it so it stays valid only inside the sub.

Public MyVar()

Sub test1()

Dim MyVar()
ReDim MyVar(1 To 4)
For x = 1 To 4
MyVar(x) = "ffffff"
Next x
End Sub

Sub test2()

For x = 1 To 4
Range("A" & x) = MyVar(x)
Next x
End Sub
Error I get is 'MyVar(x) = Subscript out of range'

View 9 Replies View Related

Public Variable Producing Error

Feb 10, 2007

I want to define a varible named MonthEnd that I will use in more than one project. In a normal example the variable would look like this:

Dim MonthEnd As String
MonthEnd = Format(Sheet1.Range("C3"), "MMYY")

The problem is that I will be using this more than one time so I figured I could define this a Public constant like

Public Const MontEnd As String = Format(Sheet1.Range("C3"), "MMYY")

View 3 Replies View Related

Public Variable Declaration Not Being Recognized

Jul 27, 2007

Hopefully this isn't too vague, but almost every project I've worked on so far has not recognized Public variables in all modules. I've constantly been searching for exceptions to this rule that could be contributing to this issue but haven't been successful.

I have read a million times that declaring a variable as Public makes it visible to all modules and preserves the value throughout. I've also read that if you edit code or reset your project, the values may be lost. I also know that if you try to use a variable before you give it a value (or for object variables set it = to something), it doesn't have a value yet. But all this applied, I'm still not getting Public variables to be consistently recognized or stay with the value I want them to have throughout the lifetime of my project (while the workbook is open)

My specific current issue (one of many so far) is that I have 4 command buttons within a worksheet object. Each triggers a different group of procedures, some of these are user forms. In the first button, I create two worksheets one to contain the current fiscal period "Options" the other for "Summary" of all the totals etc... (all financial data). In this first userform, triggered by the button, I allow the user to name these worksheets: like Summer2007Options or Summer2007Summary and I declare these publically as object variables OptSheet and SumSheet. These variable names and sheets are recognized for the next 2 buttons but now that I'm on the fourth, I have object variable not set errors when I try to refer to this variable.

View 9 Replies View Related

Public Variable Loosing Reference

Mar 14, 2008

I've got the following code, setting up and initializing a Public variable for a sheet, but I've found that it's has no value after the Auto_Open is run,
though it works fine in the procedure Auto_Open calls.

I can get around it by reSetting it in the module I need it in,
but that kind of defeats the purpose of having a Public variable:

code in Module1

Public parSheet As Worksheet

Sub Auto_Open()

Set parSheet = Worksheets("Params")
Call Initialization.BuySellInit

End Sub

View 8 Replies View Related

Pass Userform To Public Sub Procedure

Feb 15, 2010

I have a userform that has a number of cascading comboboxes, however i wish to use the same cascading feature in a number of other userforms. So what i am trying to achive is calling the cascading element from a public macro, to do this i need to pass the userform name to the procedure. The snippet of code i am using is:

Form:
Private Sub ComboBox1_Change()
If ComboBox1.Value <> "" Then
myform = Me.Name
Call Cascade1(myform)
End If
End Sub

Module:
ub Cascade1(myform)
myform.Label2.Visible = True
myform.ComboBox2.Visible = True
myform.ComboBox2.RowSource = Range(myform.ComboBox1.RowSource).Cells(myform. ComboBox1.ListIndex + 1, 1)
End Sub

View 2 Replies View Related

UserForm Variables In Public Modules

Jan 13, 2008

I make a userform called Option and now i want to call some of the variables of the userform in a module. First i try to copy this variables in a cell with the next

ActiveSheet.Cells(2, 3) = Me.MediaP.Value

and it works. But now, i want to call to MediaP in a module. For do that I try to do this:

With Worksheets("Hoja2")
.Range("B1").Value = Option.MediaP.Value

but it doesn't work. I don't want to use public variables or copy/use the information of the cell(2,3), i only want to know how use the information on the textbox called MediaP in a module.

View 3 Replies View Related

Declaring Public 2 Dimensional Array In Userform

Apr 22, 2014

i'm trying to develop an array that is populated when a userform is activated. The userform has a command button that when pressed, will cross check the information filled out in the userform with the entries in the array. If there are no matches then the array is ReDim and the new information is added to the array. Once all the entries have been made. The array is then transposed to a sheet titled "Database". My problem is that vba is not allowing me declare a public array.Below is the first part of the code. Which is when the userform is activated.

Private Sub UserForm_Activate()
With Sheets("Resources")
cbZIP.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
cbBED.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
cbBATH.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
cbSTABRV.List = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value
End With

[code]....

View 1 Replies View Related

Reference Userform Controls In Public Module

Jan 9, 2007

I located combobox on userfom and don't see it in the module.

View 7 Replies View Related

Pass Variables From Userform To Public Procedures

Nov 17, 2007

When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?

View 3 Replies View Related

Passing Variable To Two Macros?

Jul 25, 2012

I have the following code in 'ThisWorkbook':

VB:
Private Sub Workbook_Open()
Call Meetdata
End Sub

It calls this macro which is in a standard module:

VB:
Public firstvariablename As String
Public secondvariablename As String
Sub Meetdata()
regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West")
meetdate = InputBox("Enter the date of the Meet.", "Date of Meet")
End Sub

This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .

VB:
MsgBox(firstvariablename & " " & secondvariablename)

At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]

View 2 Replies View Related

Passing Value Of Of EndXLUp To A Variable?

Mar 11, 2013

Dim lngrow As Long.

i want the above variable to hold the value of endxlup . so for example :

lngrow = Cells(.Rows.Count, "A").End(xlUp)

Why doesnt it hold the # of the last cell with data in column A ?

View 2 Replies View Related

Passing A Variable To A Subroutine

Dec 23, 2007

My code defines a variable: newrow. Then I call a subroutine using the Call command.
The subroutine does not recognize newrow.

It says it has value zero. How can I pass the value of newrow on to the subroutine.

View 9 Replies View Related

Passing A Variable Between Workbooks

Oct 22, 2006

I have two open workbooks, each having a single worksheet which includes a command button. The VBA code for each button performs tasks on its worksheet, then activates the other (inactive) workbook. I want to set the value of a variable in the button_click code in Workbook1 before activating Workbook2, and then use the value of the variable in the button_click code in Workbook2.

Declaring the variable as 'Public' doesn't work because the variable scope remains within its own workbook project. I must use two workbooks rather than two worksheets in one workbook.

View 4 Replies View Related

Passing A Private Variable To Another Module

Nov 25, 2008

I have some code in which I need to pass the value of "j" from one module to another. "j" is declared in Sheet1 (Data) under the Microsoft Excel Objects.

View 8 Replies View Related

Passing Boolean Variable To A Function

Nov 25, 2008

This might be a dumb question but is it possible to pass a boolean variable as a parameter to another function?

I have the following code that produces some compile error ("expected ="):

View 2 Replies View Related

Passing Variable Value From Private Sub To Module

Aug 12, 2009

I am trying to pass a variable called "Filter" from a Private Sub to a Module but keeping coming up with a zero value in the Module. I tried to make the variable Global but that didn't seem to work.

Private Sub Code as follows:

View 3 Replies View Related

Passing A Variable Into A Different Piece Of Code

Mar 6, 2009

I have a spreadsheet that has several buttons on that run code using the cells around the code for parts of the data. eg

ColumnA1 ColumnB1 ColumnC1 Button1
ColumnA2 ColumnB2 ColumnC2 Button2

I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)

View 9 Replies View Related

Passing A Variable On OnChange-event

Aug 8, 2006

I've been trying to pass a variable from the OnChange- event on one sheet to a macro located in module one, but it seems I cant (or havent figured out how anyway). Anyone knows how to do it? The code here give me the error "RefreshSetup(WhatSheet) cannot be found"

Alternativly, maybe someone can come up with a better solution on my entire problem. 3 sheets with webquery. All should be handled in the same way (copy-pasting only), but source and destination-sheet differs. My big problem is if two queries are done at the same time, they conflict with eachother, and data from one query is copied on to the wrong sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then
RunWhen = Now + TimeSerial(0, 0, 1)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
Else
RunWhen = Now + TimeSerial(0, 0, 3)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
End If
End Sub

Module1:................

View 2 Replies View Related

Passing A Range Of Strings To Vba Variable

Apr 3, 2007

I'm trying to pass a vector with in Strings (words) to a VBA variable, but something is not working.

That's how I'm writing: ...

View 9 Replies View Related

Run Time Error 13 Passing Cell Value To Variable

Nov 28, 2006

I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:

Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)

Sub Reservoir()
Dim Inflow As Single
Dim Withdrawal As Single
Dim Evap As Single
Dim Area As Single
Dim Spill As Single
Dim Stor_1 As Single
Dim Stor_2 As Single
Dim count As Integer
Dim max As Integer
max = ActiveSheet.Cells(9, 4)
For count = 1 To max * 12
Stor_1 = ActiveSheet.Cells(count + 40, 11)
Inflow = ActiveSheet.Cells(count + 41, 4)
Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved