Solved

Code if sheet has been created

Posted on 2008-10-21
10
364 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook Free & Paid Tools
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

763 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