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
This question generated 18 answers. To proceed to the answers, click here.
This thread is current as of March 19, 2017.
For more resources for Microsoft Excel:
This article includes the following tags:
- Microsoft Excel