-
Feb 18th, 2008, 09:04 AM
#1
Excel VBA - Does Sheet Exist
I can do
Code:
If Blah = True Then
Sheets("My Sheet Name").Visible = True
End If
And that works fine, but I want to alter this to check whether or not the sheet even exists. I don't know what it does like, but I can tell you it does not like this
Code:
If Sheets("My Sheet Name").Name = True Then
So, how to I determine whether or not a sheet exists in my workbook?
-
Feb 18th, 2008, 09:20 AM
#2
Hyperactive Member
Re: Excel VBA - Does Sheet Exist
Hi Hack,
I have been using the following.
VB Code:
Function WorksheetExists(TWbk As String, WName As String) As Boolean
' Returns True if a worksheet WName exists in Workbook TWbk
Dim Counter As Long
WorksheetExists = False
Counter = 1
Do
' The Worksheet names are Case Insensitive, hence the
' Upper Case conversion in the comparison.
If UCase(WName) = UCase(Workbooks(TWbk).Worksheets(Counter).Name) Then
WorksheetExists = True
Exit Do
End If
If Counter = Workbooks(TWbk).Worksheets.Count Then
Exit Do
End If
Counter = Counter + 1
Loop
End Function
Someone else might be able to come up with a more elegant solution but I know this one works.
Signature Under Construction
-
Feb 18th, 2008, 11:32 AM
#3
Re: Excel VBA - Does Sheet Exist
I'll give it a shot. Thanks Torc!
-
Feb 19th, 2008, 03:59 AM
#4
Re: Excel VBA - Does Sheet Exist
Hi Hack
Another way to do it is...
vb Code:
Private Sub CommandButton1_Click()
Dim WSName As String, Found As Boolean
Found = False
'Replace Sheet3 with the name of the relevant sheet
WSName = "Sheet3"
'loop thru the names of the sheets
For i = 1 To ActiveWorkbook.Sheets.Count
If UCase(WSName) = UCase(ActiveWorkbook.Sheets(i).Name) Then
Found = True
Exit For
Else
Found = False
End If
Next i
'Display Status
If Found = False Then
MsgBox "Sheet Doesn't exist"
Else
MsgBox "Sheet exists"
End If
End Sub
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Feb 19th, 2008, 07:33 AM
#5
Re: Excel VBA - Does Sheet Exist
Originally Posted by Torc
' The Worksheet names are Case Insensitive, hence the
' Upper Case conversion in the comparison.
Yes, worksheet names are Case "Insensitive" (ie. Not Case Sensitive), so do not need to use Upper Case conversion to compare, unless the Option Compare Binary are used.
@Hack, This is a much simpler way to check whether a worksheet name exist without a loop:
Code:
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("YourSheetName")
If Err > 0 then
MsgBox "Worsheet 'YourSheetName' Does Not Exist!"
Err.Clear
Else
MsgBox "Worsheet 'YourSheetName' Found!"
End If
On Error Goto
-
Feb 19th, 2008, 08:10 AM
#6
Re: Excel VBA - Does Sheet Exist
Thank you koolsid and anhn.....I will give these a try as well.
-
Aug 21st, 2012, 01:26 PM
#7
New Member
Re: Excel VBA - Does Sheet Exist
Hack, here is another option...
Option Explicit
Option Base 0
Option Compare Binary
DefLng A-Z
Function DoesSheetExist()
Dim SheetName As String, DoesWorkSheetExist As Boolean
SheetName = "My Sheet Name"
DoesWorkSheetExist = CycleThroughSheets(SheetName)
If DoesWorkSheetExist = True Then MsgBox """" & SheetName & _
""" worksheet does exist.", vbOKOnly, "Sheet Exists!" Else _
MsgBox """" & SheetName & """ worksheet does not exist.", vbOKOnly, _
"Sheet Does Not Exists!"
End Function
Function CycleThroughSheets(ByRef SheetName As String) As Boolean
Dim Wks As Excel.Worksheet
For Each Wks In Worksheets
If Wks.Name = SheetName Then: _
CycleThroughSheets = True: Exit Function
Next Wks
End Function
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|