Private Sub Worksheet Calculate
⚡ 👉🏻👉🏻👉🏻 INFORMATION AVAILABLE CLICK HERE 👈🏻👈🏻👈🏻
by CodyR. ∙ Jul 30th, 2014 at 7:50pm
I am attempting to use Private Sub Worksheet_Calculate, to trigger a separate sub when an individual cells value changes.
I initially used Worksheet_change, but I figured out that this doesn't trigger for cells whose value changes based on a formula.
I have queried the internet by means of the mighty Google, ...to no avail.
I have come across similar problems, with solutions such as:
The above solutions don't seem to want to work for me. I'm sure I'm missing something small that actually makes all of them work. I keep changing the code around waiting for it to mysteriously work for me. But I get run-time error 91, saying a variable isn't set to a value before it is used.
If the desired code were to be read in plain English it would sound like this:
"When the formula belonging to B3, changes the value of B3, Go to this Sub to run its commands.
All other cells that change due to calculation, don't do anything."
I know its wrong, but to what makes sense to me the code should be as follows:
Any help would be greatly appreciated!
EDIT: I solved my own problem by means of an alternative route. I decided to remove the formulas in the worksheet, and complete their tasks with vba. This helped me avoid having to use the above code. Thank you all for trying to assist me.
Escul X5 ∙ Jul 30th, 2014 at 8:03pm
Escul - Adding "Call" to my attempted code produces a Run-time error '424': Object Required.
Escul X5 ∙ Jul 30th, 2014 at 8:15pm
ChadEstes ∙ Jul 30th, 2014 at 8:16pm
You can either add Call in front of your subroutine call or drop the parenthesis.
I don't have any variables declared in this Sub, because I am not using any variables as of yet.
Is it seeing target as a variable undeclared? I thought this was a method.
ChadEstes ∙ Jul 30th, 2014 at 8:18pm
Is your SecondarySub also a Private Sub? Is it in the same module?
ChadEstes - Your snippet still gives me the object required error. I have tried with my secondary sub as private public and with neither defined. Same results. It is under the worksheet code.
Here's a dirty way that might work.
edit* dim randomvarible obviously has to go in declarations
ChadEstes ∙ Jul 30th, 2014 at 8:23pm
Cody, target is typically set in the event header. Please note however that the Worksheet_Calculate() event has no arguments. This code would need to be run from the Worksheet_Change event instead.
Another option which is not as pretty, is to store the value in B3 in a cell on another worksheet or in a Global variable. Then in the Worksheet_Calculate() event compare the value of B3 with the previously recorded value to know whether to fire your SecondarySub routine. But it would mean having to record the value of B3 in this other location either in the calculate event or in your SecondarySub routine.
Escul X5 ∙ Jul 30th, 2014 at 8:23pm
i'm assuming its calling SecondarySub() now and your problem lies in there? stick a msgbox at the start of SecondarySub() if your not sure
ChadEstes ∙ Jul 30th, 2014 at 8:24pm
Here's a dirty way that might work.
That would only work if randomvariable was declared in the module and not the sub.
ChadEstes ∙ Jul 30th, 2014 at 8:33pm
Try this code instead and see if it works. Create another worksheet and change the codename to HiddenSheet and then make its visibility VeryHidden.
Escul - I have already done so. I have had this working once before. But another part of my vba script was hitting my secondary sub multiple times while writing to cells other than B3. Which lead me to believe my "If" statement was not effectively filtering the cells that should hit the secondary sub. (B3 only) Reworking the "If" statement is what is causing this mess.
ChadEstes - I am using a similar statement as to the one we are attempting to iron out now, in a Worksheet_Change Sub. With target set in the event header, and it works. So I tried to put it into the Worksheet_Calculate event header as well. That is when I noticed that Worksheet_Calculate won't accept arguments. But I assumed it would still work due to the vast amount of similar situations solved on countless forms I have found.
Mooney - I have tried similar coding with no such luck.
ChadEstes ∙ Jul 30th, 2014 at 8:39pm
Escul - I have already done so. I have had this working once before. But another part of my vba script was hitting my secondary sub multiple times while writing to cells other than B3. Which lead me to believe my "If" statement was not effectively filtering the cells that should hit the secondary sub. (B3 only) Reworking the "If" statement is what is causing this mess.
ChadEstes - I am using a similar statement as to the one we are attempting to iron out now, in a Worksheet_Change Sub. With target set in the event header, and it works. So I tried to put it into the Worksheet_Calculate event header as well. That is when I noticed that Worksheet_Calculate won't accept arguments. But I assumed it would still work due to the vast amount of similar situations solved on countless forms I have found.
Mooney - I have tried similar coding with no such luck.
If B3 is a formula and depends on inputs from other cells, you'll have to monitor the dependents to the formula in the worksheet change event and not B3, because as you stated previously when a recalculation occurs it doesn't capture B3 in the target range.
Mooney - I have tried similar coding with no such luck.
I must be misunderstanding your requirement...
Change a few cells, A1 value remains the same. No popup is triggered
Remove one, which changes the value of a1 and we get a popup:
ChadEstest - I really appreciate your assistance. I tried your code, still gives me "Object Required."
In case this will help, Cell A1 contains ="Title" & " " & "Q" &TEXT(B3, "#") & " " & TEXT(B2, "yyyy"), Cell B2 contains formula =TODAY(), Cell B3 contains formula =ROUNDUP(MONTH(B3)/3,0).
My secondary sub, archives the active worksheet using A1 as filename, quarterly. If the file exists it overwrites it, if it doesn't it creates it.
The sub should be triggered pending the recalculation of B3, the quarter. I used code posted by Gerard as solution to a previous question of mine that can be found here.
However he overwrites the formulas that trigger my subs with VBA native variables.
IPX Dave ∙ Jul 30th, 2014 at 8:58pm
I think this is more what you were going for... target.address returns a cell value, "B3" is a string, not a cell.... Range("B3") or Cells(3,2) is a cell.
You use intersect if you want to also include instances where they select more than one cell and B3 is included in that selection. For your situation it sounds like you're only interested in them selecting on B3 itself though.
Mooney - Worksheet_Change won't work because the cell I am monitoring for change is updated via formula. Which is a calculation(not detected by Worksheet_Change) not a manual change(detected by Worksheet_Change)
In case this will help, Cell A1 contains ="Title" & " " & "Q" &TEXT(B3, "#") & " " & TEXT(B2, "yyyy"),
Largely unrelated to your original request, but you might like to know you can use:
=concatenate("title Q", text(b3,"#")," ",text(b2,"yyyy"))
IPXDave - What you are saying makes perfect sense as to why my "filter" wasn't working. But plugging your code in still returns "Object Required." Am I missing something so blatantly obvious/simple/stupid??
IPX Dave ∙ Jul 30th, 2014 at 9:09pm
IPX Dave ∙ Jul 30th, 2014 at 9:54pm
the proper syntax for using the target.address feature is actually Target.Address...then if you want it to run the check whenever they select any cell it is Target.Address(False, False)
Everywhere
Threads
This forum
This thread
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Thread starter jamescooper
Start date Nov 9, 2016
Hello,
I have reached the limit for rows for a "Private Sub Worksheet_Calculate".
If I add a 2nd "Private Sub Worksheet_Calculate_2" within the same sheet in VBA it doesn't execute the content of this one.
Any ideas please?
Thanks.
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Joined
Nov 12, 2010
Messages
14,177
Office Version
365
2010
Platform
Windows
Mobile
Put the code in various regular subs then call the macro's from the worksheet_calculate or review the code in the worksheet_calculate to make sure you have the code optimised.
Joined
May 26, 2009
Messages
17,480
Office Version
365
2010
Platform
Windows
Hello,
I have reached the limit for rows for a "Private Sub Worksheet_Calculate".
If I add a 2nd "Private Sub Worksheet_Calculate_2" within the same sheet in VBA it doesn't execute the content of this one.
Any ideas please?
Thanks.
I get the error compile error: Procedure too large
Joined
May 26, 2009
Messages
17,480
Office Version
365
2010
Platform
Windows
I get the error compile error: Procedure too large
How many lines of code do you have?
I'm hapy that you've found a way round, but what it really means is that your code is poorly organized. If you find yourself copy and pasting then there's probably a better way.
Threads
1,138,913
Messages
5,690,636
Members
420,572
Latest member
friendlas
We've detected that you are using an adblocker.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Mom Watching Tv
Stepsisters Sex Porn
3d Porno Ass
Brazzers House Part 2
Mature Teachers Com
Private Sub Worksheet_Calculate | MrExcel Message Board
Private Sub Worksheet_Calculate - Herber
excel - VBA - Worksheet_Calculate event triggers over and ...
Private Sub Worksheet_Calculate() ActiveSheet.ChartObjects ...
excel - VBA sub for Worksheet_Calculate is not being ...
Private Sub Worksheet_Calculate() | MrExcel Message Board
Worksheet.Calculate 事件 (Excel) | Microsoft Docs
Private Sub Worksheet Calculate
