MarkNumskull
08-21-2009, 08:46 AM
Hi,
I have written the following code to take a name from a user then delete a worksheet with the relevant name.
After it deletes the sheet i want it to search down a sheet called "data" and then remove the corresponding name for the list.
My loop proecdure works fine on its own but when combined as below it throws up errors namely "400"
Sub Removestaffmember()
Dim Staff As String
Dim Answer As String
Staff = InputBox("Please Enter The Name Of The Staff Member You Want To Remove", " Remove Staff Member")
Answer = MsgBox("Deleting will permanently remove the record, do you want to continue?", vbOKCancel)
Application.DisplayAlerts = False
If Answer <> vbOK Then Exit Sub
ThisWorkbook.Sheets("" & Staff).delete
ThisWorkbook.Sheets("Data").Activate
Range("A1").Select
Do While ActiveCell <> ("" & Staff)
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.delete
Application.DisplayAlerts = True
End Sub
Any help would be much appreciated as to what im doing wrong
Mark
I have written the following code to take a name from a user then delete a worksheet with the relevant name.
After it deletes the sheet i want it to search down a sheet called "data" and then remove the corresponding name for the list.
My loop proecdure works fine on its own but when combined as below it throws up errors namely "400"
Sub Removestaffmember()
Dim Staff As String
Dim Answer As String
Staff = InputBox("Please Enter The Name Of The Staff Member You Want To Remove", " Remove Staff Member")
Answer = MsgBox("Deleting will permanently remove the record, do you want to continue?", vbOKCancel)
Application.DisplayAlerts = False
If Answer <> vbOK Then Exit Sub
ThisWorkbook.Sheets("" & Staff).delete
ThisWorkbook.Sheets("Data").Activate
Range("A1").Select
Do While ActiveCell <> ("" & Staff)
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.delete
Application.DisplayAlerts = True
End Sub
Any help would be much appreciated as to what im doing wrong
Mark