I want to overwrite VBA edit data

Enter the number of the employee you want to search in the Browse button to view the data in the form.
I would like to edit the data from there and press the edit confirmation button to overwrite the table.
However, with the current code, it looks like 14 lines.

VBA

This is what you do when you press the reference button

 'When you press the edit confirm button'
Private Sub OkButton1_Click()

    Dim x As Integer
    With Works ("Members")
    Forx=2 To 10
    Cells(ActiveCell.Row, x).Value=Me.Controls("TextBox" & x).Text
    Me.Controls("TextBox" & x).Text="
    Next
    End With

End Sub

 'What to do when you press the reference button'
Private Sub RefBtn_Click()

    US>'If no value was entered to reference'
    If Me.IDTextBox="Then
        MsgBox "Please enter a reference number."
        Exit Sub
    End If

    'View Data'
    Dimrng As Range
    Dimr As Variant
    Dimi As Integer
    With Works ("Members")

    Setrng=.Range ("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
    r = Application.Match (Val(IDTextBox.Text), rng, 0)

    Fori=2 To 5
        Controls("TextBox"&i).Text=.Cells(r+1,i)
    Next i
    If.Cells(r+1,6) = "Man" Then
        Man.Value = True
    Else
        Woman.Value=True
    End If

    ComboBox1.Text=.Cells(r+1,7)
    Fori=6 To 8
    Controls("TextBox" & i ).Text=.Cells(r+1, i+2)
    Next i
    End With

End Sub

This is just the registration code for additional members

 'Select the sheet you are currently working on'
    ActiveSheet.Select
    If Cells (8,1).Value=1 Then'cell value may change'

        'Get the last cell number entered'
        CellCount=Cells(7,1).End(xlDown).Row+1

        'Enter IDTextBox value in "Employee Number" field'
        Cells(CellCount, 1).Value=Me.IDTextBox.Value

        'Enter Last Name'
        Cells(CellCount, 2).Value=Me.TextBox2.Value

        'Enter First Name'
        Cells(CellCount, 3).Value=Me.TextBox3.Value

        'Enter Say'
        Cells(CellCount, 4).Value=Me.TextBox4.Value

        'Enter May'
        Cells (CellCount, 5).Value=Me.TextBox5.Value

        'Enter the selected gender'
        If Man.Value = True Then
            Cells (CellCount, 6).Value=Me.Man.Caption

        ElseIf Woman.Value=True Then
            Cells (CellCount, 6).Value=Me.Woman.Caption

        End If

        'Enter means of transportation'
        Cells (CellCount, 7).Value=Me.ComboBox1.Value

        'Enter the nearest station'
        Cells (CellCount, 8).Value=Me.TextBox6.Value

        'Enter transportation expenses'
        Cells (CellCount, 9).Value=Me.TextBox7.Value

        'Enter hourly wage'
        Cells (CellCount, 10).Value=Me.TextBox 8.Value

vba

2022-09-30 16:11

1 Answers

The program 'What to do when you press the reference button' is as follows, and the line of the cell to be written to is ActiveCell.Row.
　In the screenshot of the question, the G14 cell appears to have been selected (ActiveCell), so I think that's why the data was written on line 14 (B14:H14) (with the A15 cell selected, if you click the edit confirm button, it will be written on line 15.

Private SubOkButton1_Click()
    Dim x As Integer
    With Works ("Members")
        Forx=2 To 10
            Cells(ActiveCell.Row, x).Value=Me.Controls("TextBox" & x).Text
            Me.Controls("TextBox" & x).Text="
        Next
    End With
End Sub

If you want the specification to be overwritten by pressing the edit confirmation button,
Remember which line you got your data from and try to write it back to that line.
(It is not good to write ActiveCell.Row back in line.I don't know which cell to run the program with.)

2022-09-30 16:11

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656