lundi 27 mai 2019

Userform combobox values linked cells, depend on all combobox values

I have a userform with 2 comboboxes and a textbox. The code below are for the cell linkages, borders and filldown. I would like to add in the scenarios written below depending on how the user fills out the userform.

Scenario 1: If Combobox 2 displays the same text/value currently in the worksheet MRFL Range(A1:A250) and combobox 1 displays the same text/value as in worksheet MFRL Range(B1:B250) then only change the value of column AE in the same row as the combobox 2 value in Worksheet MFGLR.

Scenario 2: If Combobox 2 displays the same text/value currently in the worksheet MRFL Range(A1:A250) and combobox 1 displays a different text then in worksheet MFRL Range(B1:B250) then add the values of combobox1 and combobox2 at the next available row in worksheet MFRL, Combobox1 in column B and Combobox2 in column A. And then only change the value of column AE in the same row as the combobox 2 value in Worksheet MFGLR.

Scenario 3: If Combobox 2 displays a different text/value currently in the worksheet MRFL Range(A1:A250) and combobox 1 displays a different text then in worksheet MFRL Range(B1:B250) then add the values of combobox1 and combobox2 at the next available row in worksheet MFRL, add combobox2 value at the next available row in Column A in the worksheet CT and add the combobox 2 value in the next available row in Column A of worksheet MFGLR Combobox1 in column B and Combobox2 in column A. And then only change the value of column AE in the same row as the combobox 2 value in Worksheet MFGLR.

What I want to happen for only Scenario 3 will always happen with the code below. I would like to keep this in the code as well, while adding in the above scenarios but keeping the filldown and bordering the same as they are identical for all the scenarios.

Private Sub CommandButton1_Click()
Dim sht As Worksheet, pflds As PivotFields, showItems As Boolean
Dim arr

With Worksheets("MFRL")
.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)


End If
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(, 21).Resize(2)
    .Borders.LineStyle = xlContinuous

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

If ComboBox2.Value <> "" Or TextBox1.Value <> "" Then  
    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) = ComboBox2.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.
    End With
End With
End If
 ActiveWorkbook.RefreshAll
 Unload Me

Aucun commentaire:

Enregistrer un commentaire