Trigger Macro on specific cell

[stkarma]

I would like to run a macro when a specific cell is clicked. I have tried a Worksheet_SelectionChange(ByVal Target As Range)macro and it worked however it cleared the undo/redo contents which is a problem. Is there a way to trigger a macro on a specific other than using the SelectionChange event? I am trying to expand the width of a data validation list when a user clicks the drop-down button so the user can view the entire contents of the list. I am using Excel 2000. I cannot merge 2 cells as there is data in the cell to the right and left of this cell.

 

[SkipVought]

Hi,

Can you put a dummy row in your sheet with a long enough string in that column, that an initial AutoFit will suffice during the entire session?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[stkarma]

I only want to expand the column while that cell is being viewed otherwise the column size will affect the output of several different print macros included in the worksheet.

 

[bartrein]

From what i know there is no way of preserving the items on the UnDo List when you run a macro so whichever event you choose it will still clear the the undo/redo contents.

There is something called Application.OnUndo but that will only reverse the results of your macro (and you would have to write the code that does the reversing anyway).

How about Format/Wrap text on the whole column?

 

[stkarma]

just gave the wrap text a try, but it doesn't affect the drop-down list.

 

[SkipVought]

I only want to expand the column while that cell is being viewed otherwise the column size will affect the output of several different print macros included in the worksheet

Well then, in the Print Macros, autofit your columns.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]