If the sheet exists...., then

[Darin Kramer]

HI there,

Ive got VB runing that creates a sheet called master. Problem is users
sometimes (and in certain cirumstances correctly) re-run the macro, but
then I get an error cause the sheets exists. So Im lookin for VB that
says if sheet Master exists, bring a a message that says "sheet exists,
do u really want to run Macro?" - if you say yes it will delete the
sheet master, and then run the macro, if you say no it will stop!

Ideas welcomed!!!

Regards

D

 

[JE McGimpsey]

One way:


Dim ws As Worksheet
Dim nResponse As Long
On Error Resume Next
Set ws = ActiveWorkbook.Sheets("Master")
On Error GoTo 0
If Not ws Is Nothing Then
nResponse = MsgBox( _
Prompt:="Sheet exists." & vbNewLine & vbNewLine & _
"Do you really want to run the macro?", _
Title:="Error", _
Buttons:=vbYesNo)
If nResponse = vbNo Then Exit Sub
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If

 

[Guest]

Possibly:-

Sub marine()
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name = "Master" Then flag = True: Exit For
Next
If flag = True Then
response = MsgBox("That sheet exists. Continue", vbYesNo)
If response = vbYes Then
ActiveSheet.Delete
Sheets.Add.Name = "Master"
End If
End If
End Sub

Mike

 

[Guest]

Dim sh as Worksheet
Dim ans as Long
On Error Resume Next
set sh = Worksheets("Master")
On error goto 0
if not sh is nothing then
ans = MsgBox("Master Exists - " & vbNewlIne & _
" Hit OK to delete Master and continue" & _
vbNewLine & " Hit Cancel to quit", _
Buttons:= vbOKCancel)
if ans = vbCancel then exit sub
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
end if
' at this point, Master does not exist.

 

[Guest]

Missed a line

Sub marine()
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
sh.Select
If sh.Name = "Master" Then flag = True: Exit For
Next
If flag = True Then
response = MsgBox("That sheet exists. Continue", vbYesNo)
If response = vbYes Then
ActiveSheet.Delete
Sheets.Add.Name = "Master"
End If
End If
End Sub

 

[Darin Kramer]

Thanks!!!!