Editing text on a spreadsheet within a UserForm-CodePudding

I have the following code which allows me to select text from within a spreadsheet based upon the values of two combobox's.

The question is; how do make edits on the spreadsheet when they are made on the userform?

Private Sub CommandButton1_Click()
    Dim s As Worksheet
    Set s = ActiveSheet ' use for active sheet
    'Set s = Worksheets("Sheet1") ' use for a specific sheet
    For row = 1 To s.Cells(s.Rows.Count, 1).End(xlUp).row
      If s.Cells(row, "A").Value = ComboBox1.Value And _
          s.Cells(row, "B").Value = ComboBox2.Value Then
          TextBox1.Value = s.Cells(row, "C").Value
          TextBox2.Value = s.Cells(row, "D").Value
          TextBox3.Value = s.Cells(row, "E").Value
          TextBox4.Value = s.Cells(row, "F").Value
      End If
    Next
End Sub

CodePudding user response：

Assuming "row" is a module-level variable (declared at the top of the module) this is what you need, assuming you have a button named CommandButton2 that you want to click to save the data:

Private Sub CommandButton2_Click()
  s.Cells(row, "C").Value = TextBox1.Value
  s.Cells(row, "D").Value = TextBox2.Value
  s.Cells(row, "E").Value = TextBox3.Value
  s.Cells(row, "F").Value = TextBox4.Value
End Sub

CodePudding user response：

Binding a Userform Control on a Range

You can use the ControlSource property of a control. This binds it on a spreadsheet range. All changes on the userform control will be directly go into the bindet range.That will also work on opposite direction.

How to Use

Create an empty Userform. Put two textbox controls in it (TextBox1 and TextBox2). Set the ControlSource property on both to the Target Range.

Keep in mind that you assign a string to ControlSource. Therefore you can build its value during runtime.

Private Sub UserForm_Initialize()
    ' Set the Target Range to bind the control to
    TextBox1.ControlSource = "Table1!" & "A1"
    TextBox2.ControlSource = "Table2!A1"
End Sub

Questions use case

Here is the code fitting into your questions example. Put this into an empty userform

Option Explicit


'
'   Create an empty userbox with the following Controls
'
'   ComboBox1 as ComboBox
'   ComboBox2 as ComboBox
'
'   TextBox 1 as TextBox
'   TextBox 2 as TextBox
'   TextBox 3 as TextBox
'   TextBox 4 as TextBox



Private Sub ComboBox1_Change()
    Dim lngTargetRow As Long
    lngTargetRow = FindTargetRow()
    Call SetTextBoxControlSources(lngTargetRow)
End Sub
Private Sub ComboBox2_Change()
    Dim lngTargetRow As Long
    lngTargetRow = FindTargetRow()
    Call SetTextBoxControlSources(lngTargetRow)
End Sub

' Finds the Target Row regarding the values of both comboboxes
Private Function FindTargetRow()
    Dim s As Worksheet
    Set s = ActiveSheet
    Dim Row As Long
    Dim RetVal As Long: RetVal = -1
    For Row = 1 To s.Cells(s.Rows.Count, 1).End(xlUp).Row
      If s.Cells(Row, "A").Value = ComboBox1.Value And _
          s.Cells(Row, "B").Value = ComboBox2.Value Then
          RetVal = Row
          Exit For
        End If
    Next
    FindTargetRow = RetVal
End Function


Private Function SetTextBoxControlSources(lngTargetRow As Long)
    If (lngTargetRow <= 0) Then
        ' No Match, No Binding
        TextBox1.ControlSource = ""
        TextBox2.ControlSource = ""
        TextBox3.ControlSource = ""
        TextBox4.ControlSource = ""
        TextBox1.Text = "No Match"
        TextBox2.Text = "No Match"
        TextBox3.Text = "No Match"
        TextBox4.Text = "No Match"
    Else
        ' Set the Target Range to bind the control to
        TextBox1.ControlSource = "Table1!C" & lngTargetRow
        TextBox2.ControlSource = "Table1!D" & lngTargetRow
        TextBox3.ControlSource = "Table1!E" & lngTargetRow
        TextBox4.ControlSource = "Table1!F" & lngTargetRow
    End If
End Function

Assign the Textbox Value to a Ranges Value

If you want to go only into one direction, you can assign the userform textbox.text property to a targets range.value.

Activesheet.Range("a1").value = TextBox1.text

This is only going from the userform into the spreadsheet.Changes made on the Spreadsheet will not update back into the userforms textbox.

Using the ControlSource Method descripted at the beginning of the answer keeps you away from coding own update methods, so it helps you to keep the code more readable.