Can't call a Sub

[TheRedCardinal]

Hi all,

I want to run a Subroutine when a user of my workbook leaves a sheet. I have written the code in the ThisWorkbook section of the book. It is called "Update Table".

I entered this in the Worksheet code section:

Private Sub Worksheet_Deactivate()
 
Call UpdateTable

End Sub

But it tells me the Sub is not defined.

Can I only run self-contained code in the Worksheet code section? Because there are 70 sheets that I want to do this with, and that's a lot of code to maintain when we make changes!

 

[Fluff]

Place the sub in a standard module & then you should be ok.

 

[joeu2004]

Use:

Call ThisWorkbook.UpdateTable
or
ThisWorkbook.UpdateTable    ' without Call keyword; read VBA Call Statement help page for subtle differences

But in ThisWorkbook.UpdateTable, be sure to fully qualify worksheet references. Things might not be what you (I) expect.

When called from Worksheet_Deactivate, ActiveSheet is the newly __activated__ worksheet, not the deactivated one (!).

To confirm, enter the following codes into a new workbook with Sheet1 and Sheet2. Then alternately select Sheet1, Sheet2, Sheet1, Sheet2.

In the ThisWorkbook object:

Public Sub mysub(s As String)
MsgBox "mysub " & s & ":  " & vbNewLine & "activesheet: " & ActiveSheet.Name
End Sub

In each worksheet object:

Private Sub Worksheet_Activate()
ThisWorkbook.mysub "activate"
End Sub

Private Sub Worksheet_Deactivate()
ThisWorkbook.mysub "deactivate"
End Sub

 

[TheRedCardinal]

Thanks both.

The problem identified by @joeu2004 is an issue as the exit routine is designed to perform tasks on the sheet that is moved away from.

I have done this in my testing by just allocating the sheet as Worksheet Variable WS1

How could I do this by identifying the actual worksheet that has been closed?

 

[RoryA]

Why is the code in ThisWorkbook if it is specific to a particular sheet? Why not put it in the code module for that sheet? That way, any unqualified Range or Cells calls will refer to the sheet with the code.

 

[TheRedCardinal]

Thanks Rory,

Some background might be worthwhile.

It is not specific to a particular sheet. There are 78 sheets, and they are all identical but relate to different products. I wanted to save the user the trouble of having to press a button to refresh the summary table (which is created by the main code) so wanted it to run automatically when they clicked away.

This might be an inefficent way of doing it. Perhaps instead a worksheet change function is best?

 

[RoryA]

In that case you could use the Workbook_SheetDeactivate event, which has a Sh parameter that is the sheet being deactivated. You can pass that to the relevant routine so that it can qualify all ranges with that sheet.

 

[TheRedCardinal]

That sounds promising.

I have tried the following:

Private Sub Worksheet_Deactivate(ByVal Sh As Object)
    
Call UpdateTableTest(Sh)

End Sub

Then in my main module:

Sub UpdateTableTest(Sh As Object)

And then later on:

Set WS1 = WBk1.Sheets(Sh)

I get a Compile Error in the Deactivate routine - Procedure declaration does not match the description of event or procedure having the same name.

The highlighted line is Private Sub line.

 

[RoryA]

No, it's a Workbook level event, so it goes in the ThisWorkbook module, and looks like this:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
   
UpdateTableTest Sh

End Sub

alternatively, if there are a lot of sheets that this should not apply to for example, you could keep your worksheet level events as they are but pass Me to the routine:

Private Sub Worksheet_Deactivate()
    
UpdateTableTest Me

End Sub

 

[TheRedCardinal]

Ah ok.

So could I exclude specific sheets:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Select Case Sh
   
Case <Sheets I don't want>
Exit Sub

Else

UpdateTableTest Sh

End Select

End Sub