I have very limited experience with Excel and I have been trying to duplicate something I do in Access and the reason being for people who do not have Access.

I am using

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target

to trigger different macros. I have a bunch of If/Then to determine whether a macro will run. My problem with drop down lists (combo box) is that the macro runs when I click the cell but does not run after the selection is made. I need the data that the drop down inserts in the cell.

Also, I was told by a poster on another forum that ActiveCell is not good to use due to reliability. So far I have based a lot of what I am doing on ActiveCell, Target.Column And Target.Row. Does anyone see a problem with these as I am close to the stage of "cleaning up" the screen for a working version.

Thanks for any help.

If the macro to be fired is dependant on the value in the combo box, don't trigger it from the worksheet. Use the combo box's OnExit event instead.

I've had no problem using ActiveCell, and I've done so since it was demonstrated to me in 2005.

Where do you find it? or are there different ways to make the drop down list.

I put some entries in cells/rows and then to Data/Validate/List and drage down the cells. What I have made only shows the drop down arrow when you click on it. Does that sound right.

If I can get a macro working on after update then that will add heaps of flexibility.

It's quite tricky working in the abstract. Do you have a copy of the workbook in question that you could post? If so, I'll take a look when I get home tonight.

I have attached 3 Word docs with it as they have the bookmarks as you will see on the Excel drop down list they are 3 that are referenced

Macro11 is the main one I am playing with and you will see it is referencing on the Sheet1 event.

Basically, I am trying to do in Excel a system I run on Access to produce Word docs and also the saving and retrieving of them and also a back a system I do.....hence you will see in some of the macros the SaveAs type stuff.

My main problem is how to trigger macros etc as you will see from my attempts

Sorry about the delay in getting back to you. The tail-end of last week was manic.

I've had a look and neatened up the code a bit. I think I know what's going on, but I can see one problem immediately. You've put the master code in the sheet module for Worksheet1 on the SelectionChange event, but you want it to trigger when a value in a cell changes. SelectionChange happens when you move the selection from one cell to another, rather than when a cell changes value. I think that your validated cell (your drop-down list) needs to be replaced with a combo box. Assuming that nothing else goes wrong, I'll have a play tonight and see what comes to mind.

I still haven't had a chance to pick through your code and work out what's what. (Comments are you friend, and even more so the friend of those who come after you!) However, one thing struck me when I trawled the help file. If you copy the code from the worksheet's SelectionChange event to the Change event, it will fire after you pick a new value from the validated list. I tried this, and the code fired when a new value was selected.

Have a try and see if it works for you.

You forgot to attach

I have attached my latest and have made some good progress with the combo on the screen via Data/Validat.

It is macro27 behind it.

Basically, I click on the row (any column will do) on the person who will get the letter and then select the letter from the combo. That then takes me back to the row/cell I clicked on and presses on to use the data on the rwo for the bookmarks.

I am still using the drop down list at E25 for the reference, that is where the combo is inserting the name of the Word.doc.

I think the next step I would like to be able to do, if it is posible, is that when clicking on the row that has the person's details that the action then moves the combo to that row. The way I have it at the moment will not be practical if the spread sheet had a 1000 rows. Perhaps I need to use a combo on a form instead and clicking on the row opens the form.

I think a basic problem I have here (apart from being short on knowlege on Excel) is I am trying reproduce something I do in Access but it is an area that falls into Access's strong points but Excels weak points. The reverse would be having a column in Excel with a row of writing every 10 rows and summing the column, that would be a big deal in Access

I have moved to the form with a combo and have that working and that will eliminate the problem of having a 1000 rows sicne the form will open in the middle of the screen.

The next thing I need to do to duplicate what I di in Access might be a bit hard. In Access, when the letter is run apart from saving the Word doc with a date/time stamp in the file name it also inserts that name in a Many table record for the person. That in turn allows letters to be selected and opened.

At the moment the only way I can thik of approaching that in Excel would be for the name of the Word doc to be saved across the person's row.

Worksheet_Change(ByVal Target As Range) will capture the drop down list change event
& ActiveCell will give u the selected value

Using the combo on a form had done the job for me.