Populate / Fill Combobox From Range Name?

Feb 22, 2008

I have a userform (uf_PaxInput) with a Combobox (cmb_flight_dest) which I am trying to populate from a worksheet range ("Dest") using the following code:

VB:
Sub LoadForm()
With uf_PaxInput.cmb_flight_dest
For Each Item In Range("Dest")
.AddItem
Next Item
End With
uf_PaxInput.Show
End Sub

The code seems to run okay however whent the form loads the combobox drops down to show only blank items to select. These blank items number them same number of items that are in my named range ("Dest").

View 6 Replies


ADVERTISEMENT

Populate Combobox With Range Names

Aug 15, 2007

All I want to do is
put a combo box on a spreadsheet,
have it populated, and sorted - ideally, alphabetically with range names already created.

And

I'd like to be able to click on the Range Name and GoTo the location.

View 9 Replies View Related

Populate ComboBox With Named Range

Jan 9, 2007

I use

Set rnData = ActiveSheet. Range("ProductList")
vaData = rnData.Value

With Me.model
.Clear
.List = vaData
.ListIndex = -1
End With

to populate a combobox list. 'ProductList' is a Dynamic Named Range with the definition

=OFFSET(Stock_List!$L$2,0,0, COUNTA(Stock_List!$L:$L)-1,1)

The '-1' allows for a heading at the top of the column.
This works fine when the list in col. L has more than one entry under the heading. But with only one entry I get 'Runtime error 381 - Could not set the List property, Invalid property array index' and the debug highlights on

.list=vaData

View 9 Replies View Related

Populate Combobox With 2 Column Range

Jul 11, 2007

I would like to populate a combobox with text from two different ranges:

I have an ID No in column A (range = ID)
and a descripton in Column B (range = Description)

I would like the following to appear in the combobox:

"J.034 Kitchen Bench"
at the moment I can only get J.034 from the following
Private Sub UserForm_Initialize()
Dim cBase As Range
Dim ws As Worksheet
Set ws = Worksheets("Matdata")
For Each cBase In ws.Range("ID")
With Me.cboBase
.AddItem cBase.Value
.List(.ListCount - 1, 1) = cBase.Offset(0, 1).Value
End With
Next cBase
Me.cboBase.SetFocus
HideTitleBar Me
End Sub

View 9 Replies View Related

Populate Userform Combobox From Range In Different Workbook

Jul 28, 2012

I am looking to fill out a combobox (say combobox1) in a userform from a set of ranges located in a different workbook (that is in a different location).

For example, the cells that would need to be in the combobox are B5:B41,D5:D40 and F5:F45 and the file location is W:WCWORKCENTERS.xls

View 3 Replies View Related

Populate ComboBox And TextBox With Horizontal Range?

Nov 14, 2012

I've done define name to read column C until G in 1st row, N define another name from column C until G in 2nd row.For example; NameRanges for "Year" =OFFSET(Sheet2!$C$1,0,0,1,COUNTA(Sheet2!$1:$1)-2), for
"Thickness" = OFFSET(Sheet2!$C$2,0,0,1,COUNTA(Sheet2!$2:$2)-3)

I want to display list of year in combobox and then, when the user click on the first item in combobox, the textbox will display the value that correspond to the combobox. For example; when the user click Year = 1990 in the combobox, the textbox will take value from excel and display thickness for year 1990 which is 4.

This is my code, the code in UserForm_Initialize is working. unfortunately, coding to display value in TextBox1, doesnt work.

VB:
Private Sub UserForm_Initialize()

Dim rngYear As Range [code].....

View 9 Replies View Related

Automatically Populate Combobox With The Data In A Certain Range

Mar 12, 2009

I would like to know how to automatically populate a combobox with the data in a certain range. Attached is a simple workbook with the example. I know how to do this with the additem. But I dont know how to create the array or userform initialize to populate the combobox with the range.

View 3 Replies View Related

Populate Combobox In VBA With Only Filled Cells From A Range

Nov 2, 2013

I have a combobox on a spreadsheet it is populated on a sheet called teams :

The Format control has an input range of: Teams!$B:$B

Now this has a few thousand empty cells in the range but I would need it to only populate with the cells that are not empty.

View 3 Replies View Related

Populate Userform Combobox With Unique Values From A Range

Jul 26, 2012

I found this thread which deal with populating unique values in my listbox.

Here is the thread link. [URL] ........

What these line of code mean (the ones in red).

Code:
Private Sub UserForm_Initialize()

Dim v, e
With Sheets("maintenance").Range("c2:c500")
v = .Value
End With
With CreateObject("scripting.dictionary")

[Code] ......

Note : I simple tried to check the value of v by giving msgbox (v), just below the line v = .Value.

But I got run-time error 13 : Type mismatch error.

View 5 Replies View Related

Populate ComboBox With Specific Sheet Column Range

Mar 4, 2008

I am attempting to populate a ComboBox ( named "SRnumber2") on a userform (named "UpdateSR"), but have hit a brick wall. The following testing code was working but now it is not:

Private Sub UpdateSR_Initialize() 'This section of code initializes the drop-down boxes.

