Delete Sheet based on cell value

[cparks]

I have a drop down list, through data verification, of sheets that I can choose from to delete.

I would like to assign a macro to a button to delete the selected sheet name from the drop down list.

Also, I want a message box to appear so that I can confirm the deletion of the sheet (i.e. "You have selected [Sheet Name] to be deleted. Press OK/Select Yes to confirm."

Any help will be greatly appreciated. Thanks

 

[Hui]

Please see attached file

 

[PCosta87]

Hi @cparks

Maybe something like:

Sub deletefromlist()

    If MsgBox("You have selected sheet " & Range("F3").Value & " to be deleted. Press OK to confirm.", vbOKCancel, "Delete sheet name!") = vbOK Then
        Columns("E").Find(What:=Range("F3").Value, LookIn:=xlValues).ClearContents
    End If

End Sub

Regards

 

[cparks]

Please see attached file

Hui,

Thank you so much for your help! I was looking more at what @PCosta87 submitted. But Again, I appreciate the effort.

 

[cparks]

Hi @cparks

Maybe something like:

Sub deletefromlist()

    If MsgBox("You have selected sheet " & Range("F3").Value & " to be deleted. Press OK to confirm.", vbOKCancel, "Delete sheet name!") = vbOK Then
        Columns("E").Find(What:=Range("F3").Value, LookIn:=xlValues).ClearContents
    End If

End Sub

Regards

@PCosta87,

Looks great to me! Now, I've noticed something that I didn't think about that maybe you or @Hui could help with.

As I requested and you both pulled through quite well, the code deletes what is chosen, but it's not what I expected. I need for it to delete a record (i.e. the entire employee's record).

So with that being said, by deleting the First/Last Name fields (parent fields), it will delete the entire record. I still want it to display the Sheet Name, but when confirmed it needs to:

1. delete that corresponding First/Last Name fields
2. delete the connected sheet
3. if not too much trouble, move the cells up to fill in the blanks.
4. and with every new sheet populated, insert in cell A1 a hyperlink "Back to Crew"

Hope it's not too much trouble.

Here is more of what I have in the file below. I have the macros that you suggested in the file.

 

[Hui]

Your comments were very specific:
"I would like to assign a macro to a button to delete the selected sheet name from the drop down list."

 

[PCosta87]

Your comments were very specific:
"I would like to assign a macro to a button to delete the selected sheet name from the drop down list."

^This^

In any case... replace the previous code with this:

Sub deletefromlist()

On Error GoTo errorhandler

    If MsgBox("You have selected sheet " & Range("F3").Value & " to be deleted. Press OK to confirm.", vbOKCancel, "Delete sheet name!") = vbOK Then
        Sheets(Range("F3").Value).Delete
        Columns("E").Find(What:=Range("F3").Value, LookIn:=xlValues).EntireRow.Delete shift:=xlUp
    End If

Exit Sub

errorhandler: MsgBox "No worksheets found under the name " & Range("F3").Value & ". No sheet was deleted!", vbExclamation

End Sub

and add the following to "ThisWorkbook" (you also need to give the name "Crew" to the range you wish to go to when following the Hyperlink in the new sheet):

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Sh.Cells(1, 1).FormulaLocal = "=HYPERLINK(""#Crew"",""Back to Crew"")"

End Sub

and next time, please post the full problem right away

Hope this helps,
Regards

 

[cparks]

Your comments were very specific:
"I would like to assign a macro to a button to delete the selected sheet name from the drop down list."

Yes I sure was...and if you want to talk specifics, you didn't even put a button in your example.

 

[cparks]

^This^

In any case... replace the previous code with this:

Sub deletefromlist()

On Error GoTo errorhandler

    If MsgBox("You have selected sheet " & Range("F3").Value & " to be deleted. Press OK to confirm.", vbOKCancel, "Delete sheet name!") = vbOK Then
        Sheets(Range("F3").Value).Delete
        Columns("E").Find(What:=Range("F3").Value, LookIn:=xlValues).EntireRow.Delete shift:=xlUp
    End If

Exit Sub

errorhandler: MsgBox "No worksheets found under the name " & Range("F3").Value & ". No sheet was deleted!", vbExclamation

End Sub

and add the following to "ThisWorkbook" (you also need to give the name "Crew" to the range you wish to go to when following the Hyperlink in the new sheet):

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Sh.Cells(1, 1).FormulaLocal = "=HYPERLINK(""#Crew"",""Back to Crew"")"

End Sub

and next time, please post the full problem right away

Hope this helps,
Regards

I really appreciate it. I didn't know it was a problem until I put the macro in play. I apologize.

 

[PCosta87]

I really appreciate it. I didn't know it was a problem until I put the macro in play. I apologize.

You are welcome

 

[cparks]

^This^

In any case... replace the previous code with this:

Sub deletefromlist()

On Error GoTo errorhandler

    If MsgBox("You have selected sheet " & Range("F3").Value & " to be deleted. Press OK to confirm.", vbOKCancel, "Delete sheet name!") = vbOK Then
        Sheets(Range("F3").Value).Delete
        Columns("E").Find(What:=Range("F3").Value, LookIn:=xlValues).EntireRow.Delete shift:=xlUp
    End If

Exit Sub

errorhandler: MsgBox "No worksheets found under the name " & Range("F3").Value & ". No sheet was deleted!", vbExclamation

End Sub

and add the following to "ThisWorkbook" (you also need to give the name "Crew" to the range you wish to go to when following the Hyperlink in the new sheet):

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Sh.Cells(1, 1).FormulaLocal = "=HYPERLINK(""#Crew"",""Back to Crew"")"

End Sub

and next time, please post the full problem right away

Hope this helps,
Regards

@PCosta87 . It's working FLAWLESS. Absolutely FLAWLESS! Bravo! Again, MUCH thanks!

 

[PCosta87]

I'm glad I could help