Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Code if sheet has been created

Posted on 2008-10-21
10
Medium Priority
?
368 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 400 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 600 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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