Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

add message box to the code

0

hello

I  have  this   code  deletes  the  specific  sheets  ,  but   if   I   continue  running   then  it  shows error   because   the  sheets  are  deleted  ,so  to  overcome   the error    I  would add message box  when  run repeatedly  then  show  the  message  " the  sheets are already deleted"

Sub delsh()
Dim ws As Variant
Application.DisplayAlerts = False
ws = Array("ss", "dd")
Worksheets(ws).Delete
Application.DisplayAlerts = True

End Sub

thanks

Answer
Discuss

Answers

0
Selected Answer

Speed

If you want a message if the delete fails, On Error Goto... can be used in conjuction with a loop through the array and trying to delete each sheet.

In the revised code below, the code jumps when an error occurs to the line ErrorHandling (and returns after the name of the sheet is captured). I've called the array WsArr (rather than ws) and shown major changes in bold (plus some explanatory comments):

Option Explicit

Sub delsh()

Dim WsArr As Variant
Dim NotFound As String, r As Integer, n As Integer

WsArr = Array("ss", "dd")

Application.DisplayAlerts = False
    On Error GoTo ErrorHandling
    For n = LBound(WsArr) To UBound(WsArr) 'loop through array
        Sheets(WsArr(n)).Delete ' causes an error if sheet doesn't exist
    Next n
Application.DisplayAlerts = True

If NotFound <> "" Then r = MsgBox("Sheets not found/deleted: " & NotFound, vbOKOnly)

Exit Sub

ErrorHandling:
    NotFound = NotFound & WsArr(n) & ", " ' add undeleted sheet to list
    Resume Next 'carry on with loop

End Sub
It will delete the sheets first time but next time advise if any can't be deleted. I didn't bother to add the list of the successfully deleted files to the message.

Hope this works for you.

Discuss

Discussion

honestly   I  like  your  answer    just  I  want  learn  how  show  the  message  with  the  condition 
many  thanks  for  your  supporting 
speed (rep: 40) Jun 10, '21 at 8:36 am
Thanks Speed. Sorry but I'm not clear what you mean by " I  want  learn  how  show  the  message  with  the  condition "- the code shows you, right?

If you don't want the user to be informed which sheets could not be deleted, this will deliver a simple message:
Sub delsh()
 
Dim WsArr As Variant
 
WsArr = Array("ss", "dd")
 
Application.DisplayAlerts = False
    On Error GoTo ErrorHandling ' anticipate deletion error...
    Sheets(WsArr).Delete ' error arises if a sheet doesn't exist
 
Application.DisplayAlerts = True
    
Exit Sub
 
ErrorHandling:
    r = MsgBox("One or more sheets not found/deleted", vbOKOnly)
    Resume Next
 
End Sub
John_Ru (rep: 6102) Jun 10, '21 at 8:56 am
John it's  ok  all  what  you  did     exactly  what    I want  may  be  my  expression    is  not  good    what  i meant      I  don't  know  how  add  the  line  of  message box  linked  with  condition    that's  why     I  asked   that to  show  how  does    that    and  you  did  it  thanks  again   
speed (rep: 40) Jun 10, '21 at 10:08 am
Okay, good.
John_Ru (rep: 6102) Jun 10, '21 at 10:31 am
Add to Discussion
0

Instead of the message box I suggest you add one line of code as shown below.

Sub delsh()

    Dim Ws As Variant

    Application.DisplayAlerts = False
    Ws = Array("ss", "dd")
    On Error Resume Next
    Worksheets(Ws).Delete
    Application.DisplayAlerts = True
End Sub

The effect is that the error you try to avoid will not be shown when it occurs. Therefore it doesn't matter how many timesd the user runs the code. The tangible result is the sheets are deleted.

Discuss

Discussion

thanks  for  your  suggestion 
speed (rep: 40) Jun 10, '21 at 8:34 am
Hi Speed, Thank you for your feedback. But please remember to mark my answer as "Selected" after you tested my recommendation and found it not only working as promised but also solving your problem. That's what other readers of this thread are really interested in - your experience. Please share it.
Variatus (rep: 4889) Jun 10, '21 at 10:41 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login