goto sheet and email template question

[Velmortis]

Good morning,

I have been trying to solve what seems to be a very basic problem to no avail. I have a droplist of names on multiple sheets (all in cell B3) so I can enter data on a sheet then select the next name and go to that sheet etc. To prevent changing code when a new name is added, I have the names associated to a number by vlookup so the sheet selected is based on the numeric value from the vlookup. The problem is if I select 'Bob (numeric value 6) and go to sheet6, when I select the next person, say 'Larry (numeric 8), it will go to sheet 8 but when I try to enter any data it automatically jumps me back to sheet 6 since the first droplist sheet still has the numeric value on 6. Is there a way to stay on a particular sheet until a new value is selected from the droplist? Additionally I'm trying to find a way that I can click a cell which will email a range of cell data from a template to a selected person from the list using outlook however the template values change week to week.

[tehneXus]

Hi and welcome to the forum,

could you please post at least your code, better a sample workbook, without it we can only guess how your drop down list behave.

[Velmortis]

test run.xlsm

Sorry, I should have attached it from the start.

[tehneXus]

Hi

try this file: test run.xlsm, ideally the code in the ThisWorkbook module will handle all dropdown lists in the file

contains:

Please Login or Register  to view this content.

[Velmortis]

TehneXus your a genius! It worked perfectly thank you. (saved me a lot of grief). Any idea towards my second question with regards to sending an excel template through outlook? Example would be attached. Depending on which week I wanted to send from where you see the 'email:' in the AC column. I basically wanted to simply 'click' on the cell and have outlook open an email to send to the specific person with a preset range of data in the attached Book1 for the respected week that I would click on. If you could assist with how to create that would be appreciated.

[tehneXus]

Hi,

check the code in ThisWorkbook module in: test run.xlsm

For more help I need to know exactly where to take values/mail address from

[Velmortis]

Hi TehneXus, this is essentially what I was looking for. The values to be taken would be from columns G,H,I,J,K,L,M,N,O,P,Q,R to the respective Row and the mail addresses would be under the names sheet in column C where a different email address would be per line to the respective name.

[tehneXus]

Check test run.xlsm

[Velmortis]

TehneXus, you are by far a lifesaver. This is exactly what I was needing. I appreciate your time and effort.