Advice on using VBA to delete cells on a worksheet

[Graydjames]

Hi to all

I am a novice at VBA and what I have done in the past has been by trial and error Usually I succeed but I am totally stumped by the following. Can anyone help?

I have a workbook that contains a sheet called “001 Help Sheet”. This sheets contains a list of employees in three columns F, G and H employee number (F), name (G) and department (H) in each column respectively. I have a user form to enter and add new employees to the list. This works fine.

I want a user form to delete an employee. I have set the user form up. The user simply enters the number of the employee to delete in a text box called tbEmpNumberDelete. The command buttons on the form are “OK” named cbOK and cancel, named cbCancel. The code for cbOK is as follows:

Private Sub cbOK_Click()
'declare the variables
Dim c As Range, emplist As Range
Dim lastrow As Long
Dim Answer As String
Answer = MsgBox("Are you sure you wish to delete employee " & tbEmpNumberDelete.Value & "?", vbYesNo, "Confirm")
If Answer = vbNo Then
Unload Me
Exit Sub
Else
Application.ScreenUpdating = False
lastrow = Worksheets("001 Help Sheet").Range("F" & Rows.Count).End(xlUp).Row
Set emplist = Worksheets("001 Help Sheet").Range("F6:F" & lastrow)
'find the value in the range
For Each c In emplist
If c.Value = tbEmpNumberDelete.Value Then
'delete
c.Offset(0, 3).delete Shift:=xlUp
End If
Next c
End If
'turn on screen updating
Application.ScreenUpdating = True
Unload Me
End Sub

The list of employees starts in F6.

When I run this everything is fine until the user confirms that he does wish to delete the employee and then nothing whatsoever happens. Nothing is deleted.

I am sure this is something stupid on my part and obvious – but not to me. Can anyone tell me what I have done wrong?

 

[Graydjames]

Ah yes, a very good point and I have used a combo box elsewhere in the workbook so I actually know how to do that!! Thank you for the suggestion Dave.

 

[Graydjames]

Just realised I got muddled above between two different Daves. VBA is tricky enough for me without two Daves. Seriously though, thanks to everyone.

 

[SQUIDD]

no problem, i guessed you had.

SQUIDD

 

[dmt32]

Hi,
Glad you found solution that worked for you.
Just to point out that you declared your Msgbox variable as a String data type.
In my suggestion you will note that I amended this to MsgBox enumeration VbMsgBoxResult which returns an integer.
By declaring your variable using MsgBox VbMsgBoxResult enumeration you will get the intellinse (this is the drop down list that appears next to variables in the VBA editor) display available.


Hope Helpful

Dave

 

[Peter_SSs]

You cannot Delete single cells, just an entire row or column.

Dave, I note that you have repeated this comment in this thread. To put the record straight, it isn't true - you can delete a single cell or group of cells.
eg Put data in all cells in A1:D5
Select say B2:C3 (ie 4 cells)
Right-click -> Delete... -> Shift cells up -> OK

 

[dmt32]

Dave, I note that you have repeated this comment in this thread. To put the record straight, it isn't true - you can delete a single cell or group of cells.
eg Put data in all cells in A1:D5
Select say B2:C3 (ie 4 cells)
Right-click -> Delete... -> Shift cells up -> OK

despite my advancing years - you learn something new every day!

Thanks Peter


Dave

 

[Peter_SSs]

despite my advancing years ..

I know what that is like - my avatar is a few years old now.

 

[Graydjames]

Dave, I note that you have repeated this comment in this thread. To put the record straight, it isn't true - you can delete a single cell or group of cells.
eg Put data in all cells in A1:D5
Select say B2:C3 (ie 4 cells)
Right-click -> Delete... -> Shift cells up -> OK

Thank you. I was confused by the conflicting comments (and also by the two Daves). I knew your code did the trick (once I sorted the problem with the number format) as Squidd confirmed, but I am grateful to you for verifying this beyond doubt.