UserForm Variables Available To All UserForm Procedures
Jan 16, 2008
I have a userform that should return a global varable but when the global varable is used back in the program...it is 0 and not the number in the userform.
The varable I speak of is ForcedCurrent
Global declairation
Private Type TempData
LotNumber() As String
Site() As String
Macro() As String
ChainName() As String
ResistancePerContact() As Double
Index As Integer
End Type
Private Type Lot
Product As String
ParaOrDeft As String
ThermalOrStress As String
Hours() As String
Data() As TempData
Index As Integer
End Type
Dim LotData As Lot
Dim TestNameArray() As String
Dim NumberOfContacts() As Double
Dim ReedholmUnits() As String
Dim ForcedCurrent As Double ...
View 4 Replies
ADVERTISEMENT
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
May 31, 2009
My question is about passing arguments to UserForm procedures. I've created a Main procedure. Main calls a sub procedure and returns an array. Obviously Main can now use the array. But then Main calls a sub procedure to open my userform.
All of my userform sub procedures need to use the array I created in Main. Do I have to pass the array into all of my userform procedures? Into the UserForm_Show procedure? And then again into UserForm_Initialize? And then again into ComboBox_Change? I don't understand how to give my UserForm procedures the array from my Main procedure.
View 2 Replies
View Related
Apr 19, 2008
I have been trying to use a multi-select listbox as the argument for a subroutine. For some reason, I keep getting a run time error, type mismatch. I'm baffled because when I define the specific listbox, it works fine, but when I attempt to pass the listbox I get an error.
Public Sub OtherDirectTotal(Expense As ComboBox, CostCat As ComboBox, _
Cost As TextBox, Years As Msforms.listbox)
Dim IDC As Double
Dim IDCt As Double
IDC = Val(UserForm2.txtIDC) / 100
If Years.Selected(0) = True Then
If Expense = "Sponsored" Then
If UserForm2.ChkODC = False Then
Select Case CostCat......................
View 2 Replies
View Related
May 29, 2013
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.
View 9 Replies
View Related
Apr 5, 2007
passing variables within procedures on a worksheet. i have:
Private Sub lblGoToMaterials_Click()
'email to:
'----------------
'grab current position
myRow = ActiveCell.Row
mycol = ActiveCell.Column
and i would like to pass the values gotten in myRow and mycol to:
Private Sub cmdGoBackTo_Click()
Application.Goto Reference:="R" & myRow & "C" & mycol
View 3 Replies
View Related
Dec 21, 2009
I am trying to eliminate a lot of the global variables from my program by passing the variables to my functions and subs as arguments. I am stuck though when it comes to variables created in userforms.
The program starts with a series of userforms that asks the user for information that will be used throughout the rest of the program. Data is assigned to the variables on the click events. Is it possible, without using global variables, to pass those variables to the rest of the program?
Example:
View 3 Replies
View Related
Apr 6, 2013
Goal seek function
Code:
Worksheets("Sheet1").Range("Polynomial").GoalSeek _
Goal:=15, _ ChangingCell:=Worksheets("Sheet1").Range("X")
Is it possible to modify this such that I can use variables? For example, the variables are 'left term', 'right term' and 'sigma max' .
I want to set the value of the following equation 'left term - right term' to 0 by changing variable 'sigma max' Everything is done on the userform and not in the spreadsheet.
(Note: In spreadsheet format, the above query is equivalent to setting a cell which has a formula to 0 by changing the value in another cell, fairly simple).
View 3 Replies
View Related
Sep 2, 2007
I am looking at using forms, as in the Userform... Not used them before, but would like to know, if you create a text box on it, is the value global, so any module can use the var? or how can I, so that value entered can be used on the whole book ?
View 3 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
May 20, 2014
I have the following issue:
I have a table of data, and would like my userform to select parts on this depending on different variables.
When I run the macro "Show_UserFormCalculatePrice" or click the button "Calculate Price", the userform is activated which has 2 comboboxes, Date and Service.
When a certain date a service are selected I would like the the dish name to show in the labels of the userform.
My main issue is that there will be multiple dishes that will correspond to the data, and I would like them to be vertically listed in the labels depending on their number (Column A).
Essentially I would like to run some sort of If function:
If the date and the service correspond to the criteria then they are shown in the labels.
VBALabeling.xlsm
View 3 Replies
View Related
Jul 24, 2007
I have a sheet with 3 macros. It consists of two macros to produce results, and the third to bridge the two together where is all starts. I have built a userform in the sheet and am trying to get the variables in both sheets, to be user changeable and selectable via the userform.
In the attached data, the userform allows for the selection of two sheets, A and B. The user would select in A the sheet with data which is equivalent to Oval_An and in B the sheet with the data equivalent to Oval_DMA. The first macro, Find_75 runs, followed by the second macro, kTest, all working to produce results when hitting the Subtract button on the form. The Find_75 tolerance are also defined via the useform box, as well as kTest's compare tolerance.
View 6 Replies
View Related
Jul 16, 2012
I have created a userform and a command button to bring up the user form but when I click on the command button and the user form pops up I am not able to enter any data, the entire page freezes
This is the code
Private Sub CommandButtoncancel_Click()
unloadme
End Sub
Private Sub CommandButtonOK_Click()
With Workbooks("RETS results version 2.xlsm")
[Code] ......
View 1 Replies
View Related
Jun 15, 2009
This is weird - if you delete a sheet that contained a control then
a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End
b. public variables lose their value
These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?
View 9 Replies
View Related
Oct 1, 2009
I have one userform that loads combobox values upon userform Initialize. Though through a second userform changes can be made to anotherworkbook this workbook is saves any changes. when i close the second userform i need to rerun the 1st userform Initialize event to update the combobox's incase changes have been made.
View 5 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
May 8, 2012
I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.
Code for Form2: Const myName As String = Form1.txtName.Value
View 5 Replies
View Related
Apr 26, 2014
I Have a Userform which Have My Data i Print User Form Using Print Command Button And My Code Is
[Code] ......
But Its Printout Whole Form I only Wants To Print contents of Text Box's Or only Text From Userform TextBox. How To Print out Only Content of User Form Not The Whole Form ...
View 2 Replies
View Related
Oct 29, 2009
I'm trying to do: Let's say I have a simple userform with labels as follows:
(Label1) John's birthday: (Label2) 12.10.1974
(Label3) Mary's birthday: (Label4) 15.03.1978
(Button1) Change Birthdays
Let's say want to change the birthdays I see, so I press the button and enter the new values through another userform with inputboxes.
How do I make that change permanent, so that next time I start the macro in the labels 2 and 4 I have the last entered values for the birthdays???
View 4 Replies
View Related
Jul 4, 2012
What I would like to do is to be able to open a second workbook from a user form, preform some work on it then save and come back to the same place in the original user form.
So in steps:
1 : user clicks command button to open user form
2: user then clicks on command button on userform that opens 2nd workbook via a yes / no message box, but closes userform on 1st workbook (would be ideal if this could stay open, but hidden)
3: user then does work on 2nd workbook,
4: userform on 2nd workbook saves then activates the 1st workbook and reopens the userform
This is where no matter what I try I cant get the command button on the 1st userform to be clicked automatically so the yes / no message box appears.
This is part of the code in the 2nd sheet commandbutton that saves / closes / opens
Code:
Unload Me
ActiveWorkbook.save
Windows("ABC.xlsm").Activate
Sheets("Request Sheet").Activate
Call Sheets("Request Sheet").ForceClickOnBouttonXYZ
Call UserForm1.CommandButton6_Click 'this is where I cant get it to work!!
Windows("xyz.xlsm").Close
ABC is the 1st workbook
xyz is the 2nd workbook
This is the code on the 1st workbook I use to call on the 2nd workbook
Code:
Public Sub ForceClickOnBouttonXYZ() Call CommandButton1_Click End Sub
View 7 Replies
View Related
Jun 8, 2009
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Application.ScreenUpdating = True
Unload Me
RestrictedOptions.Show
Else
Cancel = True
End If
End Sub
Trying to use above code to unload active userform and show new userform when red X (close) selected by operator. With the code as is, the Unload Me leaves the form on the screen and displays the RestrictedOptions form. If i remove the Restrictedoptions.Show, the Unload Me does remove the original form.
View 9 Replies
View Related
Jul 3, 2004
When I run the userform initialize procedure to reset the values in text boxes and the like instead of resetting like it should, now it closes the userform completely and then won't allow me to show it again, what could be the problem?
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
TextBox3.Value = ActiveWorkbook. Sheets("Sales Invoice"). Range("G15").Value
Dim hWndForm As Long
Dim hMenu As Long
hWndForm = FindWindow("ThunderDFrame", Me.Caption) 'XL2000
hMenu = GetSystemMenu(hWndForm, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End Sub
The Dim stuff down is to gray out the x button, there are also some module level declarations to go with that...
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Private Const SC_CLOSE As Long = &HF060
the userform shows when the workbook opens and is then hidden when the user has finished entering values, then when the user goes through the process of being asked to print and save, they are then asked if they would like to create a new record, if yes then it shows the userform again, but the userform is still filled with all the stuff previously entered. I tried using the unload me instead of hiding and that wouldn't work at all, didn't give errors just didn't show the userform either, this at least shows the userform, but now when the user goes to clear the information by initializing the userform again, it simply closes the userform and then it can't be shown again either.
[url]
This is the link to the ZIP, and here are some instructions for setting it up to work.
The contents of this need to be unzipped into a folder called SyntheticShield that is placed in the C: drive that way all paths begin with C:SyntheticShield and then the other folders or files will be referenced correctly. The template I'm having problems with is the SyntheticShieldInvoiceMaker template, when you open it you'll be prompted whether you want to create a new invoice, number is final blah blah blah, say yes, then it will ask if you are importing from the Quotemaker which you aren't say no, it should then show the userform as it should. Then you can go through that process and by pressing either the create invoice or go to template directly whatever, you can hide the userform. Then you press the command button (red) a userform is brought up asking if you'd like to save without emailing, save with emailing or close controls, as for right now, I'm just getting the save without emailing to work the rest is all the same just a few tidbits of code. So click save without emailing, it should then prompt an are you sure message box, click yes it will do some things, then it will ask if you want to print, click no, then it will ask do you want to create a new invoice, click yes, this should then start the process all over again by calling the workbook_open procedure, however, when you go through everything, and click no to the import from quotemaker part it won't show the userform. And at one point it would, but then I couldn't initialize the userform without it disappearing and not being allowed to be shown...I tried putting a command button on the template to show the userform, but it wouldn't do it either.
View 6 Replies
View Related
Jul 25, 2006
how do i show a userform in Excel in the spreadsheet environment? I used to "run macro" but unable to run userform code "private sub".
View 2 Replies
View Related
Nov 11, 2006
having trouble with the details of actually making these features work for me. I figured out how to create a UserForm with a ListBox and 2 buttons, but I don't know how to proceed from here.
1. Populate the ListBox in the UserForm with a list of names from the sheet "Totals_Dropdowns", cells K2:K11
2. Make the UserForm pop up and enter the user's selection into cell C40 of the "Regenerate Request"
I know these are very basic operations, and I'm pretty sure I can figure out the rest of my problems once I can get past the above.
View 7 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
Jun 3, 2009
I have a userform button that when clicked loads another userform.
I am getting a Runtime Error 13: Type mismatch on the line in bold in the code below
Private Sub cmdAdd_Click()
Me.txtAdd.Value = Me.VarNo.Value
VariationsForm.Show
Unload Me
End Sub
I do not get an error on the "Edit" button code which is similar so why is the above not working.
Private Sub CmdEdit_Click()
Me.txtEdit.Value = Me.VarNo.Value
VariationsForm.Show
Unload Me
End Sub
View 9 Replies
View Related
Apr 19, 2006
I want to use the following to launch a userform I created.
Sub showuserform()
Dim ans As Boolean
ans = msgbox("do you want to show the userform?", vbYesno)
If ans = vbYes Then
userform.show
End If
End Sub
The trouble is that if I click yes, nothing happens.
View 4 Replies
View Related
Jul 2, 2007
I am trying to change the property value of all optionbuttons on userform1 via userform2's deactivate event
Private Sub UserForm2_Deactivate()
If userform1.Controls = OptionButton Then
Value = False
End If
End Sub
View 5 Replies
View Related
Nov 22, 2007
I want to populate a textbox (output) on one userform with data from a textbox (target) on another userform.
The data in the textbox (target) is the result of calculations in the userform code and is not gathered from or saved to a cell.
I want the textbox (output) to be populated at "Userform intialize" event.
I have tried various other methods without any success.
View 9 Replies
View Related
Aug 5, 2008
I have a large userform (Main) that the users enter data into. At the end of the data entry the users click OK and the data is transferred to a spreadsheet. I then ask the users if they want to add another record. If they click "Yes" all is OK. If they click "No" the userform should close and another "switchboard" type userform should open.
My problem is that try as I might I cannot get the (Main) userform to close. It only does so after the switchboard closes.
If I don't load the switchboard, main closes without issue.
I have left out the bulk of the data transferred to the "data" sheet in the code below.
Private Sub Main_OK_Button_Click()
Application.ScreenUpdating = False
Sheets("Data").Visible = True
Sheets("Data"). Range("F2").Value = Surname.Text
Dim result As VbMsgBoxResult
result = MsgBox("Do you wish to enter another record?", vbYesNo + vbInformation, "Continue...")
If result = vbNo Then
Main.URN.SetFocus
Unload Me
Else
View 3 Replies
View Related