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
ADVERTISEMENT
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
View Related
May 4, 2007
I worked really hard on this userform for a work project - my first and likely last attempt at coding!
It used to work, and then I changed a bunch of listboxes to comboboxes, and now my file gives the 'Excel has encountered a problem and needs to close' error - but ONLY when I'm working directly with the excel file and NOT when I've got VBA open.
Compiling shows no problems - I have no idea what to do because this is a very important file for work and I am a very inexperienced coder! ...
View 7 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jan 9, 2007
I located combobox on userfom and don't see it in the module.
View 7 Replies
View Related
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
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 Replies
View Related
Aug 14, 2012
I am trying to bring up a form after clicking the "ok" button on another form. Both forms are defined in my public sub, so basically, the module creates both forms, calls the first form, and hides the second form. Then the first form hides itself and shows the second form. However, when second form is defined using "dim", the first form can't find it to show it, and when I make the second form "public" in my public sub, I get the error that it is an invalid attribute or function.
Here is the code from my sub that applies to this error:
Code:
Public Sub PutInEngine()
Dim InputForm As New FrmInputs
InputForm.Show
[Code].....
View 3 Replies
View Related
Jan 20, 2010
i currently have an email button setup, so when the person has filled in the sheet and clicks the button it sends the active worksheet to the specified email address in the code
i need an extra step in this code to check that data has been filled in, in certain cells before the email is sent
for example i have numberous cells but in cell i17 if something is entered then there must be something entered in cell i19. If there is nothing filled in cell i17 then the data validation can ignore this. Basically it needs to check that corresponding cell(s) are filled in "if" the first cell is filled in (make sense?)
i would also like it to check certain cells are filled in (mandatory cells)
here is the email button code, is there anything i can implement in that to produce the above results? or if this cannot be achieved maybe a warning box to pop up before it sends to say "Please make sure everything is filled in corrently before submitting" (something like that)
View 14 Replies
View Related
May 3, 2012
I have several non-modal userforms in my App, some of them have date-fields that require manual entry typing of dd/mm/yy etc (No single userform has more than one date-box in it, this I think may be pivotally useful)
Now the Userform 'Calendar' that is built on the class of the same (cCalendar) name, has the write value line 'ActiveCell.value = theCal.value'
I'm looking to change this to refer to the correct userform.Textbox value, depending on which form is open.
I would imagine I could simply have a global string, whose value is set (or re-set) whenever a Userform is initialized (some sort of 'ActiveUF.value = Me.Name), where I get lost is referring to the components by name, so as to have a case statement by where I go:
Code:
Private Sub theCal_AfterUpdate()
Select Case ActiveUF
Case "AddForm"
application.vbe.components("AddForm").controls("AddFormDatePicker").value = theCal.value
Case "EditForm"
'.... etc
end select
end sub
better way of doing this (instead of passing around the userform name as a variable) - or proper syntax for referring to controls outside of the 'active' userform (but an open userform nonetheless)?
Every time I have to do this particular thing with userforms, I completely forget how, and the object browser always leads me on an infinite loop of Application.vbe.activevbproject.vbcomponents.vbe.active....
PS - there may be one slight complication to the process - one of the forms, has a 2-tab page in it, each page having similar (but named differently) fields. So I may need to be able to throw in 'Activepage' or whatnot
View 5 Replies
View Related
Dec 17, 2008
In order to deal with the multitude of screen resolutions that can be found at our work, I have got a module to check the current screen resolution on loading the workbook. I then have set up 3 different sized userforms, all containing the same coding, and based on the displayed screen resolution one of these is open (the userforms are named Customer1, Customer2, Customer3).
The displayed userform then has a button to show another userform (Customer_Details), and this form requires some referencing back to the previous form for some of it's information eg,
customer_details.textbox1.text = customer(1, 2 or 3).textbox1.text
customerdetails.combobox1.list = customer(1, 2 or 3).combobox.list
way for me reference the specific "Customer" form that has been opened, without calling it by name?
View 9 Replies
View Related
Aug 15, 2014
is there a way how to make text in a user form created in VBA dependable on a value of some variable? In another words: I want the userform to pop up at some point and I want the text inside of userform to vary depending on a variable which can take on four values.
View 9 Replies
View Related
Feb 21, 2014
I got some codes defined as below and I want use a userform for and checking the process. If ı could use variable "i " in userform, my codes would work.
Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("E3:P36")
[Code] ......
View 2 Replies
View Related
Aug 13, 2007
Hi, i'm having a terrible problem (at least for me).
I want to use a var from a UserForm to use it in calculation method in a Module.
How can i refer to that variable in the Userform in the Module code?
View 11 Replies
View Related
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
Oct 20, 2005
I have code in a userform that shows another userform with a text box. When the user types text there and presses OK, I want that userform to close (which it does fine), and return back to the original userform (which it does fine), but I want the typed text to be stored in a variable that the code in the original userform can use.
I tried declaring a public variable, "Rresponse1", in each userform's procedure, but that doesn't work. I test for the content of the variable afterward in the original userform and it is empty.
In the original userform I have:
SaveList.Show '(this is the second userform)
MsgBox "This is response1: " & Response1 '(to test if the variable is being passed)
In the userform SaveList, in the Private Sub CommandButton1_Click() procedure, I have:
Response1 = SaveList.ListName.Text
Application.ScreenUpdating = True
MsgBox Response1 '(to test the variable, here it shows the text fine)
Unload SaveList
How do I get the Response1 variable to keep its content when control is passed back to the original userform?
View 6 Replies
View Related
May 23, 2013
I have a macro that calls a User Form using:
Code:
Update_Too.Show
The form has a drop down list menu and the user needs to select a month. I store que choosen value in a variable named "OPT", using this code:
Code:
OPT = Me.Months_List.Value
I declared this variable at the start of the macro like this:
Code:
Dim OPT As String
But it doesn't seem to be working. I am not declaring the variable in the user form,
View 9 Replies
View Related
