Solved

Code if sheet has been created

Posted on 2008-10-21
10
365 Views
Last Modified: 2010-07-27
Hi I have some quick code questions:

1- Is there a vba code that checks if a specific sheet exists? (has been created)
2- Also, I need a vba code that overrides a warning msg to delete a sheet:
like the 'Microsoft excel msg: Are you sure you wish to delete this sheet?'

0
Comment
Question by:hugoohta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22766011
You can test for sheet existence as below

to remove the warning on sheet deletion use

Application.DisplayAlerts = False
prior to the deletion, remember to set it back to
Application.DisplayAlerts = True

when finished

Cheers

Dave

Dim ws As Worksheet
On Error Resume Next
Set ws = Sheets("mytest")
On Error GoTo 0
If ws Is Nothing Then MsgBox "sheet does not exist"

Open in new window

0
 
LVL 17

Expert Comment

by:aflockhart
ID: 22766014

sheetexists = 0
For Each x In ActiveWorkbook.Worksheets
If x.Name = "Sheet 1" Then sheetexists = 1
Next
If sheetexists = 1 Then
MsgBox ("SHeet exists")
Else
MsgBox ("SHeet does not exist")
End If



0
 
LVL 4

Expert Comment

by:JurgenDB
ID: 22766020
for your first question:

Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
SheetExists = False
On Error GoTo NotaSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NotaSheet:
End Function
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 6

Expert Comment

by:Jeews
ID: 22766028
Supress messages by
Application.showalerts = False

and Check the sheet by

Dim loSheet As Object

On Error Resume Next

Set loSheet = ThisWorkbook.Worksheets("Sheet4")

If loSheet = "" Then
    MsgBox ("Sheet Not Found")
End If

0
 
LVL 4

Expert Comment

by:JurgenDB
ID: 22766031
For your second question, there is an answer on the Ozgrid forum

http://www.ozgrid.com/forum/showthread.php?t=63211
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22766040
Guys,

Pls refresh before posting.

Thx

Dave
0
 

Author Comment

by:hugoohta
ID: 22766041
The code that I need to check if a sheet exists is not for display purposes...
so the user doesnt need to know
but its more when the programme is running following a button press
and I need to create a sheet 'List'
but once the button is pressed again I need it to have a different name
like 'list2' or something
thats why I need to check if it has been created or not

thanks for the help!
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 22766062
Yes, just use the suggestions above and add some code to act accordingly:

e.g.

if sheetexists=1 then mysheetname=mysheetname & "1"

to get it to create a slightly different name.  The detail will depend on how you want the renaming to happen.
0
 
LVL 6

Assisted Solution

by:Jeews
Jeews earned 100 total points
ID: 22766085
Try something like this  

For each loSheet in thisworkbook.Worksheets
          IF LoSheet.Name = "List" then
                    llSheetFound = True
          endif
next
 
Thisworkbook.Worksheets.Add After:=Worksheets(Workbooks.Count)
 
If  llSheetFound then
     ActiveSheet.Name = "List2"
else
    ActiveSheet.Name = "List"
endif

Open in new window

0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 150 total points
ID: 22766126
To name a created sheet allowing for previous versions you can use something like the followng

Chris
Sub namesheet()
Dim ws As Worksheet
Dim sheetstring As String
Dim sheetnum As Integer
 
    Set ws = ThisWorkbook.Worksheets.Add
    On Error Resume Next
    sheetstring = "Template"
    ws.Name = sheetstring
    Do While ws.Name <> sheetstring
        sheetnum = sheetnum + 1
        sheetstring = "Template (" & sheetnum & ")"
        ws.Name = sheetstring
    Loop
    On Error GoTo 0
End Sub

Open in new window

0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Outlook Free & Paid Tools
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question