Can combobox selection place another value in spreadsheet?

[Astello]

I have a combobox where the user selects a city. The cities come from
a spreadsheet where all the cities are listed in one column, and all of
their zip codes are in the column next to it. How can I allow the user
to pick a city, while putting that city's zip code into the spreadsheet?

 

[Nick Hodge]

Astello

If this is a combobox from the control toolbox (ActiveX) then set the
ListFillRange property to the address of the list (e.g Sheet3!$A$1:$A$100),
then set the LinkedCell property to a cell that will return the value of the
selected item in the combobox. I usually set this to the cell 'under' the
combobox as it will not be seen.

Now set up a formula in the cell next to the combobox that references the
cell with the LinkedCell range (e.g A1)

=VLOOKUP(A1,'YourCityZipRange',2,FALSE)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk

 

[Astello]

It's actually a ComboBox in a UserForm, can I still do the same thing,
or something similar? I'm trying to make it so that the end user never
actually touches the data. Thanks.

 

[Nick Hodge]

Astello

I have a combo box called ComboBox1, a label called Label1 and the data to
load the combobox is in A1:A25 on Sheet1 and the 'lookup' value is alongside
in B1:B25. The event I am using is the ComboBox's _Change event below,
after I have set the ComboBox's ControlSource property at design time to
Sheet1!$A$1:$A$25.

Private Sub ComboBox1_Change()
Me.Label1.Caption =
Application.WorksheetFunction.VLookup(Me.ComboBox1.Value,
Worksheets("Sheet1").Range("Data"), 2, False)
End Sub

(The code from me.label... to ...False) should be on one line)

This will load the associated value for the item selected in the combobox

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk

 

[Astello]

Your logic makes perfect sense to me, but it won't work, probably
because of how I'm inputting the values from the UserForm in the first
place. I have a CommandButton that adds the values entered in the
UserForm to certain cells in the spreadsheet all at once. But when I
insert the code for ComboBox1_Change() it crashes when something in the
ComboBox is chosen. Here is my code:

Private Sub CloseButton_Click()
Unload Me
End Sub
______________

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("CostModelData")

'find first empty row in database
iRow = 2

'check for a destination city
If Trim(Me.ComboBox1.Value) = "" Then
Me.ComboBox1.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 15).Value = Me.ComboBox1.Value
ws.Cells(iRow, 16).Value = Me.txtMCFloor.Value
ws.Cells(iRow, 17).Value = Me.txtDiscount.Value

'clear the data
Me.ComboBox1.Value = ""
Me.txtMCFloor.Value = ""
Me.txtDiscount.Value = ""
Me.ComboBox1.SetFocus

End Sub
_____________

Private Sub ComboBox1_Change()

Me.Label3.Caption =
Application.WorksheetFunction.VLookup(Me.ComboBox1.Value,
Worksheets("Sheet1").Range("Data"), 2, False)

End Sub
_____________

Private Sub UserForm_Click()

End Sub

 

[Nick Hodge]

Astello

It should work. If you have data in a combobox called combobox1 and you
have a table set up on a sheet called Sheet1 with a range name of data (2
columns). If you then change the combobox, the value selected will look up
the value in the data table and display in a label called label3

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk