Search code examples
excelvbauserform

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

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. Worksheet Monthly FGL Report

Solution

  • 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...