Update Textbox after selection

[Kevin R]

I have a userform with a combo box that contains office locations. I also
have a textbox for the address of the location. I can update the textbox
with the address after the user selects the office from the combobox?
Basically, the user select the office and the address pops into the textbox.
It's not pulling from any database, its just a few offices so I would just
hardcode the addresses within the vba code. Thanks!

 

[Kevin R]

I meant to say HOW CAN I update the textbox with the address after the user
selects the office from the combobox?

 

[Jay Freedman]

Here's one way: Store the addresses as a second column in the combo
box's data array, and make it invisible there. Then, whenever a name
is selected in the first column, copy the second column to the text
box. Here's a short sample:

Private Sub UserForm_Initialize()
Dim Ary(1, 2) As Variant

Ary(0, 0) = "Fred"
Ary(1, 0) = "1 Rock Rd"
Ary(0, 1) = "Barney"
Ary(1, 1) = "36 Cave Blvd"
Ary(0, 2) = "Pete"
Ary(1, 2) = "The Big House"

With ComboBox1
' assign the array to the combo box
.Column = Ary
' make the first column 90 pt wide
' and hide the second column
.ColumnWidths = "90;0"
' initially select the first entry
.ListIndex = 0
' require exact match
.MatchRequired = True
End With

End Sub

Private Sub ComboBox1_Change()
On Error Resume Next
With ComboBox1
' fill the text box with the second
' column of the selected item
TextBox1.Text = .Column(1, .ListIndex)
End With
End Sub

Note that both the array subscripts and the combo box's column and row
numbers are zero-based. So, for example, the first column is column 0
and the second column is column 1. Also note the oddity that the
..Column assignment transposes columns and rows from the array (see the
VBA help about the Column property).

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Kevin R]

I used a variation of your example that works for me. Thanks.