Two Comboboxes On A Userform
Jan 20, 2009
I have two comboboxes on a userform, they both get there list from the same formula. What I am trying to do is have the second combobox have it's selection preset based on the selection in combobox 1.
ie
Combobox1 = 6:00 AM
when you click on the dropdown for combobox2 i would like 6:00 AM to be the first selection possible, but I dont want it displayed in the box unless it is selected.
Dim timdat1(1 To 85)
For i = 1 To 85
timdat1(i) = Format(TimeSerial(5, (i + 1) * 15, 0) - Int(TimeSerial(5, (i + 1) * 15, 0)), "h:mm AM/PM")
Next i
combobox1.List = timdat1
combobox2.List = timdat1
i am at a loss for where to go from here
View 9 Replies
ADVERTISEMENT
May 7, 2009
I have a userform that has a text box. If user puts a number in it and click on proceed the userform must expand and display that many comboboxes. for e.g. if user inputs 8 and then click on proceed then there should be 8 comboboxes on the form. Is it possible to do?
View 4 Replies
View Related
Feb 17, 2007
I have a worksheet "Master Log" with a UserForm "UserForm2" set up that has 4 comboboxes and 2 text entries. I already have the filtered unique values for each combobox sent to columns "O" thru "R" and they are dynamic ranges. I have the code to populate one of the comboboxes in the userform but cant figure out how to modify the code to have all four populated without getting ambiguous entry errors. And also, do I have to initiate the form for each combobox? Below is the code Im using for a single combobox.
Private Sub UserForm_Initialize()
Dim MyUniqueList As Variant, i As Long
With Me.ListBox1
.Clear ' clear the listbox content
MyUniqueList = UniqueItemList(Range("o4:o100"), True)
For i = 1 To UBound(MyUniqueList)
.AddItem MyUniqueList(i)
Next i
.ListIndex = 0 ' select the first item
End With
End Sub
Private Function UniqueItemList(InputRange As Range, _
HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count > 0 Then
Redim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _
Application.WorksheetFunction.Transpose(UniqueItemList)
End If
End If
On Error Goto 0
End Function
View 2 Replies
View Related
May 3, 2009
I have a userform with many comboboxes and textboxes and I am using the following code to empty those controls:
View 9 Replies
View Related
Jun 6, 2013
Let's say I have Sheet1-Sheet6. I also have combobox1 (with item 1, 2, & 3 as the list items) & combobox 2 (with items 1 & 2 as the list items).
If combobox1 = 1 & combobox2 = 1 then hide Sheets 2,3,4,5,6
if combobox1 = 1 & combobox2 = 2 then hide Sheets 1,2,4,5,6
If combobox1 = 1 & combobox2 = 3 then hide sheets 1,2,3,4,6
If combobox1 = 2 & combobox2 = 1 then hide sheets 1,3,4,5,6
If combobox1 = 2 & combobox2 = 2 then hide sheets 1,2,3,5,6
If combobox1 = 2 & combobox2 = 3 then hide sheets 1,2,3,4,5
I would like to also make both of these combo boxes required fields and to default text to say 'Select One...'
View 1 Replies
View Related
Jun 4, 2008
The spreadsheet is essentially a VBA GUI that validates information entered before writing it to a sheet in the workbook. There is a button on the first sheet that opens the GUI, and when the workbook is first used the GUI opens and runs fine. After adding a few rows using the GUI, saving the workbook, and then reopening it, attempting to open the GUI by clicking on the button will cause Excel to crash (and no errors are given). To compound the problem, it is not possible to find the issue by using the debugger, as the GUI runs fine as soon as VisualBasic is opened. I've tried to narrow it down by using MsgBoxes to find the approximate location where the form crashes, and it seems to happen when the .ListIndex property of a ComboBox is set inside the UserForm_Initialise method. I've played around for days trying to narrow it down further, removing .ListIndex statements as much as possible without breaking the entire thing.
View 2 Replies
View Related
Feb 20, 2012
I Have a sheet with 4 activex comboboxes and 3 text boxes. If the right item is selected in the second combo box a user form opens up. That has 4 text boxes. It has a command button titled ok that takes the information from the 4 text boxes and puts them in a sheet called data.
Code:
Private Sub cmbOK_Click()
With Worksheets("Data").Range("A1")
.Offset(1, 8).Value = Me.txtFirm.Value
[Code]....
I need a way for the above code to run when the ok button on the user form is clicked.
View 7 Replies
View Related
Jun 29, 2006
I have three ComboBoxes. I need the choice of the first ComboBox to detirmine what is shown in the second and the choice in the second to detirmine what is shown in the third. The lists will be growing as users add items.
View 2 Replies
View Related
Mar 23, 2014
This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input.
The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.
Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.
View 3 Replies
View Related
Jul 22, 2014
I'm thinking of writing a small tool to store user information on certain files.
The user data will be input by means of comboboxes on a userform.
Each combobox will need to contain a list of values previously used for that combobox. (I'm planning to use a separate ini file for each combobox)
All of the user data entered for a file will be saved into a single ini file.
Most of this is straightforward however I'm not good with comboboxes. And working this in with Ini files makes it harder!
I'm struggling with the following concepts:
SOLVED - How do to make a combobox populate with all the values from its ini file
SOLVED - How to identify if a selected value in any combobox is new (i.e. wasn't in the original list presented to the user)
SOLVED - (From above) How to write this new value to that comboboxes ini file (so it will appear in future lists for that combobox)
How to input two values from any given combobox
(From above) How to record two values for one field into the files ini file.
View 11 Replies
View Related
Aug 27, 2009
i have a table on sheet1
A B C
(Names) (Total) (Status)
1. John 500 Active
2. Dave 200 Active
3. Andrew 175 Active
4. John 225 Active
On my userform there are 2 comboboxes. In combobox 1 I have Rowsource as Column A (Names), if I select John I want Combobox 2 to list 500 & 225.
View 5 Replies
View Related
Mar 1, 2008
I'm sure this must have been answered before but I cant find it, sorry.....
I have 12 comboboxes on a worksheet. They are normally filled out by typing the first 3 characters of one of the entries in the list they are populated with.
I.e. the list starts;
AAC - Al Arish - Egypt - HEAR
AAN - Al Ain - United Arab Emirates - OMAN
AAW - Abottabad - Pakistan -
The first 3 character are unique to each line.
I'd like to be able to enter the 3 characters in the first combobox & then tab to the 2nd combobox, etc, etc. No problem in a userform but how on earth do I do that when the comboboxes are on the worksheet?
View 9 Replies
View Related
Jun 20, 2006
I am using the following code to determine whether a given range is the linked cell for a Combo Box by looping through the shapes collection.
Function LocateFormControl(OverRange As Range) As Shape
Dim objTemp As Shape
For Each objTemp In OverRange.Parent.Shapes
If Left(objTemp.name, 6) = "Drop D" Then
If WorksheetFunction.Substitute(objTemp.ControlFormat.linkedcell, "$", "") = WorksheetFunction.Substitute(OverRange.Address, "$", "") Then
Set LocateFormControl = objTemp
Exit Function
End If
End If
Next
Set LocateFormControl = Nothing
End Function
However, when I use this code on big worksheets with many other shape objects (such as Comments), the program runs very slowly. Is there a ComboBoxes Collection that I could use to avoid looping through all shapes on the worksheet, or is there a different, faster way to run this code?
View 2 Replies
View Related
Apr 12, 2010
Looking for examples of the following:
1. population a combobox in excel vba using code as opposed from a sheet
2. population a listboxin excel vba using code as opposed from a sheet
3. population a combobox in excel vba using text from a file as opposed from a sheet
4. Getting the values from a combobox on a form to populate cells on a worksheet
These list boxes and comboboxes will be on a form.
View 6 Replies
View Related
Apr 22, 2011
I totally understand how to make the combobox under form controls now but I am not having any success with the indirect function I was using as a list now that I have a combo box. I have attached the current form I am working on that just shows the list function still. How to convert this over to combo boxes with the indirect function?
I attached a second form with the feature I am asking about. It is just lacking the third list that I now have in place. (on the 1st attachment).
Attached Files
File Type: Corp MASTER (3).xlsx
File Type: Quote form (2).xlsx
View 8 Replies
View Related
Mar 3, 2014
Within a userform, I want to populate five combo boxes and with the values of 1 - 10. I looked through several excel websites and found several solutions. However, I could not figure out how to adopt their solutions to my specific case.
My naming convention for the combo boxes are: cmboPeriod1, cmboPeriod2, ... , cmboPeriod5.
[Code].........
I know my with statement is incorrect. I know the compiler will automatically think 'cmboPeriod' is a variable. I just don't know how to get it 'cmboPeriod & j' to do what I really intend for it to do.
View 4 Replies
View Related
Jun 10, 2009
How do I keep users from writing data into ComboBoxes on a UserForm?
View 4 Replies
View Related
Nov 27, 2009
I have 2 comboboxes created on a userform.
This is my code for both of them
View 13 Replies
View Related
Jul 6, 2012
In one worksheet I have a large amount of data (around 300 columns and 1000 rows), growing bigger each day. This worksheet is called "datas_pH" and consists of column A with different categories, column B with different subcategories, and then the rest of the columns with the pH data.
In a different worksheet (called "database") I have 2 comboboxes where you should be able to select the categories and subcategories. For example, in the first combobox if you select A you should only be able to view and select 1, 2, 3, 4, 5. If you select B you should only be able to select 6, 7, 8, 9, 10. (I believe it may be called cascading comboboxes or something along those lines?).
Anyway, here's a code that works perfectly if the comboboxes and data are in the same data sheet:
Combo Box 1:
Code:
Private Sub ComboBox1_Change() Dim d1 As Object
Set d1 = CreateObject("Scripting.Dictionary")
i = 2 ' start at row 2
x = 1
rowValue = Cells(i, 1) ' set rowValue to the value of the first cell (top data row)
[Code] .....
How do I modify these codes so it will pick up data in the other worksheet ("datas_pH"). Combo Box 1 should pick up data from cells datas_pH!a:a, and Combo Box 2 should pick up from cells datas_pH!b:
View 3 Replies
View Related
Sep 10, 2012
I'm trying to fill some combo boxes on a userform with arrays stored as variant type (this is how I was told it's done). The problem is, I'm using exactly the same code in two subroutines and it works in one and not in the other. I have a button to reset the form and a subroutine that runs when the form is initialized. Here's the code for the reset button which works:
Code:
Private Sub Reset_Click()
App1Date = ""
App2Date = ""
App3Date = ""
App1Time.Clear
App2Time.Clear
App3Time.Clear
[code]....
At the moment I've got the 'Journeys' and 'TimeArray' declared as public variables for maximum scope, so that they can be used by both subroutines. But I've also tried declaring them localy in each subroutine and it still doesn't work! By using a breakpoint I can see that the line
Code:
carJourneysSaved.List = Journeys
is definitely running when I expect it to but the combo boxes remain blank unless I click reset!
View 1 Replies
View Related
Sep 27, 2012
Linking two comboboxes. Actually My department consists of 4 divisions and each division contains 3 sections, that is beside Department manager staff and planning group.
In the attached workbook:
- Combobox1 contains All divisions, Department manager staff and planning group)
- Combobox2 contains all sections of the selected division in Combobox1.
What I want to do is the following:
In Combox1, I want to hide all records not related to the selected division.
In Combox1, I want to hide all records not related to the selected section.
Remarks:
I depend for filtering on the org. code for each division and section.
Fifth and Sixth digits of the first Division's Org. Code are 21
Fifth and Sixth digits of the second Division's Org. Code are 22
Fifth and Sixth digits of the third Division's Org. Code are 23
Fifth and Sixth digits of the third Division's Org. Code are 24
First section's org. code for each division ends with 11
Second Section's org. code for each division ends with 21
Third Section's org. code for each division ends with 31
I have created the following VBA codes to reach the said purposes, the first three ptions in Combobox1 work just fine, but the other options sometimes work fine and other times don't!
Moreover, Combobox 2 is not working at all:
Code:
Private Sub ComboBox1_Change()
On Error Resume Next
Application.ScreenUpdating = False
Select Case ComboBox1
Case Is = [AC4].Value
[Code] ........
View 1 Replies
View Related
Nov 28, 2012
I am having some difficulty trying to implement the following. Lets say I have a reference list
X 1
X 2
X 3
Y 4
Z 5
Z 6
I want to have two drop downs on my worksheet. One that filters by x,y,z and then based on the option i select there only give me the corresponding values in the second drop down.
Eg if i select X in my drop down I only want 1,2,3 as options in my second drop down. Or if I select Y only the value 4.
View 2 Replies
View Related
Oct 8, 2009
I have a question about mutually exclusiveness in combo boxes. I know radio buttons are if they are grouped together. I have 2 combo boxes, you can select either 1 or 2.
I want it to be when you select 1 in one of the combobox, it automatically disables you from selecting 1 again in the other combobox. How do i do this without a macro?
View 9 Replies
View Related
Jul 6, 2006
The syntax to use the values from 3 UserForm ComboBoxes as the critiria for a VLOOLUP statement in a UserForm TextBox?
View 9 Replies
View Related
Apr 22, 2008
On my Worksheet, I have a Commandbotton that executes this macro to delete all comboboxes in another sheet.
Sub DeleteShapes()
Dim obj As OLEObject
For Each obj In Sheets("Sheet").OLEObjects
If TypeOf obj.Object Is MSForms. ComboBox Then
obj.Delete
End If
Next
End Sub
This method works most of the time, but sometimes I get this error:
delete method of OLEObject class failed. I red in this forum, that I should set the TakeFocusOnClick Property of the CommandButton to false.
View 3 Replies
View Related
Jul 23, 2014
I am trying to develope a userform in EXCEL 2010 that has a couple of comboboxes that pulls data from a specific worksheet. The first combobox is initialized with data from column "A" of the worksheet, which I can get to work perfectly. But what I am having trouble with is the second combobox, the data that needs to populate combobox2 is dependent on combobox1 results which are the column headings ("B" thru "E"). What I want to do is search row 2 find the result from combobox1 and populate combobox2 with the data below that result. Below is my data that I am working from.
TableData.jpg
View 9 Replies
View Related
Jun 30, 2014
I have 2 combo boxes dependent on one combo box. When I select an item from the dropdown list in ComboBox1, I want the corresponding data attached to ComboBox1 to be available in ComboBox 2 and ComboBox3.
Example:
ComboBox1 ComboBox2 ComboBox3
John Male English
Data for ComboBox1 are in column A2 (Names), data for ComboBox2 are in column B2 (Gender) and data for ComboBox3 are in column C2 (Language spoken).
I chanced upon a code written by royUK from this Excel Forum, which works well for 2 ComboBoxes. This is the code:
Option Explicit
Dim rSource As Range
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
[Code].....
View 2 Replies
View Related
May 14, 2013
Before i start and to save you time looking at my profile, i am using excel 2010. So i have one worksheet with loads and loads of Combo boxes (form control) that are linked to a cell on another sheet and that sheet uses the linked cell along with a index and match formula in one to fetch data from a range for my chart. So based on the selection of the combo box option the chart changes.
My question is how do i change the size of the text in the Combo boxes (form control)? Is there any VBA to do this? .Font.Size?
If not i don't mind changing all my boxes to Combo boxes (activeX control), but i do have over 100 so what would be the best and fastest way to change all my current Combo boxes (form control) to Combo boxes (activeX control) whilst keeping everything the same so it all still functions. Maybe some more VBA? Plus i also need to be able to change the size in the new Combo boxes (activeX control) as well so telling me how would be splendid.
View 1 Replies
View Related
Jun 5, 2009
I have the following workbook (attached) and it is in various states of disarray but I have two questions concerning it;
First, there are three ComboBoxes that need to be linked to each other in the form. When I say this, I mean that depending on the user, it will be easier for them to enter the property name, versus the property number or AFE number or visa versa. Is there an easy way to have it so that when any of the three boxes (WELL NAME OR YARD, PROPERTY NUMBER, or AFE NUMBER) are selected, the other two populate with the corresponding data?
Second, there is a tab to enter new property details (the tab is not finished) as additional wells go on line. I can get it to the point where the input data will appear on the bottom of the PROPERTY DATA sheet. However, how can I create a dynamic range that will include this new information in the corresponding ComboBoxes on the first sheet automatically? Or, if there is a better way then a dynamic range to accomplish this, I would love to hear that too.
View 14 Replies
View Related
Nov 9, 2009
I have 2 comboboxes in the complett worksheets. By selecting some values in this comboboxes, I need some code that will copy automatically some data from worksheets a and b, to worksheet export.
For example if I select "a" and "A26" in the comboboxes, I need range E26:B26( 4 col to the right of A26) from "a" worksheet, to be copied with Paste Special /Values/ Transpose into "export" worksheet in range A1:A50. Also I need in range A51 from worksheet "export" the value "A26" selected in the second combobox. Also in range A52 from worksheet "export" the value from range H40 from "complett" worksheet.
View 8 Replies
View Related
