[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

Code if sheet has been created

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
hugoohta
Asked:
hugoohta
  • 2
  • 2
  • 2
  • +3
2 Solutions
 
Dave BrettVice President - Business EvaluationCommented:
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
 
aflockhartCommented:

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
 
JurgenDBCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JeewsCommented:
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
 
JurgenDBCommented:
For your second question, there is an answer on the Ozgrid forum

http://www.ozgrid.com/forum/showthread.php?t=63211
0
 
Dave BrettVice President - Business EvaluationCommented:
Guys,

Pls refresh before posting.

Thx

Dave
0
 
hugoohtaAuthor Commented:
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
 
aflockhartCommented:
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
 
JeewsCommented:
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
 
Chris BottomleyCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now