if selected from combo box

Muascott

New Member
Joined
Oct 27, 2005
Messages
38
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Muascott,

see the property ComboBox1.ListIndex
here is an example
Code:
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

New Member
Joined
Oct 27, 2005
Messages
38
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

New Member
Joined
Oct 27, 2005
Messages
38
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

New Member
Joined
Oct 27, 2005
Messages
38
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top