PDA

View Full Version : Solved: Why doesn't this work?



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

mdmackillop
08-21-2009, 08:58 AM
Try

Option Explicit
Sub Removestaffmember()
Dim Staff As String
Dim Answer As Long
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").Columns(1).Find(Staff).Delete
Application.DisplayAlerts = True
End Sub

Bob Phillips
08-21-2009, 09:00 AM
Try

ActiveCell.ClearContents

instead of

ActiveCell.delete

MarkNumskull
08-21-2009, 09:05 AM
Thanks a million MD that was perfect, much appreciated!