CODEWITHSUNDEEP
excelvba
SparklePony
SparklePony

Reputation: 9

Macro "Doesn't Work" From Call on Separate Sheet

This macro works as intended, from a button on the Batch Input sheet:

Sub BatchTriggerOFF()

    Sheets("Batch Input").Unprotect
    Sheets("Batch Input").Range("G3:J3").Value = "Off"
    Sheets("SQL LOGIC").Calculate
    Sheets("Batch Input").Range("A12").Select
    Sheets("Batch Input").Shapes.Range(Array("Group 12")).ZOrder msoSendToBack
    Sheets("Batch Input").Protect

End Sub

However, when BatchTriggerOFF is called from a different sheet in the same workbook, the macro neither changes the Range("G3:J3").Value nor Shapes.Range(Array("Group 12")).ZOrder msoSendToBack. There is no error message.

If Sheets("SQL LOGIC").Range("B1") = "On" Then Call BatchTriggerOFF

I've tried unprotecting the Batch Input sheet beforehand, messing with Sheets("Batch Input").Activate, Sheets("Batch Input").Select, and even tried pasting the BatchTriggerOFF line by line VBA directly into the second macro, to no avail.

What is causing BatchTriggerOFF to seemingly not run when called from the second macro/sheet?

Upvotes: 0

Views: 204

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

[...] something is inherently wrong with the second code I've provided, likely not actively running when the value in Range("B1") is changed?

Exactly. A procedure that's in a standard module needs something, somewhere to invoke it. Could be a shape or button on the worksheet, could be other VBA code, but something needs to invoke it somehow.

No procedure is going to just know to run when Range("B1") is changed on Sheets("SQL LOGIC"): you need to have code that's "triggered" when a cell is changed on that sheet.

The way to do this, is to handle the worksheet module's Change event. Find your "SQL LOGIC" sheet in the VBE's Project Explorer (Ctrl+R), double-click it. In the code-behind module for that specific worksheet, select Worksheet from the left-side dropdown at the top of the code pane; the right-side dropdown should say SelectionChange, and the VBE should have added a private procedure that looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Select Change from the right-side dropdown; the VBE creates a private procedure that looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Now delete the SelectionChange handler, you don't need it unless you need to track cells that the user has selected. Since we want to track cells that have changed, we'll use the Change worksheet event. This procedure will be invoked whenever the user or your code changes anything on that sheet.

Since we only care to run code when a specific cell is changed, we need a condition here, involving the Target parameter. Using the Application.Intersect function, we can get a Range object reference that's Nothing if the two specified ranges don't intersect; we can use this information to bail out if it's not B1 that changed:

If Application.Intersect(Me.Range("B1"), Target) Is Nothing Then Exit Sub

Any code written after that condition inside the Worksheet.Change event handler procedure, will only run after the value of cell B1 was modified - either by the user typing in a value, or by any other code writing to that cell (you need to toggle Application.EnableEvents off if you have to prevent firing that event when it's code doing the changes and you don't want the handler to run).

Now, it looks like cell B1 isn't going to change, rather, it looks like it contains a formula whose result might change after making changes to the "Batch Input" sheet.

If that's the case, then the Change event will not be fired when B1 recalculates and now evaluates to a new value, because the cell didn't change, only its result.

If that's your scenario, then you want to handle the Calculate worksheet event, and have that be your trigger:

Private Sub Worksheet_Calculate()
    If Me.Range("B1").Value = "On" Then BatchTriggerOFF
End Sub

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42256

If you need your sub to be called from any (sheet) module, move it in a module! The function/sub in the sheet module cannot be called without specifying the module name where it belongs, like you will be able to do in a module.

Upvotes: 1

Related Questions