[RESOLVED] Specify a range for worksheet_calculate method

[Raivis]

Hello everyone!

I wanted to ask if it is possible to set up a range where this worksheet_calculate method would check for changes in the specified range of cells, for example, would look for a changes only in Range("A14:A20")?

Here is the code, and for some reason it is not triggered and cells are not made hidden when the value in any cell of specified range changes:

Private Sub Worksheet_Calculate()
Dim Target As Range
Dim c As Range
On Error Resume Next
Set Target = ActiveSheet.Range("a14:a20").SpecialCells(xlCellTypeFormulas)
If Not Target Is Nothing Then Exit Sub
    For Each c In Range("A14:A20")
        If c = "#hide#" Then
            c.EntireRow.Hidden = True
        ElseIf c = "#show#" Then
            c.EntireRow.Hidden = False
        End If
    Next
End Sub

Seenu helped me with this code, but some how it does not do the work. I have attached the file, in case there is a wish to see the worksheet. Is there anything that I could add to make it work?

Kind regards,
Raivis

[westconn1]

first remove on error resume next
how do you know it does not trigger?
how are you making the worksheet calculate?

If Not Target Is Nothing Then Exit Sub

if any cells in the range contain formulas then the procedure will exit
if no cells contain formulas an error 1004 will occur setting target, but be concealed by resume next

[Raivis]

Hello westconn!

I removed the line, but still if the value in Range("A14:A20") changes to #hide#, the whole row is not made hidden.

How do I know that.. well the line does not hide. It did hide when I used worksheet_change event, but in that case code was not triggered if values were changed by formula.

All the cells in the range have formulas, and if I change some cells out of the range, then values change, but rows are not hidden.

[westconn1]

if any cells in the range contain formulas then the procedure will exit

as some/all the cells contain formulas, the procedure exits without processing the show/hide code

change to

Set Target = ActiveSheet.Range("a14:a20").SpecialCells(xlCellTypeFormulas)
If Target Is Nothing Then Exit Sub
    For Each c In Target
        If c = "#hide#" Then
            c.EntireRow.Hidden = True
        ElseIf c = "#show#" Then
            c.EntireRow.Hidden = False
        End If
    Next

tested, works

[Raivis]

It worked when I copied the code in new workbook and in empty worksheet. It run very fast and very good. BUT, when I added the code to the workbook that I currently work on, then for some reason it runs all the time until message box pops up asking to end or debug. I attached the file, is the same thing happening on your machine? Is it because I have more sheets in the workbook?

[westconn1]

i tested with your workbook in first post, before, i can not open the new attachment as i only use excel 2000

[Raivis]

I converted it to older version. When I open it, it seems it immediately runs the code, for about 5 seconds, and then debug information pops up.

[Siddharth Rout]

>>>I wanted to ask if it is possible to set up a range where this worksheet_calculate method would check for changes in the specified range of cells, for example, would look for a changes only in Range("A14:A20")?

What do you exactly want to do?

Sid

[Raivis]

Hello koolsid!

OK, I have this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    If Not Application.Intersect(Target, Range("C8:C19")) Is Nothing Then
            For Each c In Range("A20:A32")
                If c.Value = "#hide#" Then
                    c.EntireRow.Hidden = True
                ElseIf c.Value = "#show#" Then
                    c.EntireRow.Hidden = False
                End If
            Next
   End If
End Sub

It checks if user manually modifies any cell in Range("C8:C19") and presses enter. If it does, then it checks if any cell in range ("A20:A32") has #hide# or #show# value, and correspondingly hides or shows the whole row. The problem is that this code does not work if I have formula in Range("C8:C19") which refers to other cells or worksheet. And if I change the other cells, then value in Range("C8:C19") changes, but code is not triggered, because user did not press enter on any cell in the Range("C8:C19").

That is why Worksheet_Calculate is used now. But now the problem is that every time user modifies anything, it takes a long time while excel recalculates everything. So the idea now is to set up a range where Worksheet_Calculate method would check for changes. Westconn1 modified the code and it works fine in a new empty workbook, but when I copied the code to my workbook, it suddenly goes error.

[Siddharth Rout]

A much better explanation Raivis

Ok 2 things.

If the formula refers to cells in the same worksheet then that can be handled. But if the cells refer to another worksheet then it can be tricky (if you know what I mean)

1) If the formula refers to other cells in the same worksheet then you can use Worksheet Change event and track the changes in the cells "A20:A32"

2) If the formula refers to cells in the other worksheet then you can use Worksheet Activate event and track the changes in the cells "A20:A32"

Let me know if you want an example?

EDIT

Try this. (Untested)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
    
    Application.EnableEvents = False
    
    
    CheckChanges Range("A20:A32")

LetsContinue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Private Sub Worksheet_Activate()
    On Error GoTo Whoa
    
    CheckChanges Range("A20:A32")

LetsContinue:
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Sub CheckChanges(aRange As Range)
    Dim aCell As Range
    
    Application.ScreenUpdating = False
    
    For Each aCell In aRange
        Select Case aCell.Value
            Case "#hide#": aCell.EntireRow.Hidden = True
            Case "#show#": aCell.EntireRow.Hidden = False
        End Select
    Next
End Sub

Sid

[Raivis]

Ok.. what should I add to code above if formula refers to other cells in the same worksheet. Maybe it will work also if these other cells in the same worksheet will refer to other sheet, and it wouldn't be necessary to be tricky

[Raivis]

But the strange thing is that this code of Westconn works perfectly in a new workbook:

Private Sub Worksheet_Calculate()
Dim Target As Range
Dim c As Range
If Not Target Is Nothing Then Exit Sub
    For Each c In Range("A20:A31")
        If c = "#hide#" Then
            c.EntireRow.Hidden = True
        ElseIf c = "#show#" Then
            c.EntireRow.Hidden = False
        End If
    Next