'Add list entries to SR Number combo box. The value of each
'entry matches the existing SR Information spreadsheet entries in column "A"

Sheets("SR Information").Select

SRnumber.ColumnCount = 1
SRnumber.RowSource = "A2:A200"
SRnumber.BoundColumn = 0 ...........

View 9 Replies View Related

Dynamically Set Combobox List Fill Range

Jan 1, 2010

I have a combobox whose list is filled with different ranges, on different sheets, selectable using option buttons. The code is shown below:

Private Sub OptionButton1_Click()
Dim ray
Dim Last
ray = Sheets("Trades").Range("B2:B500")
If OptionButton1 = True Then
With ComboBox1
.List = ray
.ListIndex = 0
End With
End If
End Sub

View 9 Replies View Related

Fill UserForm ComboBox With Values In Range

Aug 15, 2008

I have created a User form with Combobox's, which in turn look up cell references in another sheet. In order to make maintainence of the combobox's as easy as possible I'd like the look up as many cells as possible, therefore anyone can easily add additional text to go into the user forms. However I don't want the blank spaces to show until something has been added.

View 9 Replies View Related

Fill Form's Combobox With Filtered Range

Mar 6, 2007

i have a range name (9 columns) and i filter for a certain type (Shrub) and i would like to only fill the combobox (on a form) with the filtered selection...?

myfilter = "Shrub"
Range("testmaterial").AutoFilter Field:=2, Criteria1:=myfilter
ComboBox1.RowSource = testmaterial

View 5 Replies View Related

Fill Combobox With Unique List Range

Feb 25, 2008

i have a list of surnames in column A in a spreadsheet i want to use this for the source of the combo box also i want this list to be unique and if the surname is not present i want to be able to put the name in the combo box and add it to the sheet

View 2 Replies View Related

Fill ComboBox With Cell Range And Link ComboBox To Cell?

Oct 15, 2006

I have two questions.

1). I have dates listed in excel (01/01/2006 to 12/31/2006) say from range B12:B376.

How do I get these values to a ComboBox.

2). If the above is possible and if I select a value in ComboBox say 08/07/2006, it should get updated in a particular cell say A1

So selection of 08/07/2006 in ComboBox, should reflect 08/07/2006 in cell A1.

Note: This ComboBox is on the UserForm and not on the Excel Sheet.

View 7 Replies View Related

Excel 2007 :: VBA Auto-populate ComboBox With Range Of Data In Spreadsheet

Sep 20, 2012

I'm trying to get some vba to automatically find the range of data in a spreadsheet (So start from a specified point and loop until it's blank) and populate a combobox with this range accordingly. I have read numerous articles and posts but nothing seems to work!!

I am using Excel 2007.

View 5 Replies View Related

Auto Populate 2nd Combobox Based On 1st Combobox Data

Mar 30, 2014

I need to populate two combo boxes from excel sheet, the data will be like below:

Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102

So from the above data, one combo box should hold unique values A & B.

On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.

So the data should be like below:

If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.

Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.

View 1 Replies View Related

Populate Combobox Based On Selection Of Other Combobox

Jun 12, 2006

I have a table, headers "FirstName" and "SurName".

Further a Userform with 2 Comboboxes "FirstName" and "SurName"

I'd like to choose the FirstName (say Jack) in the "FirstName" combobox, and based on that get the choice of the Surnames of all my Jacks in the "SurName" combobox.

Actually my sheet has much more fields and comboboxes, but i think my problem is just that I do not find a way to populate them dynamically.

View 9 Replies View Related

Populate A Combobox In A Userform

Oct 22, 2007

I want it to populate a combobox in a userform. Should it go in the code segment for the userform, in a module, or some other place? The following is code I originally found (by Leith Ross of this board) to find the last row in column "A", and load the combo box "ComboBox1"

View 3 Replies View Related

Populate A Combobox From Data

Jan 26, 2009

able to populate a combobox from data elsewhere in the workbook as follows:

View 7 Replies View Related

Populate Textbox From A Combobox ()

Jul 3, 2009

It's a customer form where I need the contact person (txtContact) to be filled out when selecting the customer (in the cboCustomer).

The data is listed on the sheet "Customer", where all the customer names are in column from B2. The contact names are from C2 in the same sheet.

