Execute macro automatically after cell update

Thanks Pike. The cell (U8) is being updated by way of a list combo box with three options: Half-Year; Financial Year; and Calendar Year.

When I use the combo box one of these values appears in the cell (U8). So when I make a selection from the combo box I need one of the ranges specified in the macro to be selected.

At present the macro only works after I make a selction from the combo box, and then click somewhere on the sheet. I would like it to work without having to click anywhere on the sheet.

I should add that in its current form the macro has created another problem, namely whenever I click on the sheet it selects the respective range. So I also want to lose this aspect of the macro as well.

Thanks for your help.

Thanks for the suggestion TMShucks. Unfortunately this change stops the macro from working altogether.

Thanks Pike. The issue does not appear to be with the particular cell that is being updated (U8), but with the macro executing after the cell is updated. I have added the file so you can see what is happening. Any help greatly appreciated.

Thanks guys. I copied both pieces of code straight into the VBA editor (separately of course) and neither worked unfortunately.Sorry to be such a pain.

No I want the value of U8 to change when the combo box changes.

I am using excel 2007. Is it possible that this is a problem for the VBA code you have written?

When I click on the combo box with this code the range does not change. If I then click on the cell U8 the debug box appears.

If I click the debug the line of code highlighted is:

Please Login or Register  to view this content.

Not sure if there is an issue with this line?

Hi Pike and TMShucks.

The following code appears to work:

Please Login or Register  to view this content.

This code can probably be improved. If you have any suggestions then please let me know.

Thanks a million for your help.

Hi Pike. Yes. U8 behaves in the same way in the file you sent me.

When you make a selection from the combo box, the same value appears in U8. Does the file behave differently on your computer?

I know that normal combo boxes have a number appear in them.

I copied this combo box from another file that I did not create, so I know not how it does this, or what type of combo box it is.

I have attached my version of this file with the code I posted above.

If the file behaves exactly the same on your computer as it does mine, then you can see the chart adjust to the different ranges as each new selection is made from the combo box.

Cheers.

Sorry TMShucks. Does not seem to do the job. The range of the chart remains the same each time the combo box updates.

Hi Pike,

The first file I uploaded with the original code did not make the chart range adjust each time the combo box updated. However the second file I uploaded with the new code I scratched together achieves this.

The reason I need it work this way is that I don't want gaps in the chart. So there are 8 half-year values, and only 4 finanicial year values. When the combo box goes from half year to financial year I don't want 4 empty cells out the end of the financial year range.

Anyway. The file now works the way I want it to thanks in part to what I learnt from looking at your code, and the code provided by TM.

So many thanks for your help. Will mark this thread as solved.

Cheers,
Carl.