Solved

Code if sheet has been created

Posted on 2008-10-21
10
360 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
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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

932 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

12 Experts available now in Live!

Get 1:1 Help Now