Help with hidden sheet - trying to unhide in VBA code

[turbotoan]

Note: TESTING is a constant that contains the sheet name.

I am trying to verify if changes to a sheet exist when ThisWorkbook BEFORECLOSE or BEFORESAVE fires off. I have a HIDDEN "TESTING" sheet that is used to track whether or not there are pending changes on one of the visible sheets.

1. This code executes during the BEFORECLOSE (the TESTING sheet is unhidden and re-hidden appropriately)

2. Then this code executes a 2nd time when the BEFORESAVE event fires this off. Only the 2nd time, the TESTING sheet NEVER unhides. Why would that be? I also tried UNPROTECTING the sheet prior to updating the "Visible" property of the sheet, but thad did not solve it.
What ends up happening, is an "Application-defined or object-defined error" when Excel tries to execute the With statement - see "****** ERROR BELOW ******" below.

Any help would be appreciated.

Sub Changes_Exist()
Dim lngLastRow As Long
Dim intLastCol As Integer
Dim rngSearchOC As Range, rngSearchXC As Range
'First do a quick search to see if "OC" or "XC" can
'be found on the TESTING sheet.

'Unhide the sheet
Worksheets(TESTING).Visible = -1
Worksheets(TESTING).Visible = True

Worksheets(TESTING).Select
'UnProtect the sheet
Worksheets(TESTING).Unprotect

' Get the last row and column
GlobalFunctions.Reset_Used_Range lngLastRow, intLastCol
'Find the last row & last column.

'Search for the "OC" cells on the TESTING sheet.
'It only works when you specify cell (1,1)
' ****** ERROR BELOW ******
With Worksheets(TESTING).Range(Cells(1, 1), Cells(lngLastRow,
intLastCol))
Set rngSearchOC = .Find(What:="OC", After:=ActiveCell, LookIn:=xlValues, LookAt :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase :=True)
End With

'Search for the "XC" cells on the TESTING sheet.
...

If (Not rngSearchOC Is Nothing) Or _
(Not rngSearchXC Is Nothing) Then
modCommon.Set_Data_Changed_Flag (True)
ElseIf (rngSearchOC Is Nothing) And _
(rngSearchXC Is Nothing) Then
modCommon.Set_Data_Changed_Flag (False)
End If

'Protect the sheet
Worksheets(TESTING).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'Re-hide the sheet
Stop
Worksheets(TESTING).Visible = False
'Worksheets(TESTING).Visible = xlSheetVeryHidden
End Sub

 

[Damon Ostrander]

Hi turbotoan,

In looking at your code I see only one reason why you need to unhide/rehide the worksheet, that reason being that your code selects the sheet, which is unnecessary.

The same thing applies to the sheet protection. Since your code never attempts to modify the sheet in any way, unprotecting it should be unnecessary.

The key to referring to cells on a worksheet without selecting the sheet d(making it active) is to EXPLICITLY refer to the sheet in qualifying each range object with the worksheet object. I believe the following modification of your code should work:

Sub Changes_Exist()
Dim lngLastRow As Long
Dim intLastCol As Integer
Dim rngSearchOC As Range, rngSearchXC As Range
'First do a quick search to see if "OC" or "XC" can
'be found on the TESTING sheet.

With Worksheets(TESTING)

' Get the last row and column
GlobalFunctions.Reset_Used_Range lngLastRow, intLastCol
'Find the last row & last column.

'Search for the "OC" cells on the TESTING sheet.

With .Range(.Cells(1, 1), .Cells(lngLastRow,
intLastCol))
Set rngSearchOC = .Find(What:="OC", After:=.Cells(1), LookIn:=xlValues, LookAt :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase :=True)
End With

'Search for the "XC" cells on the TESTING sheet. ...

If (Not rngSearchOC Is Nothing) Or _
(Not rngSearchXC Is Nothing) Then
modCommon.Set_Data_Changed_Flag (True)
ElseIf (rngSearchOC Is Nothing) And _
(rngSearchXC Is Nothing) Then
modCommon.Set_Data_Changed_Flag (False)
End If

'Worksheets(TESTING).Visible = xlSheetVeryHidden
End With
End Sub