About Us

Stephan Leduc[_2_]

Fantastic Joel. It worked.

"Joel" wrote:

Something like this. I would use a listbox instead of a combobox.

Private Sub ListBox1_Change()
off = ListBox1.ListIndex
email = Sheets("Sheet1").Range("B1").Offset(off, 1)
End Sub

Private Sub UserForm_Initialize()
ListBox1.Clear
ListBox1.RowSource = "Sheet1!B1:B7"
End Sub

"Stephan Leduc" wrote:

Joel:

If I have my rep name in row B and I have their email address in row and I
want my form to show the rep name in the combo box but I want to select the
email in row C, how can I do this using the linked cell function in Excel ?

Thanks

Stephan

"Joel" wrote:

You should be using the linkedcell property of the combobox to fill the
combobox. the click routine is where you would put the code I provided. The
code only gets the email address in the column next to the managers name.
You have to put the variable MEmail into the send to section of your code.

"Stephan Leduc" wrote:

Thanks Joel for the fast answer.

Where do I write this code ?

Thanks

Stephan

"Joel" wrote:

MName = AccountManagerName.AccountManagerName
with sheets("AccountManagerName")
set c = Range("B2:B15").find(what:=MName,lookin:=xlvalues, lookat:=xlwhole)
if c is nothing then
msgbox("Could not find Manager : " & MName)
else
MEmail = c.offset(0,1)
end if
end with

"Stephan Leduc" wrote:

I have a form with different combo boxes.

The row source for each combo box is in different sheets.

For example, I have one sheet for account manages info.

I have 3 columns, ID, AccountManagerName, AccountManagerEmail.

For my combo box, my row source is AccountManagerName, so the names are
displayed.

My code is:

Private Sub AccountManagerName_DropButtonClick()
AccountManagerName.AccountManagerName= "AccountManagerName!B2:B15"
End Sub

My form is dedicated to store info within a sheet and send email. In my code
I need to insert the email address of the AccountManagerName selected from
the combo box.

In my "sendto" statement, how can I selected the email address which relates
to AccountManagerName selected by the user in the combo box ?

Thanks in advance for any help.

Regards

Stephan

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
clear specific cells in a form	vdmbqb	Excel Discussion (Misc queries)	1	November 24th 07 08:07 PM
Form. to ignore cells w/o value from calc from previous Form.	Michele	Excel Worksheet Functions	2	October 30th 07 02:40 PM
Linking cells for a form	Melody	Excel Worksheet Functions	3	September 7th 06 08:16 PM
Average form cells containing formulas?	Edmdas	Excel Worksheet Functions	7	March 7th 05 03:59 PM
Activating VB form by changing cells	CTInt04	Excel Programming	3	June 30th 04 12:13 AM

Menu

About Us