Check is a worksheet exists?

I

Ickle Lynnie

New Member
Joined
Feb 5, 2003
Messages
28
Can anyone tell me the code to check if a worksheet name exists?

A cell, say A1 contains the text Loanbook 1

I want a piece of codes that checks if that worksheet exists and responds TRUE or FALSE in cell B1.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Sub a()
Dim sname As String
Cells(1, 2) = False
sname = UCase(Cells(1, 1))

For Each sht In Worksheets
If UCase(sht.Name) = sname Then
Cells(1, 2) = True
Exit For
End If

Next sht

End Sub
 
Upvote 0
Ickle Lynnie said:
Can anyone tell me the code to check if a worksheet name exists?

A cell, say A1 contains the text Loanbook 1

I want a piece of codes that checks if that worksheet exists and responds TRUE or FALSE in cell B1.
Click to expand...

Here is an Excel function that can do this, Enter it into a module and then use it like any other Excel function.

Code: 
Function TestSheet(ShName) As Boolean
TestSheet = False
For i = 1 To Worksheets.Count
    If Sheets(i).Name = ShName Then TestSheet = True
Next
End Function

(edit)
In Cell B1 enter

=TestSheet("Loanbook 1")
 
Upvote 0
Hi,

As Sean has indicated, these sorts of test are best performed with a separate function that you can reuse as needed.

Try this:
Code: 
Sub WSTest()
Dim strFindMe As String

With Worksheets("Sheet1")
    strFindMe = .Range("A1").Text
    'the sheet to find
    .Range("B1").Value = SheetExists(ThisWorkbook.Name, strFindMe)
    'pass workbook name and sheet name to function to get True or False
End With

End Sub

Function SheetExists(strWbkName As String, strShtName As String) As Boolean
Dim ws As Worksheet

SheetExists = False
'initialise
On Error Resume Next
Set ws = Sheets(strShtName)
If Not ws Is Nothing Then SheetExists = True
Set ws = Nothing
'release memory
On Error GoTo 0

End Function
HTH
 
Upvote 0
s-o-s said:
Here is an Excel function that can do this, Enter it into a module and then use it like any other Excel function.

Code: 
Function TestSheet(ShName) As Boolean
TestSheet = False
For i = 1 To Worksheets.Count
    If Sheets(i).Name = ShName Then TestSheet = True
Next
End Function

(edit)
In Cell B1 enter

=TestSheet("Loanbook 1")
Click to expand...

I have since encountered a problem I think relates to the Function.

Trying to run a recorded macro in a module seems to trigger to Function somehow. I did the following and detected the issue -

Copy the last sheet manually.
Record a macro to copy the details of another sheet to the new one.
Step through the macro just recorded with F8 (or run it) then F8 jumps back to the Function after completion of the macro.

Nasty results thoughout the workbook with all sorts of formula corrupting.
ie #value! errors.

Bit of a nuisance really because I was planning on a couple of buttons to run a couple of macros later but I untiil I resolve this issue macros seem a long, long way away.
DesC
 
Upvote 0
Hi, I have a similar issue, but not sure how to modify to above codes.

Basically, I need the following:
If sheet "USD" exists, run "USD" Macro, otherwise run "Other" Macro.

Is this possible?
 
Upvote 0
boringpie said:
Hi, I have a similar issue, but not sure how to modify to above codes.

Basically, I need the following:
If sheet "USD" exists, run "USD" Macro, otherwise run "Other" Macro.

Is this possible?
Click to expand...
Code: 
Sub Main()
If IsSheetExists("USD") Then
    Macro1
Else
    Macro2
End If
End Sub
 
Function IsSheetExists(ByVal wsName As String) As Boolean
On Error Resume Next
IsSheetExists = Sheets(wsName).Name = wsName
End Function
 
Last edited by a moderator:
Upvote 0
boringpie said:
Hi, I have a similar issue, but not sure how to modify to above codes.

Basically, I need the following:
If sheet "USD" exists, run "USD" Macro, otherwise run "Other" Macro.

Is this possible?
Click to expand...

I am using the following code which does exactly what i want - check if a sheet exists and if it does displays a message and if it doesn't it continues with the code in the rest of the macro (which creates a new sheet of that name)

Code: 
On Error Resume Next
Sheets("sheet1").Select
If Err.Number <> 9 Then
    MsgBox "A sheet already exists, you need to delete it before creating another"
    End
On Error GoTo 0
End If

so it only continues with the rest of the code IF an err.number 9 is produced when it tries to select the (non-existant) sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
156
mikefisher
M
rjplante
  • Solved
Change from Set as Default to Set as Active printer
Replies
1
Views
219
NdNoviceHlp
NdNoviceHlp
JohnZ1156
  • Solved
Simple macro create folder if not exist else move on
Replies
2
Views
121
JohnZ1156
JohnZ1156
MartinS
  • Question
Better Approach to Checking For Existence of Worksheet Level Range
Replies
4
Views
259
Micron
M
gheyman
  • Solved
Copy from one workbook to another (just format and values)
Replies
2
Views
421
gheyman
gheyman

Forum statistics

Threads
1,215,035
Messages
6,122,785
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top