If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto run macro
I have an array (B6:J21) that requires a macro being run (sorting
macro) when any figure in the array is changed. How can I run this macro "on demand"?? |
#2
|
|||
|
|||
Auto run macro
Include the following macro in the wroksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B6:J21") If Intersect(t, r) Is Nothing Then Exit Sub Application.EnableEvents = False Call yoursub Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu2007k "Esradekan" wrote: I have an array (B6:J21) that requires a macro being run (sorting macro) when any figure in the array is changed. How can I run this macro "on demand"?? |
#3
|
|||
|
|||
Auto run macro
From what you have said or pointed me to read, I take it that the
macro will not change or "sort" if there is a change by formula. Is that right?? I tried this, and doesnt seem to work and I suspect that is the cause. Esra Gary''s Student wrote: Include the following macro in the wroksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B6:J21") If Intersect(t, r) Is Nothing Then Exit Sub Application.EnableEvents = False Call yoursub Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu2007k "Esradekan" wrote: I have an array (B6:J21) that requires a macro being run (sorting macro) when any figure in the array is changed. *How can I run this macro "on demand"??- Hide quoted text - - Show quoted text - |
#4
|
|||
|
|||
Auto run macro
Esradekan wrote:
From what you have said or pointed me to read, I take it that the macro will not change or "sort" if there is a change by formula. Is that right?? I tried this, and doesnt seem to work and I suspect that is the cause. Hi Esra, I think you are correct. The Worksheet_Change event is not triggered by a formula calculation. See also http://www.cpearson.com/excel/Events.aspx Would you be able to trap a change in the precedent(s) to the formulae instead? |
#5
|
|||
|
|||
Auto run macro
I do have a workbook where this exact thing happens, a "sort" macro is
executed on a change in the array, ,but it is someone elses work and is password protected. I dont know what the password is. Is there a way that I can view thw code used in that book? That may help me. Esra On Sep 1, 10:20*am, wrote: Esradekan wrote: From what you have said or pointed me to read, I take it that the macro will not change or "sort" if there is a change by formula. *Is that right?? I tried this, and doesnt seem to work and I suspect that is the cause. Hi Esra, I think you are correct. The Worksheet_Change event is not triggered by a formula calculation. See alsohttp://www.cpearson.com/excel/Events.aspx Would you be able to trap a change in the precedent(s) to the formulae instead? |
#6
|
|||
|
|||
Auto run macro
This site explains how Excel password protection works (and doesn't):
http://www.mcgimpsey.com/excel/removepwords.html Esradekan wrote: I do have a workbook where this exact thing happens, a "sort" macro is executed on a change in the array, ,but it is someone elses work and is password protected. I dont know what the password is. Is there a way that I can view thw code used in that book? That may help me. Esra On Sep 1, 10:20 am, wrote: Esradekan wrote: From what you have said or pointed me to read, I take it that the macro will not change or "sort" if there is a change by formula. Is that right?? I tried this, and doesnt seem to work and I suspect that is the cause. Hi Esra, I think you are correct. The Worksheet_Change event is not triggered by a formula calculation. See alsohttp://www.cpearson.com/excel/Events.aspx Would you be able to trap a change in the precedent(s) to the formulae instead? |
Thread Tools | |
Display Modes | |
|
|