Have the userform textbox link to the same row as the combobox value but a different column

Question

I have a userform with comboboxes and a textbox. I would like the textbox to link to the cell 2 columns over from the value of combobox1. How would I go about doing so?

Also if the combobox/textbox is blank I would like the linked cells value to remain as is.

The code below for populating the userform comboboxes.

 With Worksheets("ML")
.Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = ComboBox1.Value
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = ComboBox2.Value
.Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).AutoFill 
.Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2)

With .Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2)
    .Borders.LineStyle = xlContinuous
End With
With Worksheets("CT")
  .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = ComboBox2.Value
  .Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 21).AutoFill 
  .Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 21).Resize(2)
With .Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2)
    .Borders.LineStyle = xlContinuous
End With


ActiveWorkbook.RefreshAll
Unload Me
End Sub

I would like the value of the Combobox1 to display at the next available cell in column A then I would like the textbox1 to show up in the same row as the combobox value but in column AE. In the same row as both the textbox value and the combobox value I would like the columns up to AM to be filled down. Finally I would like the columns up to AM have borders.

You could reuse the same code .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = ComboBox1.Value, just add 2 to the column offset, i.e.: .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 2) = TextBox.Value. As for keeping values if blank, you should wrap each of your value allocations within an if statement, something like if combobox1.value <> "" then... — FAB
Please clarify: Do you want the value from the "cell 2 columns over from the value of combobox1" to be placed in the TextBox or vice versa — GMalc
Cell 2 columns over from the linked cell of the combobox @GMalc — Tmacjoshua
@DarXyde This is what I tried so far but I can nothing is displaying if I input into the textbox. I also don't think anything is happening when it is left blank. With Worksheets("ML") If TextBox1.Value <> "" Then .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 2) = TextBox1.Value .Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 43).AutoFill .Cells(Rows.Count, "A").End(xlUp).Offset(-1, 1).Resize(, 43).Resize(2) Else End If End With ActiveWorkbook.RefreshAll Unload Me — Tmacjoshua
Please see the response as answer... indentation is horrible (well, lack of) in comments. — FAB

FAB FAB · Accepted Answer · 2019-05-23T20:50:06

Assuming here that filldown is what you want instead of autofill (get the formulas from last row). See if this works for you.

Dim shtML As Worksheet: Set shtML = ActiveWorkbook.Worksheets("ML") 'Set this to the correct workbook
Dim rngDest As Range
Dim lRow As Long

If ComboBox1.Value <> "" And TextBox1.Value <> "" Then  'Use <[ Or ]> instead of <[ And ]> as you see fit
    With shtML
        lRow = .Cells(.Rows.Count, 1).End(xlUp).row + 1 'Get the first free row
        Set rngDest = .Range(.Cells(lRow, 1), .Cells(lRow, 39))

        With rngDest
            .FillDown 'In the same row as both the textbox value and the combobox value I would like the columns up to AM to be filled down
            .Cells(1, 1) = ComboBox1.Value   'the value of the Combobox1 to display at the next available cell in column A
            .Cells(1, 31) = TextBox1.Value   'the textbox1 to show up in the same row as the combobox value but in column AE
            .Resize(1, .Columns.Count + 5).Borders.LineStyle = xlContinuous  'Finally I would like the columns up to AM have borders (+5 past the fill down range).
        End With
    End With
End If

EDIT: made changes as per last discussion...

Have the userform textbox link to the same row as the combobox value but a different column

1 Answers