Verify if sheet name still exist
Hi Edmond
I forgot this, sorry.
Option Explicit
'// Placed both in ThisWorkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'// Macro
On Error Resume Next
SheetExistTest
On Error GoTo 0
End Sub
Private Sub Workbook_Open()
'// Macro
On Error Resume Next
SheetExistTest
On Error GoTo 0
End Sub
'******
'// Placed in Module
Sub SheetExistTest()
--
Best Regards
Joergen Bondesen
"Joergen Bondesen" wrote in message
...
Hi Edmund
Try this, please
Option Explicit
Const hil As String = "Best Regards fro Joergen Bondesen"
Const SheetNameSheet As String = "Sheet1"
'----------------------------------------------------------
' Procedure : SheetExistTest
' Date : 20060709
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Does sheet(s) exist.
' Note : Sheets names is in 'SheetNameSheet' from
' cell A1
'----------------------------------------------------------
'
Sub SheetExistTest()
Dim Lastrow As String
Dim Shrange As Range
Dim cell As Range
Dim wkSht As Worksheet
Lastrow = Sheets(SheetNameSheet).Cells _
(Sheets(SheetNameSheet).Rows.Count, 1).End(xlUp).Row
Set Shrange = Sheets(SheetNameSheet).Range("A1:A" _
& Lastrow)
For Each cell In Shrange
On Error Resume Next
Set wkSht = Worksheets(cell.Value)
If Err < 0 Then
MsgBox "Sheet: " & cell.Value _
& " do not exist." & vbCr _
& "Macro will terminate.", vbCritical, hil
GoTo xit
End If
On Error GoTo 0
Next cell
xit:
Set Shrange = Nothing
End Sub
--
Best Regards
Joergen Bondesen
"Edmund" wrote in message
...
If possible, it will be excellent if you can demonstrate it with an
array.
--
Edmund
(Using Excel XP)
|