Help creating formula in VBA

[HansV]

You can have only one Worksheet_Change event procedure per worksheet. You can add the code that I posted (minus the Sub ... and End Sub lines) to the Sub that you have.

[Asher]

2. Target is the range consisting of all cells that have directly been changed by the user. The Intersect is used to check whether any of the relevant cells have been changed,

• What if the Target cells are being indirectly changed by a formula. Will this still work?
• Also, what if only one cell in the Target range changes, will it still update?
• And finally, for learning and curiosity purposes, why do the "NOT Intersect - Is Nothing"? ...Is there conversely an "Intersect - Is Something"?

[HansV]

The Worksheet_Change event only occurs when the value of a cell is changed directly by the user or by VBA, not when the result of a formula changes (use the Worksheet_Calculate event for that).

It doesn't matter whether one cell is changed or several.

Target is the range of all cells that have been changed directly. This is usually only one cell, but it is possible to change multiple cells simultaneously, e.g. by selecting a range and pressing Delete, or by selecting a range, typing something and pressing Ctrl+Enter to fill all cells in the selection.
Intersect(Range("N1,AF303:AQ303"), Target) is the range consisting of all cells that are part of the Target (i.e. that have been changed) and that are also in N1,AF303:AQ303. If there are no such cells (i.e. if the changed cells are all ouside the range N1,AF303:AQ303), Intersect returns Nothing. Nothing is not really a value, but an empty object. Because it's not really a value, we can't test with =, we must use the keyword Is.

If we used

If Intersect(Range("N1,AF303:AQ303"), Target) Is Nothing Then

we would check whether the intersection is empty. By using

If Not Intersect(Range("N1,AF303:AQ303"), Target) Is Nothing Then

we check whether the intersection is *not* empty.

[Asher]

Wow Hans! Thanks for the clear translation of that! I swear every time I try to look these things up the descriptions are more vague than the term I'm researching.

Ok, so It's clear that I need to use the WorkSheet_Calculate but when I switch to that I get an error saying "Procedure declaration does not match description of event or procedure having this same name."

I think it doesn't like the

Code: Select all

"ByVal Target As Range"

for this one and it highlights

Code: Select all

"If Not Intersect(Range("N1,AF303:AQ303"), Target) Is Nothing Then"

So can I not use Target with calculate? How would that line of code work then?

[HansV]

Indeed, the WorkSheet_Calculate event does not have a target. The procedure header is

Private Sub WorkSheet_Calculate()

and *not*

Private Sub WorkSheet_Calculate(ByVal Target As Range)

This means that you can't check which cells have been recalculated - the code will be executed whenever *any* cell is recalculated. So WorkSheet_Calculate has a much larger impact on performance than WorkSheet_Change. You should only use it if really necessary.

[Sundog]

Asher, sounds like a great idea; how exactly do I

unhide your personal.xlsb?

Mine is in my XLSTART folder; its file properties does not have Hidden checked, and clicking it, or trying to open it from Excel > File > Open, produces the last three spreadsheets I had open, none of which are personal.xlsb.

[HansV]

Personal.xlsb is not hidden at the folder (Windows) level, but within Excel.
- Start Excel.
- Activate the View tab of the ribbon.
- Click Unhide in the Window section.
- Select Personal.xlsb, then click OK.

x214.png

When you want to hide Personal.xlsb again, make sure it is the active workbook, then click the Hide button.

[Sundog]

Hans to the rescue!

Is there a reason I SHOULD hide it again?

[HansV]

Is there a reason I SHOULD hide it again?

Yes, under normal circumstances, you don't want Personal.xls(b) to be visible, you only want its macros and functions to be available. If it is visible, you might accidentally start editing its worksheet(s), but you shouldn't use those.

[Asher]

I always hide it so I don't confuse it with another sheet (I usually have many open at once) and end up saving (or not saving) changes that are imperative to the working of my personal.xlsb file.

[Asher]

This means that you can't check which cells have been recalculated - the code will be executed whenever *any* cell is recalculated. So WorkSheet_Calculate has a much larger impact on performance than WorkSheet_Change. You should only use it if really necessary.

Ok, I think it's necessary, I still need to be able to run these formulas from vba in a protected sheet and what I think is happening, is Excel is reading the Formula and .Value switch as a user trying to change a cell so it is erroring me out even with the protection under UserInterface.

So, I've tried to adjust the code, put it back in Sheet 1, and I get "Subscript out of range"

Here's what I have:

Code: Select all

Private Sub Worksheet_Calculate()
         With Worksheets("Sheet 1")
            .Unprotect "password"
                With Range("D50")
                    .Formula = "=IFERROR(IF('Sheet 2'!$N$1>=D$47,D8,""""),"""")"
                    .Value = .Value
                End With
                With Range("E50:O50")
                    .Formula = "=IFERROR(IF('Sheet 2'!$N$1>=D$47,D50+E8,""""),"""")"
                    .Value = .Value
                End With
             .Protect "password"
         End With
 End Sub

-Asher

[HansV]

On which line?

[Asher]

Correction to previous for continuity...

The highlighted line is: With Worksheets("Sheet 1")

[HansV]

Try replacing

With Worksheets("Sheet 1")

with

With Me

I'd add a line

Application.EnableEvents = False

at the beginning of the procedure, and

Application.EnableEvents = True

at the end, to prevent an endless loop.

[Asher]

HANS YOU ARE A GENIUS!!!!! It's totally working! I'll have to keep "Me" in mind in the future. It seems to be useful in many areas.

I can't wait until I can get good enough at this stuff to actually help others.

Thanks bunches Hans!

[Asher]

Um, however, is there any reason the formulas in my other sheets would not work right?

[HansV]

Please provide enough details - I can't do much with general statements.

[Asher]

I wondered if the EnableEvents = False would be causeing the formuls that feed the AF303:AQ303 line to not be working now.

So, sheet 2 AF303:AQ303 is the total line of a range that is filled from formulas referenceing cells within sheet 2, well, now the fill formulas are not updating when the data from the cells change. Let me know if you need a specific example.

[HansV]

Could you post a stripped down copy of the workbook that demonstrates the problem but that doesn't contain any sensitive data? It has become too difficult to understand what's going on.

[sdckapr]

Target is the range of all cells that have been changed directly. This is usually only one cell, but it is possible to change multiple cells simultaneously, e.g. by selecting a range and pressing Delete, or by selecting a range, typing something and pressing Ctrl+Enter to fill all cells in the selection.

Just to add to your list, another very common multiple cell "Target" range comes from Copy/paste operation. When a range of cells is copied and then pasted into another range, all the pasted cells that have new values in it would be the range of the Target.

Steve