Need code for popup msgbox when ws has been deleted

[tuongtu3]

Hello All,
I'm looking for the code for popup msg "The WS has been delete".

Something like:

If Sheets("Sheet3").Range("A1").Value > 360 Then
UserForm1.Show
''''How to popup msgbox if Sheet3 has been deleted???'''
Else
MsgBox "The Info is no longer exist"

Regards,
tt3

[jolivanes]

I don't follow the logic.
How can you have a value in Sheet3 if it has been deleted?
Unless you want to delete Sheet3 once the value in cell A1 reaches >360

Sub Like_This_You_Mean()
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    If [Sheet3!A1].Value > 360 Then
        Msg = "Do you want to delete Sheet3?"
        Style = vbYesNo + vbCritical + vbDefaultButton2
        Title = "Delete Sheet3?"
        Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then
            Sheets("Sheet3").Delete
        Else
            Exit Sub
        End If
    End If
End Sub

[tuongtu3]

Hi Jolivanes,
I'm not looking for code to delete Worksheets. The ws will be deleted after project is done (per company policy purposes). Later on if someone is looking for more info of this project then the popup msgbox says "The Info is no longer exist".

Regards,
tt3

[jolivanes]

So what is the criteria for "looking for info of this project"? Is this done from the same workbook?
How would they navigate to Sheet3, if they indeed do?
Or do you want a MessageBox when the Workbook is opened up stating that Sheet3 has been deleted previously?

[tuongtu3]

Hi Jolivanes,
Just a little more details for your info why I need that code. The user finished a project and submitted it to the customer (somehow the user(s) made a mistake and lost a huge amount of money). They found out then went back and modified the project then the user(s) blame the worksheet errors. Now I'm forced to delete the ws after the user(s) finish and no way for them to modify if they make a mistake.
I believe the ws with all codes (got help frome here) are 100% correct .

Thank you very much for all your help.

Regards,
tt3

[jolivanes]

So you want a message that they can't make changes because the sheet which handles the relevant calculations has been deleted?
That would be in Workbook_Open.
If you don't trust the people, never give them any document that can be changed.

[tuongtu3]

Hi jolivanes,
Yes, it's would be nicer if it have a popup message instead of error message "Subscrip out of range" when the user(s) click into the button. Is it possible to have this code? I already have something else in WB open.

Regards,
tt3

[Trebor76]

Hi tt3,

See how this goes:

Option Explicit
Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au
   
    Dim varSheetLen As Variant
    
    On Error Resume Next
        varSheetLen = Len(Sheets("Sheet3").Name)
    On Error GoTo 0
    
    If IsEmpty(varSheetLen) = True Then
        MsgBox "The Info no longer exists"
        Exit Sub
    ElseIf Sheets("Sheet3").Range("A1").Value > 360 Then
        UserForm1.Show
    End If
    
End Sub

Regards,

Robert

[tuongtu3]

Hello Robert,
Yeah, that code works and can you explain what does "On Error Resume Next" mean/do in this code and when/where/how to use it? How many "ElseIf" we can use in one code?

Thank you very much for your time and help.

Regards,
tt3

[Trebor76]

Yeah, that code works and can you explain what does "On Error Resume Next" mean/do in this code and when/where/how to use it?

This tells an application to ignore any error messages. The "On Error Goto 0" statement resets the code to not ignore error messages. Bear in mind though it in no way fixes any error - it just suppresses the message so use it sparingly for situations you know may cause an error and that you can provide a custom solution for.

How many "ElseIf" we can use in one code?

I don't think there is any limit on the number ElseIf that can be used, but beyond three I prefer to use the "Select Case" method.

I'm glad we were able to provide you with a solution. If you could mark the thread as solved, it would be appreciated.

Regards,

Robert

[tuongtu3]

Hello Robert,
Thank you very much for your time and help.

Regards,
tt3