if selected from combo box

[Muascott]

hi all,

have created a combo box with a list with names and addresses in it in one column. if you select a name and address from the list how do i get excel to place a phone number and email address in another cell which also is on the same sheet as the names and addresses but in another column?

thanks

 

[erik.van.geit]

Hi, Muascott,

see the property ComboBox1.ListIndex
here is an example

Option Explicit

Dim rng As Range
Dim LR As Long
Const FR = 2

Private Sub CommandButton1_Click()
Cells((ComboBox1.ListIndex + FR), 1).Offset(0, 3) = "here you are"
End Sub

Private Sub UserForm_Initialize()
LR = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A" & FR & ":B" & LR)
ComboBox1.List = rng.Value
End Sub

kind regards,
Erik

 

[Muascott]

thanks erik i will have a go at it

 

[Muascott]

I have cut and paste the macro into my command button and combo box, but I get an error

Application Defined or Object defined error
Cells((ComboBox1.ListIndex + FR), 1).Offset(0, 3) = "here you are"

Do i need to input the cell range which contains the address list

 

[erik.van.geit]

sorry, my code was meant to apply on a userform
because of your statement

how do i get excel to place a phone number and email address in another cell

I thaught you had a userform with also some textboxes to fill in the cells with phonenumbers, etcetera

so if you don't want to use a userform:
how is your combobox "fed" with data ?
do you have code for it or did you specify the ListFillRange ?
how will you fill in the phonenumbers ?

 

[Muascott]

in properties for my combo box "list fill range" i have selected Sheet3!N2:N477 if i select one of the names in this range I want the name to appear on sheet 1 in cell C6, additional to that i have on sheet3! K2:K477
the Phone numbers which if the name From say N3 is Selected then It will put the name in Sheet 1 C6 and the phone number from K3 into C7 on sheet 1. how does that sound?

thanks erik

 

[erik.van.geit]

you could use a validation-list for C6 on sheet1 (you will need a named range since the list is on another sheet)

comboboxcode could be
Sheets("Sheet1").Range("C6") = Combobox1
you could also use the LinkedCell property: no code needed !
(syntax Sheet1!C6)

take a look at VLOOKUP to put the phonenumber in the next cell

 

[Muascott]

I have used this:


Private Sub ComboBox4_Change()
Application.EnableEvents = False
Application.ScreenUpdating = False
Range("C6").Value = ComboBox4
ComboBox4.Visible = True
ToggleButton6.Value = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


with my toggle button:
Private Sub ToggleButton6_Click()
Application.EnableEvents = False
Application.ScreenUpdating = False
ComboBox4.Visible = ToggleButton6.Value
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


when i click the toggle button the combo box appears, then i select the
name from the list.
the only problem im having is if name select from list then corresponding phone number placed in C7 on sheet 1

does that sound right?

thanks erik

 

[Muascott]

erik can i send you a copy so you can see it run?

 

[erik.van.geit]

the only problem im having is if name select from list then corresponding phone number placed in C7 on sheet 1

did you try VLOOKUP as suggested ?
you really don't need code to fill in cell C7

erik can i send you a copy so you can see it run?

I can see it run without copy Since your only problem is C7 which can be solved with a formula, you're almost there.
Try VLOOKUP and if any problems feel free to reply
(if you've got a good reason not to use vlookup as a formula, please explain a bit)

best regards,
Erik