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?
I can't find anything direct via the web and here so I will ask what will hopefully be an easy question.
I have a form called FIELD_MX and on this form I have a Command Button called Enter ONT Demand Requirements.
When I click the Enter ONT Demand Requirements, I want to display a form called ONT_Demand_Requirements.
I know the UserForm needs to be initialized, but where should it be or am I wrong?
I am getting a run time error # 9 when I run a macro that calls a Userform or when I try to run code in a Userform module. The code performs beautifully on my computer, but it did not work on a coworker's computer. It ended up working on 3 out of the 5 computers I have tried it on.
I have tried changing security settings to low, and a bunch of other stuff, but I cannot get the code to run on the computers that get the run time error on them when I try running the code on them.
I get the run time error when I try to load or show any userform in the workbook and I get it if I try to run code that is in the userform module. However, if I paste the code into a regular module and run it, the code runs fine.
Does anyone know what could be causing this? I don't think my code is causing the problem since it runs on some machines, I am guessing there is a setting that is preventing Excel from calling Userforms.
I have a button, Private Sub CommandButton1_Click, on a sheet that shows a userform. This is the bit of code that gets tagged with the run time error. The userform has a refedit control on it allows the user to select a cell and then hit ok to run the code or cancel to, well, cancel it.
Code for the button that gets tagged with the run time error:
Private Sub CommandButton1_Click()
frmLoadTrade.Show 'calls userform
End Sub
Code in the Userform Module:
Private Sub cmdCancel_Click()
TradeTicketSpreadsheetName = ThisWorkbook.Name
Unload Me
Workbooks(TradeTicketSpreadsheetName).Activate
End Sub
I call a userform from a sub menu i created it, but i got an error 449: Argument not optional.
Private Sub Workbook_Open()
Dim cControl As CommandBarPopup
On Error Resume Next 'Just in case
'*Delete existing Super Menu if it was left.
Application. CommandBars.FindControl(Tag:="MyMainMenuTag").Delete
On Error Goto 0
On Error Goto u
'*Add new Accounting Menu
Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup, 1, , , True)
With cControl...........................
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
I get a "Run-time error '9': subscript out of range" error when calling a userform.
The code opens a reference spreadsheet then shows a userform with a combobox and two command buttons (Continue and Quit). The userform has a private initialization sub-routine to populate the combobox with values from a list on the reference spreadsheet. When the userform is called the error comes up.
Here's my problem: The code was written on my computer and saved as an Add-in. The add-in has since been installed on a number of other computers and works properly. Recently I tried to run the code and this error started popping up, don't know what changed. All versions of Excel are the same and haven't changed.
BRF = ActiveWorkbook.Name
If BRF = "Blank upload.xls" Then
Workbooks.Open path & "QC Data AnalysisQCDA ref.XLS"
OtherBRF.Show
Workbooks("QCDA ref").Close SaveChanges:=False
Goto 1
End If
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?
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 RelatedI 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] ......
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 RelatedI 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?
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,
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 RelatedI was just trying to work around a problem with multiple UserForms in project. I have assigned Object variables oUserForm1 and oUserForm2 to represent UserForms of specific names.
VB:
Dim oUserForm1 As Object
Dim oUserForm2 As Object
Set oUserForm1 = VBA.UserForms.Add("Data" & CStr(X))
oUserForm1.Y = Y
[Code] .....
When oUserForm1 shows new data are inserted and another macro runs with a line to hide the opened UserForm.
In my project manager this user form name is i.e. Data1 (for x = 1), but the syntax Data1.Hide returns error. After that line it ask me to close the TOP most modal UserForm.
But When I use Me.Hide all works well.
My question is: Does VBA not see the name of the userform ("Data1") because I have used the oUserForm1 variable to give it a focus to it?
I am trying to pass two variables RARD and CARD from Sheet1 'change selection' subroutine to the 'Set ARD Command button click' subroutine. The variable value remain empty and it gives me a 1004 error when I try to execute this line of code:
[Code] ....
What I am trying to do is put the text value 'ARD' in the cell on sheet1 which was the original cell I clicked on to open the userform2. To do this I click on the 'Set ARD' button on the userform2 which is then suppose to hide the userform2 and put the 'ARD' text in the selected cell on Sheet1.
I have attached the workbook for review : Therapy Tracker - Tester V2.10 - deleted logo.xlsm
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.
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 View Relatedi have a userform where when i initialize i load my combo box with a range
I have a variable which = combo box value
Sub cmd click()
myval = cbo.value
unload me
End sub
How can i use or pass this variable to my standard module i.e
Sub test()
myevent = myval
end sub
I am creating a UserForm to get macro running options from the user. The Workbook__Open calls the macro which shows the UserForm right after the variables are defined. Is there a way to avoid the compile error?
07.15.2013-14.15.54 - Bermex's library
I am trying to use some code similar to what i have used in one of my Modules in a Userform (The module calls this userform)
I have declared a few variables in the module:
Code:
Public LastRow As Long
Public TabLen() As Integer
Public AHUArray() As String
Public ArrayDim As Integer
and i want to try and use them in a userform (i call the userform through an bit of code that usures that the other subs that assign values to the variables have been used so there should be values in the variables??i think?)
i get to this line in my userform
Code:
TabPos = TabPos + TabLen(i)
this is the first line that requires a value to be in the array and it errors: Subscript out of range
I just want a few variable that are used in a module to be available in other modules and userforms.
EDIT:the (i) is decalred in this sub within the userform. A thought occurs, the sub in the userform is a private sub, might that effect it?Also, explain Sub vs Private Sub vs Public Sub?, im new to this! the sub that fils the arrays in the module is just Sub SubName_Click()
I created in module custom toolbar with a button, see the
Option Explicit
Public ctrlGUI As CommandBarControl
Sub CreateToolbar()
Dim cb As CommandBar
On Error Resume Next
Application.CommandBars("Cust").Delete
On Error Goto 0
Set cb = Application.CommandBars.Add( Name:="Cust", temporary:=True)
With cb
.Visible = True
Set ctrlGUI = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrlGUI
.FaceId = 3205
.Style = msoButtonIcon .............
I have a macro that when it runs presents a form that gives the user an option to skip the calculations or continue. If they click Skip then it continues the 'Next CE' . My problem is I can't get the Boolean value to pass from the form to the macro. Here is what I have
The main macro
Sub Dividend_MF()
For Each sh In ActiveWorkbook.Worksheets( Array("G&I", "Growth"))
For Each ce In sh. Range("a5:a" & sh.Range("a65536").End(xlUp).Row)
If Not IsEmpty(ce) And Not (IsError(ce.Offset(0, 56))) Then
If ce.Offset(0, 56) = "X" Then
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.
I have a form using text boxes to enter some data into a spreadsheet. These text boxes appear in pairs for any single point (e.g., name ; location). Does anyone have a code which can actually define how many of these pairs will appear on the form when loaded? For example if there are 5 points to be defined the form should have 5 pairs of these text boxes. The maximum number of these pairs is around 10 and I could make a form with a 'standard' number of 'pairs' but that's not really it. I've tried to attach the form but it did not work
View 9 Replies View RelatedI am trying to figure out how to make a userform to display the contents of a 2-D array which has a variable number of rows. I want the userform to height of the userform to correspond with the number of rows of data to display.
I don't have much experience with userforms, but here's what I was thinking:
VBA code which would find the # of rows of data and then adjust the height of the userform and the length of the lable (which the data would go in).
I have a sub that I need to call multiple times. I'm trying to figure out if there is a better way to do this rather than write out the call each time:
AYForm = "UserForm2.txtAYMonths" & i
AYPForm = "UserForm2.txtAYPercent" & i
SummerForm = "UserForm2.txtSummerMonths" & i
SummerPForm = "UserForm2.txtSummerPercent" & i
JobForm = "UserForm2.cboJobClass" & i
NameForm = "UserForm2.txtName" & i
For i = 1 To 15
Call FormatAY((AYForm), (AYPForm), (SummerForm), (SummerPForm), (JobForm), (NameForm))
' Call FormatAY(UserForm2.txtAYMonths & i, UserForm2.txtAYPercent & i, UserForm2.txtSummerMonths & i, _
' UserForm2.txtSummerPercent & i, UserForm2.cboJobClass & i, UserForm2.txtName & i)
Next i
the sub FormatAY has six arguments; 5 textboxes and 1 combobox. Since the names of these items are the same except for a number (example UserForm2.cboJobClass1, UserForm2.cboJobClass2, UserForm2.cboJobClass3 etc...), I wanted to write a for loop and use a variable to represent the last number. Is this even possible? The couple of ways that I have tried this, I get either a member not found, or ByRef error.
I want to display a string to users. Normally I would do that using message boxes. But that would not give me enough control over the font size, and I want to display using font size 48.
I could have used global variables, but that is not a good coding way. The other way of doing that is using property procedures. I could pass a string variable to a Label control on the form and then could format it.
Users select a row number which then opens up a Userform. Comboboxes and Textboxes on this Userform are then populated with coloumn values (dependant on the row initially selected). If the row number was the same each time I could accomplish this by the following.
Private Sub TextBox1_Enter()
TextBox1.Value = Range("a1")
End Sub
but as its a variable I'm trying the following (which I think should work)
Private Sub TextBox1_Enter()
TextBox1.Value = Range("a" & edi & "")
End Sub
I think I need to declare the variable 'edi' as Public. If so where should this be? I've tried 'Genereal Declaration'
i want to use a listbox or combobox on a userform with the values coming from column A in the MAIN sheet. what i need is if the colour i want is not there i type the new colour in it then adds the value to the end of values in coloumn A and too the list for the next time i use the userform. is it possible to do this and how?
View 3 Replies View RelatedI want to be able to call one user form from multiple ActiveX command buttons. The problem is, the number of command buttons depends on user input on another worksheet, so it's variable.
I've renamed all of the command buttons so they are named "CommandButton" & i, where i is an integer between 1 and, say, 200. I want each of these buttons to direct to the same UserForm where additional information can be entered.
I can't think of a way around the event-handler procedure name.
Sub [Command Button Name]_Click
to call the User Form. I won't know the command button names, because I won't know how many there are (max i) until the user inputs.
Basically, I want to create a For loop through the max i and have the event-handlers call the user form