Using 2 cells in the same row within a form

[Stephan Leduc]

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

 

[Joel]

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]

Thanks Joel for the fast answer.

Where do I write this code ?

Thanks

Stephan

 

[Joel]

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]

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]

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]

Fantastic Joel. It worked.

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