End Sub

I tested again, if I paste it in my workbook (see attachment 6), then if value changes in the range, then the codes goes and never stops, until i press escape and then debug info appears. Why is that?

[Siddharth Rout]

While you were posting, I was updating the code above

BTW, I am using Activate Event assuming that you would be making changes to the other worksheet and then coming back to the main worksheet.

Now consider this scenario as well. Your workbook is on a Network drive and the cells refer to another worksheet in a different workbook on a network drive. Now if a different user makes changes to that workbook then definitely your workbook will get updated. In this case Activate Event() is useless. Like I mentioned it is tricky

Sid

[Siddharth Rout]

I tested again, if I paste it in my workbook (see attachment 6), then if value changes in the range, then the codes goes and never stops, until i press escape and then debug info appears. Why is that?

You need to sandwich the code between

Application.EnableEvents = False
'~~> Your Code
Application.EnableEvents = True

Sid

[Raivis]

Hey! Your code runs so fast as a rocket. It even forks if these other cells are linked to other sheet! That is wow..

I don't want to sound unthankful, but the thing with your code, is that I can not undo when I edit some other cells, I believe it is because code is triggered on every edit. In the worksheet change event I could use undo if I did not edit the specified range cells. Is there a way to implement that, otherwise it is quite risky to use this, you know.. But it works without a hitch!

[Raivis]

Well.. you handled every possible way the data could be modified, even on network drive!!!


EDIT: I am really surprised how good this code works! Immediately after I delete the data from other cell, the corresponding row gets hidden or show, in a tenth of a second. To be honest, I did not expect it would work so fast.

[Siddharth Rout]

Hey! Your code runs so fast as a rocket. It even forks if these other cells are linked to other sheet! That is wow..

I don't want to sound unthankful, but the thing with your code, is that I can not undo when I edit some other cells, I believe it is because code is triggered on every edit. In the worksheet change event I could use undo if I did not edit the specified range cells. Is there a way to implement that, otherwise it is quite risky to use this, you know.. But it works without a hitch!

Yes the code will clear the undo list. However there is a way to preserve the undo list and and reset it but I guess that all together is a different subject

If you want to explore more on that then checkout this piece of code...

UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

The .List(1) stores the last undo. Similarly, you can get the complete list and rebuild it again

HTH

Sid

[Siddharth Rout]

Well.. you handled every possible way the data could be modified, even on network drive!!!


EDIT: I am really surprised how good this code works! Immediately after I delete the data from other cell, the corresponding row gets hidden or show, in a tenth of a second. To be honest, I did not expect it would work so fast.

No, unfortunately, it doesn't handle it in "NetWork Drive Case". You could use Selection Change Event for that but that will slow you down...

Sid

[Raivis]

Sid, thank you very much for your time! I hope this article will help anyone who will need alike feature.

I'll try to google on UndoList feature, because I must add it for sure!

Westconn, thank you to, this one is resolved.

All the luck you guys!

[Siddharth Rout]

Glad to be of help

Sid

[JuanCuban]

Wow, excelent work there, Im wondering if you guys could help me in something similar, Im getting live data from a link on my excel spreadsheet, and i got a code that saves the data everytime it changes, the problem is that it only works with worksheet_change, which means it dosent work because it wait for user action to save, now the problem of using worksheet_calculate is that it fires everytime a calculation is donde, and I need it to target it to a change to a specific cell, heres the code i have for saving with the change function, i wanna do exactly the same, but this being triggered by a calculate event on a especific cell:

Private Sub Worksheet_Change(ByVal target As Range)
''Do something only if the value changes in cell A1
If target.Address = "$CH$4" Then
''Look at the full list below the Target title
With ThisWorkbook.Names("STA10A").RefersToRange.CurrentRegion
''Look at the cell at the bottom of the list
With .Offset(.Rows.Count, 0).Resize(1, 1)
''Enter the current time in the cell
.Value = Now
''Enter the new value to the right of the time
.Offset(0, 1).Value = target.Value
End With
End With
End If
End Sub

thanks in advance!

[NewBee2021]

I have tried your suggestion but doesn't work for some reason, can you suggest what would be the possible reason?

Public Sub Worksheet_Calculate()
Dim Target As Range
Dim c As Range
Set Target = Range("F7:F500").SpecialCells(xlCellTypeFormulas)
Dim AllottedDeliveries As Integer
Dim Restaurant As String
Dim Difference As Integer
Dim Overage As Integer
Dim MonthlyCost As Integer
If Not Target Is Nothing Then Exit Sub
For Each c In Range("F7:F1000")
AllottedDeliveries = c.Offset(0, -3).Value
Restaurant = c.Offset(0, -5).Value
Difference = c.Offset(0, -1).Value
Overage = c.Offset(0, 2).Value
MonthlyCost = c.Offset(0, 3).Value
If c.Value <> "OK" Then
If AllottedDeliveries = 40 Then
If c.Value = "T2-O1" Then
Mail_small_Text_Outlook AllottedDeliveries
ElseIf c.Value = "T2-O2" Then
Mail_small_Text_Outlook AllottedDeliveries
ElseIf c.Value = "T2-O3" Then
Mail_small_Text_Outlook AllottedDeliveries
End If
End If
If AllottedDeliveries = 100 Then
If c.Value = "T3-O1" Then
Mail_small_Text_Outlook AllottedDeliveries
ElseIf c.Value = "T3-O2" Then
Mail_small_Text_Outlook AllottedDeliveries
End If
End If
End If
Next c
End Sub