none
Update record RRS feed

  • Question

  • Afternoon

    I have a Userform which shows data filtered data from multiple worksheets. I have a Command Button "Update" which should update a set of timings input by the user, but for some reason I cannot seem to get my code to work.

    Userform "Input_Data" displays data taken from Worksheet "Times" when the user selects from a list of units from CmbUnit the userfom displays the following -

    Textbox1 = Unit - taken from column A (repeated as it is used elsewhere in the Userform)

    Textbox2 = Time1 -Taken from Column B

    Textbox3 = Time2 - Taken from column C etc.

    if any of these times are updated/changed on the Userform by the user and "Update" is pressed I would like the correct row updated.

    Can anyone help?

    Steve

    Monday, January 14, 2019 2:58 PM

Answers

  • Try this (where cmdUpdate is the name of the command button):

    Private Sub cmdUpdate_Click()
        Dim cell As Range
        Set cell = Worksheets("Times").Range("A2:A14").Find(What:=Me.TextBox1, _
            LookIn:=xlValues, LookAt:=xlWhole)
        If cell Is Nothing Then
            MsgBox "Unit not found on Times worksheet!", vbCritical
        Else
            cell.Offset(0, 1).Value = Me.TextBox2
            cell.Offset(0, 2).Value = Me.TextBox3
            ' etc.
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Stirlingmw Monday, January 14, 2019 9:12 PM
    Monday, January 14, 2019 8:27 PM

All replies

  • How can we tell which row should be updated?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 14, 2019 3:43 PM
  • Hans

    worksheet "Times" Column A contains "Unit" (Unit is the Header in A1) there are 13 Unit names from A2 to A14. The data displayed in the Userform is relevant to the selected "Unit". This is displayed in Textbox1 of the Userform.

    I hope that makes sense

    Steve


    • Edited by Stirlingmw Monday, January 14, 2019 4:48 PM worksheet name added
    Monday, January 14, 2019 4:47 PM
  • Try this (where cmdUpdate is the name of the command button):

    Private Sub cmdUpdate_Click()
        Dim cell As Range
        Set cell = Worksheets("Times").Range("A2:A14").Find(What:=Me.TextBox1, _
            LookIn:=xlValues, LookAt:=xlWhole)
        If cell Is Nothing Then
            MsgBox "Unit not found on Times worksheet!", vbCritical
        Else
            cell.Offset(0, 1).Value = Me.TextBox2
            cell.Offset(0, 2).Value = Me.TextBox3
            ' etc.
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Stirlingmw Monday, January 14, 2019 9:12 PM
    Monday, January 14, 2019 8:27 PM
  • Hans

    As always a perfect solution

    Thank You

    Steve

    Monday, January 14, 2019 9:11 PM