Comparing two sheet objects (not content)
In the context of error handling code, I would like to check if the user has provided the current sheet with the same name to another in the same workbook (action is prohibited, of course). So I intuitively tried to check it out, just loop through all the sheets and compare the names:
For Each sh In ThisWorkbook.Sheets
If sh.Name = ThisWorkbook.ActiveSheet.Name Then
'error handling here
End If
Next sh
However, this is a huge fall in logic in the case when:
1) The user edits, say, sheet number 3; 2) A sheet with the same name is at position No. 5;
In this case, the condition sh.Name = ThisWorkbook.ActiveSheet.Name
will be met exactly, because the sheet is compared to itself.
So, I'm wondering: how to understand if sh
notThisWorkbook.ActiveSheet
?
I thought there was a problem she could simply solve with a simple object comparison:
If (sh Is Not ThisWorkbook.ActiveSheet) And (sh.Name = ThisWorkbook.ActiveSheet.Name) Then
but this causes a compilation error, namely Object does not support this property or method. Can anyone help me find a logic flaw in my code structure?
OTHER INFORMATION
I tried to manage the case through Err.Description
and Err.Number
, but the first one is OS specific and the second one is for other types of errors. I need to handle it differently.
In addition, the sheets (names and content) are contained in the add-in .xlam
, so the user can modify the content through the user's custom forms, but not through Excel.
In general, let's say that I would like to know how I can perform a comparison, even if it is possible in this particular case, in order to use this method for future developments that I already plan to do and which cannot be managed with a VBA error handler default.
source to share
Just check the index of the worksheet along with the name. Only error (or whatever) if the name is the same but the index is not.
Option Explicit
Public Sub test()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Dim wsToCheck As Worksheet
For Each wsToCheck In wb.Worksheets
If ws.Name = wsToCheck.Name And ws.Index <> wsToCheck.Index Then
'do something
End If
Next
End Sub
Of course, you can always just check the uniformity of an object with an operatorIs
or inequality in your particular case.
Public Sub test2()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Dim wsToCheck As Worksheet
For Each wsToCheck In wb.Worksheets
If Not ws Is wsToCheck Then
'do something
Debug.Print ws.Name
End If
Next
End Sub
source to share
There is no reason to iterate over the sheet collection. Use this:
Function IsWshExists(ByVal wbk As Workbook, ByVal wshName As String) As Boolean
Dim wsh As Worksheet
On Error Resume Next
Set wsh = wbk.Worksheets(wshName)
IsWshExists = (Err.Number = 0)
Set wsh = Nothing
End Function
Using:
If Not IsWshExists(ThisWorkbook, "Sheet2") Then
'you can add worksheet ;)
'your logic here
End If
source to share