I have made a list in the namemanager called Contactlist (=OFFSET(Customer!$C$2;0;0;COUNTA(Customer!$C:$C)-1;1)

I have this VBA for getting the customer to the cbobox

View 9 Replies View Related

Populate ComboBox In VBA With Data

Jan 15, 2013

I have created a combobox1 and I have written a code to populate the box the problem is when I run the form and select the data from the dropdown list it gives me an error. I can see the data but just can't select it. I have a command button that I use to save the data I select and insert it into fields on my worksheet. All I just need the drop down box to let me select the data I chose below is my code.

Private Sub ComboBox1_Click()
ComboBox1.Value = .Range("A2:A14")
End Sub

View 2 Replies View Related

Populate Based On Combobox Value

Jun 27, 2014

i would like to create a vba that will populate based on the comboboxes value

like this code

Code:
'for cmb1
'if cmb1 = major 1 then
'if range"F4" is empty then
'copy sheet1 range"A5:R5" to sheet2 range"F4"
'if cmb1 = major 2 then
'if range"F4" is empty then
'copy sheet1 range"A16:R16" to sheet2 range"F4"

[code]....

View 1 Replies View Related

Populate Combobox From Access

Dec 29, 2008

i have the following
Dim cn As ADODB.Connection
Dim qc As ADODB.Recordset
Dim Equip_File As String
Dim Equip_ID As String
Dim Equip_Param As String

Set cn = New ADODB.Connection
Set qc = New ADODB.Recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\ServerDISK 1UncertaintiesUncertainties.mdb"
'Populate QC Number Dropdown menu
qc.Open "SELECT [Equipment_QC] FROM [Equipment_Table]", cn, adOpenStatic
With ComboBox12.List
.Clear
Do Until qc.EOF
.AddItem
qc.MoveNext
Loop
End With
Equipment_Title.Text = rst.Fields.Item("Equipment_Title").value
qc.Close


however, i cant seem to populate the list, i get error :424 object required

is this because the first record in the db has the Equipment_QC field blank? i need to be able to have blank fields..

View 9 Replies View Related

Populate Variable With Combobox Value

May 17, 2006

I'm looking to change the value of a variable using the selected value of a combobox. I've used the following

Dim DatabaseVal As String

'then on selecting my option from the combo box: "JVU" or "VPU" are the only two options.

DatabaseVal = cmbDbase.Value

When I try and use the variable 'DatabaseVal' to populate a value in another workbook (the workbook contains a reference to the add-in running the above macro), the cell just gets populated with a blank value. I'm sure I'm doing something stupid, but can't see what.

View 3 Replies View Related

Populate A Two Column Combobox

Dec 1, 2006

I am trying to populate a combo box with two columns of data read from two consecutive (or non consecutive) columns of data. The following code is what I have come up with after visiting several of the previous threads on similar topics (couldn't find one that gave a general, concise solution).

Private Sub UserForm_Initialize()
Dim lngRow As Long
Dim lngRowtot As Integer
Parameters1.SG1.Clear 'Clear combobox SG1 in userform named Parameters1
'Find the number of rows required for the array and combo box
lngRow = 12 'Start searching on row 12
Do While Sheets("Timber Properties").Range("H" & lngRow).Value <> "" 'Read until empty col H lngRowtot = lngRow - 11
lngRow = lngRow + 1
Loop
Dim MyArray(1 To lngRowtot, 1 To lngRowtot) As String 'I get an error with the last ingRowtot on this line.....................

View 2 Replies View Related

Set Array From Cell A5 To Last Row To Populate Combobox

Nov 7, 2012

I have a VBA form and on form initialize I want to populate a combo box… My array to populate it with could be a massive list so I need to set my array before it populates

I have this code so far but I need my array to be from A5 to the last row in sheet 1

I know how to set an exact range but not one that changes.

VB:
Dim myArray As Variant
myArray =
With Me.ComboBox1
.List = myArray
End With

Here is a sample of the data I need to populate in the combobox.

client_ref
A0008
A024
AA005
AA009

[Code] ....

View 8 Replies View Related

Combobox To Populate Multiple Cells?

Aug 5, 2014

I am looking for a way to have 1 combo box, but be able to click on any cell in a range and yield an individual results. In this particular case I am tracking maintenance, and what my crew's corrective actions were for each location. As this varies day by day I cannot use HLookup or VLookup. Also, the lists vary, and have over 100 options.

What I would like is to be able to click in the cell, and instead of scrolling through a list of 109 options be able to start typing the action and it to auto populate. This gets tedious when there are often 50+ locations with maintenance calls per night.

Furthermore, keeping the results exact for VLookup that is being used on another sheet.

I've attached a blank sheet. Columns B, E, F, & H each would ideally have their own combo box. I could click on any cell in the column and when I begin typing the option it would autofill to the correct list item.

Their respective List are currently to the right under columns J, L, N, & P with their headers matching the exact name they've been given.

View 2 Replies View Related

How Can I Populate The Combobox From Cells In An Xl Sheet

Jun 10, 2008

I have successfully used the course booking form as a base for an input form in an accounting workbook that has several pages. (Thanks to Roy and others for the info; the course booking form on [url]is the best explained piece I have found on the net)

What I would like to do is use a range of cells to populate the combobox so that a user can make a choice from the account descriptions (from a sheet named Intro, b63:99) and have the form return the account number (Intro, a63:a99) to the relevant cell elsewhere in the workbook. The user would then have meaningful descriptions rather than numbers to choose from.

My coding for the form thus far is as follows.

View 10 Replies View Related

Populate Several TextBoxes From A ComboBox Selection

Mar 28, 2009

I need to link the textboxes of a form to the appropriate cells associated to the selection made by a combobox. And in the process I need to be able to Edit one of those Textboxes on the fly while the rest will be locked to the user.

Not sure if the editing of the Notes section can be real time of if it must be updated through a button.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved