Save Edited Data to two worksheets using an employee number to select the rows

[BobH]

Hi I have a Workbook with 2 worksheets "Current Staff List" & "Historical Staff List" I have a user form to edit employee information and this extracts the information based on the employee number from the Current Staff List. When I save any changes the current staff list is updated correctly, but where i am struggling is to write the code to find the employee number on the Historical staff sheet and update any changes on to that sheet. Any help would be appreciated Regards Bob

Private Sub CommandButton1_Click()



Dim i As Long, LastRow As Long

Set sh = ThisWorkbook.Sheets("Current Staff List")

Set sh2 = ThisWorkbook.Sheets("Historical Staff List")

Dim n As Long

Dim m As Long



LastRow = Sheets("Current Staff List").Range("b" & Rows.Count).End(xlUp).Row

For i = 4 To LastRow



If Sheets("Current Staff List").Cells(i, 2).Value = (Me.ComboBox6) Or _

Sheets("Current Staff List").Cells(i, 2).Value = Val(Me.ComboBox6) Then

sh.Unprotect "office"

'Sheets("Current Staff List").Cells(i, 2) = entbox.Text

Sheets("Current Staff List").Cells(i, 3) = Combobox1.Text

Sheets("Current Staff List").Cells(i, 1) = ComboBox4.Text

Sheets("Current Staff List").Cells(i, 4) = ComboBox5.Text

Sheets("Current Staff List").Cells(i, 5) = fntbox.Text

Sheets("Current Staff List").Cells(i, 6) = sntbox.Text

Sheets("Current Staff List").Cells(i, 8) = dobtbox.Text

Sheets("Current Staff List").Cells(i, 16) = add1tbox.Text

Sheets("Current Staff List").Cells(i, 17) = add2tbox.Text

Sheets("Current Staff List").Cells(i, 18) = add3tbox.Text

Sheets("Current Staff List").Cells(i, 19) = pctbox.Text

Sheets("Current Staff List").Cells(i, 20) = con1tbox.Text

Sheets("Current Staff List").Cells(i, 21) = con2tbox.Text

Sheets("Current Staff List").Cells(i, 22) = mailtbox.Text

Sheets("Current Staff List").Cells(i, 9) = nitbox.Text

Sheets("Current Staff List").Cells(i, 23) = sdtbox.Text

Sheets("Current Staff List").Cells(i, 24) = hrstbox.Text

Sheets("Current Staff List").Cells(i, 26) = paytbox.Text

Sheets("Current Staff List").Cells(i, 25) = holtbox.Text

Sheets("Current Staff List").Cells(i, 30) = bontbox.Text

Sheets("Current Staff List").Cells(i, 10) = distbox.Text

Sheets("Current Staff List").Cells(i, 11) = pintbox.Text

Sheets("Current Staff List").Cells(i, 12) = pinxtbox.Text

Sheets("Current Staff List").Cells(i, 13) = pastbox.Text

Sheets("Current Staff List").Cells(i, 14) = passxtbox.Text





MsgBox "The information has been amended", , "Health Care"

With ActiveSheet

.Protect Password:="office", AllowFiltering:=True

.EnableSelection = xlockedCells

End With



End If



Next

End Sub

 

[dmt32]

Hi,
not tested but see if this change to your code helps

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim FoundRecord As Range
    Dim Search As String
    Dim i As Integer, a As Integer
    Dim msg As String
    
    Search = Me.ComboBox6.Value
    If Len(Search) = 0 Then Exit Sub
    
        For a = 1 To 2
            Set ws = ThisWorkbook.Worksheets(Choose(a, "Current Staff List", "Historical Staff List"))
            ws.Unprotect Password:="office"
            
            Set FoundRecord = ws.Columns(2).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
            
            If Not FoundRecord Is Nothing Then
            For i = 1 To 30
                i = IIf(i = 2, 3, IIf(i = 7, 8, IIf(i = 26, 30, i)))
                ws.Cells(FoundRecord.Row, i).Value = Choose(i, ComboBox4.Text, Combobox1.Text, ComboBox5.Text, fntbox.Text, sntbox.Text, _
                                                                dobtbox.Text, nitbox.Text, distbox.Text, pintbox.Text, pinxtbox.Text, _
                                                                pastbox.Text, passxtbox.Text, add1tbox.Text, add2tbox.Text, add3tbox.Text, _
                                                                pctbox.Text, con1tbox.Text, con2tbox.Text, mailtbox.Text, sdtbox.Text, _
                                                                hrstbox.Text, holtbox.Text, paytbox.Text, bontbox.Text)
             msg = msg & ws.Name & Chr(10)
            End If
            
            With ws
            .Protect Password:="office", AllowFiltering:=True
            .EnableSelection = xlockedCells
            End With
            Set FoundRecord = Nothing
            Set ws = Nothing
        Next a
    
    
        MsgBox "The following sheets have been amended" & Chr(10) & msg, 64, "Health Care"

