LinkBack URL
About LinkBacks
I want a macro to run everytime the value in say cell D6 changes.
Anyone know how to do this?
Many Thanks
I want a macro to run everytime the value in say cell D6 changes.
Anyone know how to do this?
Many Thanks
goto macro window by pressing alt+F11
Select the sheet from left panel. there will be two drop down. from the left drop down select worksheet and from right drop down list select the event change.
to access rows and cols you can use following
hope it helps..
Please Login or Register to view this content.
Ashish
Last edited by VBA Noob; 01-15-2008 at 02:31 PM.
Thanks, i have now tried using this:
The problem now is that the macro does not fire when the value in cell D5 is changed. The macro run on its own works fine.Please Login or Register to view this content.
Any ideas?
Thanks!
Hi,
You need to make sure the macro (which is an "event handler") is located in the code module for the worksheet it applies to.
Right click the worksheet (e.g., Sheet1) in the Project explorer and select "View Code". Copy the macro to the page that brings up. Then it should work any time the value in cell D5 is changed.
Chuck L
Hi,
Tried that, no luck.
Here is the macro that I want to run:
ThanksPlease Login or Register to view this content.
I have just realised that the problem is because the cell D5 where i want changed values to be detected is a validation list where values are changed via a drop down list.
I tested suing another cell where this was not the case.
Does anyone therefore know how to run a macro when the value in a cell that uses a validation list is changed?
Many Thanks
The intersect method will work with one limitation , if the user drops the list and selects the same value that was already selected from the list the macro will run the same as if he had selected a different value.
In this example I named the cell.
If it is neccessary to avoid running the macro when a selection is made but the value doesn't change , you might need to set a variable equal to the value of the cell, and use a nested IF statement.Please Login or Register to view this content.
Last edited by SuitedAces; 01-15-2008 at 06:51 PM.
Thanks,
That works fine when i use msgbox "Test" everytime a selection is made from the list. However, it will not work when I want to initiate a macro when an item in the dropdown is changed.
You must be using xl97...
worksheet_change event doesn't detect the change made via validation dropdown in '97
enter any unused cell =D5
and use worksheet_calculate event instead.
No im definatly using excel 2003.
Im very new to vba so would you be able to guide me further as to how to use Worksheet_calculate? Do i simply just copy the code I had in Worksheet_change?
Thanks
Then the code should work as it is.
1) Most probably EnableEvents property was accidentally set to False.
Or Security setting
2) Have you enabled Macro when open ?
Run following code once and try again
Please Login or Register to view this content.
If the message box works then you should be able to replace the msgbox line with a call to your sub.
Is your procedure a public sub located in a standard module ?
Please Login or Register to view this content.
Last edited by SuitedAces; 01-16-2008 at 11:04 PM.
Sub is public and in a standard module.
Thanks
Well if that is the case then you should post your workbook , because your subroutine should run.
The msgbox has demonstrated that it the event code is firing , now that you replaced the Msgbox line with a call to your sub it won't just decide to ignore the call .
Do you get an error message ?
Last edited by SuitedAces; 01-17-2008 at 01:36 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks