Solved

Code if sheet has been created

Posted on 2008-10-21
10
359 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
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
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
Comment Utility

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
Comment Utility
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
 
LVL 6

Expert Comment

by:Jeews
Comment Utility
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
Comment Utility
For your second question, there is an answer on the Ozgrid forum

http://www.ozgrid.com/forum/showthread.php?t=63211
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
Guys,

Pls refresh before posting.

Thx

Dave
0
 

Author Comment

by:hugoohta
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now