run macro on selected sheets only

[AmyTaylor]

Hi,
I have a macro which runs from a floating toolbar and allows the user
to delete the current worksheet they have open.
However, I only want it to run on certain sheets, and be disabled on
others.
Ideally, if the user tried to run it on a worksheet that they shouldnt
delete, something like a messagebox would appear and say "sorry, you
cant delete this sheet". otherwise it would run the delete macro.

I have the following macro already, but it spools thru all the
workbooks and so you need to click the messagebox a dozen times!

'Delete the current sheet
Sub Delete()
Set wkb = ActiveWorkbook
For Each wks In wkb.Worksheets
If Left(wks.Name, 1) = "S" Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Else
MsgBox "Sorry, you cannot delete this sheet"
Set wkb = Nothing
End If
Next wks
End Sub

Any help gratefully received !

Amy xx

 

[peterfarge]

Hallo Amy,

what is your question?
Another posibillity is to set a sheet property:

' Add a property to every sheet that should no be delted:
Call oWks.CustomProperties.Add( "DONT_DELTE", 1)

' Add this code to assure that the user doesnt delete sheets with this
property:
Dim i As Long
Dim bDoNotDelete As Boolean

bDoNotDelete = False
For i = 1 To Sh.CustomProperties.Count
If Sh.CustomProperties(i).Name = "DONT_DELTE" Then
MsgBox "Dont delte this table"
bDoNotDelete = True
End If
Next i

If Not bDoNotDelete Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If





Dim i As Long
Dim bDoNotDelete As Boolean

bDoNotDelete = False
For i = 1 To Sh.CustomProperties.Count
If Sh.CustomProperties(i).Name = "DONT_DELTE" Then
MsgBox "Dont delte this table"
bDoNotDelete = True
End If
Next i

If Not bDoNotDelete Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If

 

[peterfarge]

Excuse me my first question, I have only overflow your posting and have
no "?" seen.

 

[peterfarge]

I have changed your macro. If you only want to delete the current
active sheet when its name beginns with an "S" than this macro could do
the job:

Sub Delete()
If Left(ActiveSheet.Name, 1) = "S" Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Else
MsgBox "Sorry, you cannot delete this sheet"
End If
End Sub

 

[AmyTaylor]

Hi Peter, thank you for your reply - it works great !

thanks again,
Amy xx