End Sub

If this does not resolve, it would be helpful if you could place copy of your workbook with dummy data in a dropbox & provide a link to it here

Dave

 

[BobH]

Hi,
not tested but see if this change to your code helps

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim FoundRecord As Range
    Dim Search As String
    Dim i As Integer, a As Integer
    Dim msg As String
   
    Search = Me.ComboBox6.Value
    If Len(Search) = 0 Then Exit Sub
   
        For a = 1 To 2
            Set ws = ThisWorkbook.Worksheets(Choose(a, "Current Staff List", "Historical Staff List"))
            ws.Unprotect Password:="office"
           
            Set FoundRecord = ws.Columns(2).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
           
            If Not FoundRecord Is Nothing Then
            For i = 1 To 30
                i = IIf(i = 2, 3, IIf(i = 7, 8, IIf(i = 26, 30, i)))
                ws.Cells(FoundRecord.Row, i).Value = Choose(i, ComboBox4.Text, Combobox1.Text, ComboBox5.Text, fntbox.Text, sntbox.Text, _
                                                                dobtbox.Text, nitbox.Text, distbox.Text, pintbox.Text, pinxtbox.Text, _
                                                                pastbox.Text, passxtbox.Text, add1tbox.Text, add2tbox.Text, add3tbox.Text, _
                                                                pctbox.Text, con1tbox.Text, con2tbox.Text, mailtbox.Text, sdtbox.Text, _
                                                                hrstbox.Text, holtbox.Text, paytbox.Text, bontbox.Text)
             msg = msg & ws.Name & Chr(10)
            End If
           
            With ws
            .Protect Password:="office", AllowFiltering:=True
            .EnableSelection = xlockedCells
            End With
            Set FoundRecord = Nothing
            Set ws = Nothing
        Next a
   
   
        MsgBox "The following sheets have been amended" & Chr(10) & msg, 64, "Health Care"

End Sub

If this does not resolve, it would be helpful if you could place copy of your workbook with dummy data in a dropbox & provide a link to it here

Dave

Hi Dave thanks for this I have attached a link
Employees List.xlsm

I tried to use your amended code and got into a bit of a mess, sorry. I started using excel back in January a bit of a late starter at 62 but thoroughly enjoying keeping the brain active albeit a bit frustrating at times, so a lot of what I have done is from researching on line. Please have a look, its a down scaled version of what I am trying to achieve. The Current list contains current employee details the Historical List contains Historical employees and Current Employees in start date order.

Thanks again Rob

 

[dmt32]

Please have a look, its a down scaled version of what I am trying to achieve. The Current list contains current employee details the Historical List contains Historical employees and Current Employees in start date order.

At 62 you are youngster! I have had a quick glance at your project & note you have two userforms doing more or less, same thing - you should only need one userform to to perform all the actions. Also, some fields on the form are not shown on the the worksheet & seem to have no place for storing their data?

But before you continue with this project I take it you are fully aware of the security weakness of excel? You plan to store a lot of personal data which would be subject to GDPR regulations & just as friendly advice, suggest before you progress too far, you have a chat with your data protection officer to get their view on it.

I am on granddad duty today but will time permitting, see if can get my posted suggestion working for you.

Dave

 

[BobH]

At 62 you are youngster! I have had a quick glance at your project & note you have two userforms doing more or less, same thing - you should only need one userform to to perform all the actions. Also, some fields on the form are not shown on the the worksheet & seem to have no place for storing their data?

But before you continue with this project I take it you are fully aware of the security weakness of excel? You plan to store a lot of personal data which would be subject to GDPR regulations & just as friendly advice, suggest before you progress too far, you have a chat with your data protection officer to get their view on it.

I am on granddad duty today but will time permitting, see if can get my posted suggestion working for you.

Dave

Thanks Dave I have deleted some of the fields so its a bit of a down scaled version of the original. Thanks for the advice regarding excel's security, GDPR regulations considered the finished worksheet will eventually be compiled and managed by "her in doors" and on her works computer my task during furlough is to make some of the systems a bit more user friendly if I can.
Regards Rob