Number Variable To Reference UserForm Controls
Mar 31, 2008
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.
View 6 Replies
ADVERTISEMENT
May 22, 2007
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 Related
Nov 17, 2006
I have a userform with fields (TextBoxes) Num1, Num2, Num3, Num5 and Num5
I also have an array which i have looped through and seeded with five values.
Now when I try and loop again and pass the values from the array to each of the textboxes I cant remember the syntax to reference the textbox using the loop variable - Num(var). I've experimented with the & symbol but it escapes me at the moment !
View 3 Replies
View Related
Feb 4, 2008
I have a fairly big excel spreadsheet with about 2 userforms at the moment. One Userform (the main one) has a multipage control with about 12 pages. Now here is the weird part. I open up the excel document and this main user form loads up simply with this code
Sub Auto_Open()
Load Userform1
Userform1.show
End Sub
This works fine the form loads. Now on this form there is a multipage control with about 12 pages as mentioned above. Now everything works the way I want it to eg the text boxes, combo boxes, command buttons etc all work and link with the worksheets fine just the way they should...But if I reference or try to use a couple of the controls in any way excel crashes. It doesn't give any errors or anything it just crashes and then I get a prompt saying do you want to send the error report blah blah. I have narrowed it down to 3 command buttons and 1 combo box so far that this happens on. If I do the most basic of commands on any of these controls such as something as simple as msgbox problemcombobox.visible...
View 2 Replies
View Related
Jan 9, 2007
I located combobox on userfom and don't see it in the module.
View 7 Replies
View Related
Dec 18, 2009
The form that I have created has a number of controls that are created at runtime. If a user triggers the event that originally created the controls, I want to be able to delete all of the controls before recreating them.
Is there a loop that can delete all of the newly created controls? I have 9 controls on the form that need to stay put but everything after those 9 controls I want to delete. Is there something like:
View 5 Replies
View Related
Sep 4, 2008
Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?
For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.
View 9 Replies
View Related
Jun 27, 2014
I 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
View 14 Replies
View Related
Sep 3, 2007
I am using excel vba to control IE page with a variety of forms / inputs .
Set ie = CreateObject("InternetExplorer.application")
ie.Visible = True
ie.Navigate (URL)
Call ie_wait(ie)
ie.Document.all.loginID.Value = USERNAME
ie.Document.all.PASSWORD.Value = PASSWORD
ie.Document.forms(1).submit 'login
I am trying to control a drop down box in a HTML page
<select name="attv_39721_5004" onchange="executeRules(this, '39721', '5004', 'attv_', 'Choose...')">
<option value="0">Choose...</option>
<option value="8140">1</option>
<option value="8141">2</option>
<option value="8142" selected="selected" >3</option>
</select>
and i will normally use
ie.Document.all.attv_39721_5004.SelectedIndex = newS
The problem is the unique name for the html select drop-down box changes always!! i.e. it may not always be "attv_39721_5004"
Thus I am not able to use the above method. Does anyone have a different method to changing this values?
View 3 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
Oct 12, 2011
In VBA (Excel 2003), I'm wanting to get a reference to an ActiveX comboBox that is inside/attached to a cell.
I can iterate thru all the OLEObjects in the OLEObjects collection of the Worksheet and test the .TopLeftCell property to see if it matches my target cell. However, this seems like a long way around the problem - particularly if there's hundreds of comboboxes in the sheet.
Intuitively, I would have thought there would be a Cell.OLEObjects collection, but it appears that there isn't.
Is there a quicker and easier way to get a reference to the OLEObjects within a target cell?
View 2 Replies
View Related
Aug 15, 2012
I have a userform that has nested multipages (5 in the outer page, 4 in the inner page).
On each of these multipages, I want to have the same controls (sliders) laid out in the same order - but with unique names, named after their tab location, for each control so I can use their value property later in the code.
I've designed the layout and named all the controls on my first sheet (e.g. Slider1Outer1Inner1). I now need a way of automating the replication of these across the other 19 sheets (including the nested inner multipage!); so that equivalent slider for example would be called Slider1Outer1Inner2, Slider1Outer1Inner3 etc.
I'm not sure about coding VBA to act on items within VBA...
VB:
" For each multipage in outer
For Each multipage In inner
For Each Object In current multipage
Copy inner.object -> Next multipage
Inner.object.name = CurrentOuter & CurrentInner & CurrentSlider "
View 2 Replies
View Related
Nov 17, 2008
What's the best way to hide controls on a userform? I have a userfrom with 2 datepicker one for start date and one for end date. I want them hidden until I use checkbox and check it to appear. I am using this code but nothing is work.
View 2 Replies
View Related
Jan 23, 2009
I have taken this code from Access and trying to use it in Excel to lock all the TextBox, ComboBox & CheckBox on the form.
View 2 Replies
View Related
Jul 18, 2012
How to disable different parts of a userform?
I dont really want to go through each control as I reckon it will be handy code to have in the future if I need to add more but so far my code is:
Private Sub UserForm_Activate()
With Sheet2
n = 3
[Code].....
View 2 Replies
View Related
Jul 11, 2008
I have a number of TextBoxes across the page, all set to Visible=False.
If these get filled with data then I need to set Visible=True.
Rather than hard code this individually for each one (which is not a problem, it just looks untidy), can I select each row as a Group (whilst designing the form, not in the code) and then set that Group to Visible=True ?
View 9 Replies
View Related
Nov 4, 2009
I have a userform that contains several textboxes, checkboxes, and comboboxes. There are also some command buttons, one of which says clear all. What I want to do is loop through the controls and clear the contents or change the value to false, depending on the type. I can do this by type the name.value = "", but there are alot of controls. If possible, a loop would be much more effecient.
View 9 Replies
View Related
May 18, 2006
I am trying to create some controls in a userform on the fly based on the data in a sheet. The part of my code is the following:
summaryForm.Controls.Add bstrProgID:="forms.label.1", Name:="LAWts", Visible:=True
With summaryForm.Controls("LAWts")
.Top = 120
.Left = 20
.Height = 18
.Width = 300
.Caption = "Weights: "
End With
Question I is that how can I connet this textbox to a click event or just to a procedure when it is clicked? I tried to use OnAction but it does not work for textbox in a userform. Question II is that how can I find the all properties of a textbox or any other controls? Is there a manuel or reference available on the internet?
View 3 Replies
View Related
Aug 4, 2006
What are the pros/cons of using Userforms with the various objects (comboboxes, textboxes,etc) compared to putting the controls at the worksheet level? Are there things you can do in one and not the other?
View 2 Replies
View Related
Oct 25, 2006
I am trying to transfer data from a worksheet to a user form, so that the end users can edit the data on the user form, save it, and the revised data is sent back to the worksheet. since the worksheet data is dynamic, i am trying to dynamically add controls in the user form. but the form displays only one data.
Set sdel = Sheets("Deliverables")
Set rStartCell = sdel.Range("A65536").End(xlUp).Offset(0, 0)
counter = Mid(rStartCell.Address, 4)
dummy = 0
cnt = 1
'copy data from sheet to the user form
With sdel
'checking if deliverables sheet has any data
' If .Range("A3") <> " " Then Exit Sub
' MyTextBox.Caption = .Range("A3").Value
For r = 3 To counter
If .Cells(r, 1) <> "" Then
Set MyTextBox = Controls.Add("Forms.Label.1", "lbl" & cnt, Visible)
MyTextBox.Top = topadd + 30
MyTextBox.Left = 20
MyTextBox.Width = 150
MyTextBox = .Range("A" & r).Value
cnt = cnt + 1
Else
dummy = dummy + 1
End If
Next r
End With
View 3 Replies
View Related
Feb 24, 2007
I have a userform with a series of textboxes used for data entry (numeric values) specifically, 3 groups with 5 text boxes per group. If the value of any text box within a group is either >= the value of a cell in a worksheet range (Benchmark, i.e. BM), then the BackColor of that particular text box changes to green.
The challenge I have is trying to loop through each set of five controls to determine if the control's value is >= to the cell's value in the worksheet range, and if that condition is met, than changing the BackColor to green.
I know there must be a more efficient way to make this happen than to write an 'If - Then' for each text box (see code below)
Private Sub Benchmarks()
BM1 = Worksheets("Sheet 1").Range("A1").Value
BM2 = Worksheets("Sheet 1").Range("A2").Value
If txtGroup1Box1.Value <> "X" And txtGroup1Box1.Value <> "" And txtGroup1Box1.Value <= BM1 Then
txtGroup1Box1.BackColor = &HFF00&
Else
txtGroup1Box1.BackColor = &H80000005 ............................
View 9 Replies
View Related
May 9, 2007
i build a userform that includes 6 columns, and use named range to to be shown in the userform.
i use simple code to call this userform.
excuting the code, the userform appears ok.
i saved my file as an addin to be able to call my userform
the problem that the userform appears empty, no data and no columns, when calling the userform from the addin file whearas the userform works fine with the named range when calling from an xls file.
any idea how to solve the problem ?
pls find the attached file
File deleted as it of no help.
View 9 Replies
View Related
Dec 14, 2007
way to allow a user to add a control to a userform without going into the VBE. Here is the situation, I am developing a userform to calculate a projected budget. I want the user to be able to select the number of controls to add, and click a command button to add them. Based on other selections that the user makes, different controls would automatically be added. Is this possible? As a follow up, will I be able to atttach code to these new controls?
View 9 Replies
View Related
Mar 4, 2008
On the event that a combobox is changed the objects on my userform that relate to the same information as the combobox need to be disabled (and made invisible)... however if another choice is made then they need to be enabled again and then the objects relating to the new selection need to be disabled.
Private Sub cbo1_Change()
Dim DisableIndex As Integer
Dim CellAddress As String
Worksheets("Matrix").activate
ActiveSheet. Range("B1").Select
Do
If ActiveCell.Value <> cboVarNamEn.Value Then
ActiveCell.Offset(0, 1).Select
End If
Loop While ActiveCell.Value <> cboVarNamEn.Value
CellAddress = ActiveCell.Address(ReferenceStyle:=xlA1)
ActiveSheet.Range("B1:" & CellAddress).Select
DisableIndex = Selection.Cells.Count
Me.FrameAddEn.Controls("label" & DisableIndex).Enabled = False
Me.FrameAddEn.Controls("label" & DisableIndex).Visible = False
'there a few other objects here that will also be disabled... text boxes, check boxes etc.
Putting an unload/load command in won't work for obvious reasons and I can't use a command button because that would require my clients to actually think of pressing it.
View 3 Replies
View Related
Jun 16, 2014
What I would like to do is create a userform where I can input a case reference number and click a command button which searches the CSV/TXT file for any notes for the specific reference number and then returns all notes (seperated by a blank line) in the textbox within the userform in the posted/saved format.
I can find lots about importing to excel but nothing about importing data to userforms.
CSV file added for format. The userform contains two text boxes, one blank for import and one for completing the case reference number (format on CSV file. Line item 1).
i.e. if I searched by SL-001-0155648 I would get the below (It's not code but I thought the best way of showing what I mean):
[Code] .....
Attached File : Notes Test CSV.txt
View 15 Replies
View Related
Dec 15, 2008
I have a MultiPage userform and I'm adding (4) control buttons - CancelButton, BackButton, ForwardButton and FinishButton. On the first page, those names are fine but when I get to subsequent pages, I get an ambiguous error if I try to name the controls the same. But the code will all be the same. What am I missing? In an example that I have, the user was able to name all the controls with the same name. What setting would control that?
View 5 Replies
View Related
Jun 19, 2009
My userform has a group of controls, four navigation buttons and a textbox. The nav. buttons have accelerator keys, Alt+N for next record and so on. I was moving the group about on a userform, when hey presto! it suddenly disappeared. When I run the form, the navigation buttons do not appear, but I can still navigate using Alt+N, Alt+P. Here's what I've done so far, trying to find out what's going on.
* I selected all controls with Ctrl+A, but there was no sign of any object in the place where the missing group should have been.
* I looked on the Object Browser (which is something I don't understand too well yet) and the navigation buttons are there.
* I hid all the controls, and showed them again, didn't help.
* The group I was moving was near the bottom of the form. I set the zoom property of the form to 50, so all controls in design view were bunched up near the top left corner; the missing group didn't appear
View 2 Replies
View Related
Nov 17, 2003
I have a userform with 24 text and combo boxes. Aside from using the enter and exit events, is there an easy way to have the active text/combo box be highlighted in a color?
View 9 Replies
View Related
Jun 3, 2009
I am trying to calculate the result of two text boxes in a third textbox on a userform.
Box1 = DirectorHrs = a textbox that the user enters the number of hours
Box2= DirectorRate = a textbox that retrieves the rate from a sheet called "Rates" within the workbook upon initialise of userform.
Box3 = DirectorTotal = Box1*Box2. I want this to update when the user enters or changes Box1.
I have the following code on the change event of Box1
Private Sub DirectorHrs_Change()
DirectorTotal.Value = CStr(Val(DirectorRate.Value) * Val(DirectorHrs.Value))
End Sub
View 4 Replies
View Related
Feb 2, 2005
I've created several UserForms, for some reason this one won't co-operate!
Issue are:
1. Have to click the OK button 3 times for the code to execute (this does not occur when I filter through the code in debug/F8 mode).
2. Before processing I have a label and image set to visible = true that won't appear on the form at runtime (this works when I filter through the code in debug/F8 mode).
View 7 Replies
View Related
