Vba code help, userform combobox input selection to specific cell.

[Strycure12]

Hello,

I have a excel workbook that I have created that needs a specific code for userform combobox.

The question/ problem, the combobox has a selection which is automatically populated in the initialization. In combobox the selection is:
1. clamp install
2. damaged clamp

With that selection it would need to go to a specific column "AT" labeled "Damaged clamp" or "AV" "clamp installed incorrectly" and input a value that is in a text box next to it, depending on selection.

I would like to preserve the next row log entry code.

Tried the code below and received error, any help is greatly appreciated.

The code for the userform

Private Sub CommandButton1_Click()
Dim LastRow As Long
With Worksheets("sheet1")
.ListObjects("Table1").ListRows.Add
LastRow = .Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

.Cells(LastRow, 1).Value = Me.TextBox1.Value
.Cells(LastRow, 2).Value = Me.TextBox2.Value
.Cells(LastRow, 3).Value = Me.TextBox3.Value
.Cells(LastRow, 4).Value = Me.TextBox4.Value

If combox10.value select
= Combobox10 "Damaged clamp". Selection
Cells(LastRow, 48).Value = Me.textbox10.Value
Else
=
damage
Cells(LastRow, 42).Value = Me.textbox10.Value
End then
End sub

 

[dmt32]

Hi,
see if this update to your code does what you want

Private Sub CommandButton1_Click()
    Dim Tbl         As ListObject
    Dim NewRow      As ListRow
    Dim WhichColumn As Long, BoxIndex As Long
    
    'get combobox selection listindex
    BoxIndex = Me.ComboBox10.ListIndex + 1
    If BoxIndex < 1 Then BoxIndex = 1
    
    'set object variable
    Set Tbl = Worksheets("sheet1").ListObjects("Table1")
    
    'Add New row to the table
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)
    
    'get column in table to post value of textbox10 to.
    WhichColumn = Tbl.ListColumns(Choose(BoxIndex, "Damaged clamp", _
                  "clamp installed incorrectly")).Range.Column
    
    'post new record to table
    With NewRow
        .Range(1).Value = Me.TextBox1.Value
        .Range(2).Value = Me.TextBox2.Value
        .Range(3).Value = Me.TextBox3.Value
        .Range(4).Value = Me.TextBox4.Value
        .Range(WhichColumn).Value = Me.TextBox10.Value
    End With
    
End Sub

Dave

 

[Strycure12]

Hello Dave,

Thanks for the assistance, it worked.

I want to modify for other comboboxes with same situation.

Would i repeat the same for different combobox es for example:

BoxIndex = Me.ComboBox10.ListIndex + 1
If BoxIndex < 1 Then BoxIndex = 1
With the column statement

BoxIndex = Me.ComboBox14.ListIndex + 1
If BoxIndex < 1 Then BoxIndex = 1
With the column statement



Would that be possible or would that have to be written in different way? There 14 combo boxes that have same condition but different lists and columns.

Thanks again

 

[dmt32]

Would that be possible or would that have to be written in different way? There 14 combo boxes that have same condition but different lists and columns.

Hi,
please solution resolved your issue

with regards to adapting it to include other comboboxes, cannot give an immediate answer without seeing all of the code

If can post all the forms code then will take a look

Dave

 

[Strycure12]

Hello Dave ,

Sorry for the late response, I'll post the code now.

Private Sub ComboBox16_Change()

Label19.Caption = ComboBox16.Value

End Sub







Private Sub CommandButton1_Click()

Dim Tbl As ListObject

Dim NewRow As ListRow

Dim WhichColumn As Long, BoxIndex As Long



'get combobox selection listindex

BoxIndex = Me.ComboBox2.ListIndex + 1

If BoxIndex < 1 Then BoxIndex = 1





'set object variable

Set Tb1 = Worksheets("Sheet1").ListObjects("Table1")



'Add New row to the table

Set NewRow = Tb1.ListRows.Add(AlwaysInsert:=True)



'get column in table to post value of textbox10 to.

WhichColumn = Tb1.ListColumns(Choose(BoxIndex, "Damage Clamp", "Clamp Incorrectly Installed", "Gapped Clamp")).Range.Column



'post new record to table

With NewRow

.Range(1).Value = Me.TextBox14.Value

.Range(2).Value = Me.ComboBox29.Value

.Range(3).Value = Me.ComboBox15.Value

.Range(4).Value = Me.ComboBox16.Value

.Range(WhichColumn).Value = Me.TextBox2.Value

End With

Unload Me

End Sub





Private Sub CommandButton2_Click()

Unload Me

End

End Sub





Private Sub SpinButton10_Change()

TextBox10.Text = SpinButton10.Value

End Sub



Private Sub SpinButton11_Change()

TextBox11.Text = SpinButton11.Value

End Sub



Private Sub SpinButton12_Change()

TextBox12.Text = SpinButton12.Value

End Sub



Private Sub SpinButton13_Change()

TextBox13.Text = SpinButton13.Value

End Sub



Private Sub SpinButton2_Change()

TextBox2.Text = SpinButton2.Value

End Sub



Private Sub SpinButton3_Change()

TextBox3.Text = SpinButton3.Value

End Sub



Private Sub SpinButton4_Change()

TextBox4.Text = SpinButton4.Value

End Sub



Private Sub SpinButton5_Change()

TextBox5.Text = SpinButton5.Value

End Sub



Private Sub SpinButton6_Change()

TextBox6.Text = SpinButton6.Value

End Sub



Private Sub SpinButton7_Change()

TextBox7.Text = SpinButton7.Value

End Sub



Private Sub SpinButton8_Change()

TextBox8.Text = SpinButton8.Value

End Sub



Private Sub SpinButton9_Change()

TextBox9.Text = SpinButton9.Value

End Sub



Private Sub TextBox14_Change()



End Sub



Private Sub TextBox16_Change()

Label20.Caption = TextBox16.Value

End Sub







Private Sub TextBox2_Change()



End Sub



Private Sub TextBox3_Change()



End Sub

Private Sub UserForm_Click()



End Sub



Private Sub UserForm_Initialize()



Dim i As Integer

Dim myDatec As Date

Mydate = Now()

For i = 0 To 365

ComboBox29.AddItem Format(DateAdd("d", i, Mydate), "mm/dd/yyyy")



Next





TextBox14.Value = Format(Date, "mm/dd/yyyy")





With ComboBox15



.AddItem "12:00:00 AM"

.AddItem "1:00:00 AM"

.AddItem "2:00:00 AM"

.AddItem "3:00:00 AM"

.AddItem "4:00:00 AM"

.AddItem "5:00:00 AM"

.AddItem "6:00:00 AM"

.AddItem "7:00:00 AM"

.AddItem "8:00:00 AM"

.AddItem "9:00:00 AM"

.AddItem "10:00:00 AM"

.AddItem "11:00:00 AM"

.AddItem "12:00:00 PM"

.AddItem "1:00:00 PM"

.AddItem "2:00:00 PM"

.AddItem "3:00:00 PM"

.AddItem "4:00:00 PM"

.AddItem "5:00:00 PM"

.AddItem "6:00:00 PM"

.AddItem "7:00:00 PM"

.AddItem "8:00:00 PM"

.AddItem "9:00:00 PM"

.AddItem "10:00:00 PM"

.AddItem "11:00:00 PM"

.AddItem "12:00:00 PM"



With ComboBox16

.AddItem "(CTOL)"

.AddItem "AF"

.AddItem "AK"

.AddItem "AL"

.AddItem "AM"

.AddItem "AN"

.AddItem "AP"

.AddItem "AQ"

.AddItem "AR"

.AddItem "AS"

.AddItem "AT"

.AddItem "AU"

.AddItem "AV"

.AddItem "AW"

.AddItem "AX"

.AddItem "AY"

.AddItem ""

.AddItem "(SVTOL)"

.AddItem "BF"

.AddItem "BK"

.AddItem "BL"

.AddItem "BM"

.AddItem "BN"

.AddItem "BP"

.AddItem "BQ"

.AddItem "BR"

.AddItem "BS"

.AddItem "BT"

.AddItem "BU"

.AddItem "BV"

.AddItem "BW"

.AddItem "BX"

.AddItem ""

.AddItem "(CV)"

.AddItem "CF"

.AddItem "CK"

.AddItem "CL"

.AddItem "CM"

.AddItem "CN"

.AddItem "CP"

.AddItem "CQ"

.AddItem "CR"

.AddItem "CS"

.AddItem "CT"

.AddItem "CU"

.AddItem "CV"

.AddItem "CW"

.AddItem "CX"





With ComboBox2 'clamps

.AddItem "Clamp Incorrectly Installed"

.AddItem "Damage Clamp"

.AddItem "Gapped Clamp"





With ComboBox3 'Dimentional

.AddItem "Bare Hole ID's"



With ComboBox4 'FOD

.AddItem "Fod (Metal Chips/Red Scarper/Orange Tape, etc.)"



With ComboBox5 'GAP

.AddItem "Gap Fill (Low/High/Void)"

.AddItem "Debris(Sealant Balls/Paint chips)"



With ComboBox6 'Harness

.AddItem "Harness Fouling (Including Clamps Fouling)"

.AddItem "Loose Clamp (Tube/Harness)"

.AddItem "Harness String Tie (Missing/Loose/Over tight)"

.AddItem "Harness Bend Radius"

.AddItem "Insufficient harness clearance"

.AddItem "Mis-Routed Wiring"



With ComboBox7 ' ID

.AddItem "Missing ID"

.AddItem "ID Label Under Clamp"



With ComboBox8 'Misc

.AddItem "FO (Off Product) (Not included in EIA VOC Per A/C"

.AddItem "Low Form 1"

.AddItem "TMS Fan Raised Material"

.AddItem "Hair/Dust/String(Not included in EIA VOC Per A/C Metrics)"

.AddItem "Center Not Grounded"



With ComboBox9 'paint

.AddItem "Drips where there not is supposed to be)"

.AddItem "Missing Paint/Flex prime (Touch up needed)"

.AddItem "Chipped Paint"

.AddItem "Bare Metal / Gouge"



With ComboBox10 'Hardware

.AddItem "Frozen Nutplate"

.AddItem "Bent (Strap/Tube/Structure)"

.AddItem "Incorrect Hardware"

.AddItem "Red Witness Line Visible on Connectors"

.AddItem "Uninstalled Connector"

.AddItem "Connector Clocked Incorrectly"

.AddItem "Loose Fastener"

With ComboBox11 'sealant

.AddItem "Sealant Void/Insufficient"

.AddItem "Sealant Re-entry"

.AddItem "Excessive Sealant"

.AddItem "Debris (Sealant Balls/Paint chips)"

.AddItem "Damaged Bulb Seal"

.AddItem "Unpromoted Seal"

.AddItem "Adhesive on Seal Frame"

.AddItem "Uncured Sealant (MU0008)"

.AddItem "Sealant Void/Insufficient"

.AddItem "Flex (Low/Missing/High)"

End With

With ComboBox12 'Surface Finish

.AddItem "Bare Metal / Gouge"

.AddItem "Scratches"

.AddItem "Missing Alodine"

End With

With ComboBox13 'Tubing

.AddItem "Tube"

.AddItem "Tube Fouling"

.AddItem "Tube Clearance"

.AddItem "Hydro Leak"

.AddItem "Loose Clamp"

.AddItem "Crushed/Deformed Convoluted Tube"

End With







End With

End With

End With

End With

End With

End With

End With

End With

End With

End With

End With




End Sub

 

[dmt32]

Hi
with time of year don't have the opportunity at moment to ponder your project any further although my first thoughts would be for so many comboboxes, to create a common function that you pass each combobox to.
The function could based on selection index + any adjustments, return the required column integer - but its just an initial thought & would need developing.

For time being, suggest you try and adapt my code for your other comboboxes & if need further assistance, place copy of your workbook with dummy data in a file sharing site like dropbox & provide link to it & will take a look in the new year.

Happy New Year

Dave

 

[Strycure12]

Hello ,

I expanded on your idea you mentioned earlier and then I tried the same code you have provided and added extra to it (please see below).
But as I add more to boxindex function or more info to the which column array, i receive a subscript error.

I believe i wrote everything right, furthermore the code works fine with the one combobox and boxindex but starts to degrade or confuse the VBA and start to have subscript error when I add more to the code in the same way.

I will create a dummy excel so you can see my dilemma.

Private Sub CommandButton1_Click()
Dim Tbl As ListObject
Dim NewRow As ListRow
Dim WhichColumn As Long, BoxIndex As Long

'get combobox selection listindex
BoxIndex = Me.ComboBox10.ListIndex + 1
If BoxIndex < 1 Then BoxIndex = 1

BoxIndex = Me.ComboBox5.ListIndex + 1
If BoxIndex < 1 Then BoxIndex = 1

BoxIndex = Me.ComboBox2.ListIndex + 1
If BoxIndex < 1 Then BoxIndex = 1


'set object variable
Set Tbl = Worksheets("sheet1").ListObjects("Table1")

'Add New row to the table
Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

'get column in table to post value of textbox10 to.
WhichColumn = Tbl.ListColumns(Choose(BoxIndex, "Damaged clamp", _
"clamp installed incorrectly")).Range.Column

WhichColumn = Tbl.ListColumns(Choose(BoxIndex, "Gap Fill (low/high/void)", debris)).Range.Column

WhichColumn = Tbl.ListColumns(Choose(BBoxIndex, "Frozen", "Bent", "Red line")).Range.column



'post new record to table
With NewRow
.Range(1).Value = Me.TextBox1.Value
.Range(2).Value = Me.TextBox2.Value
.Range(3).Value = Me.TextBox3.Value
.Range(4).Value = Me.TextBox4.Value
.Range(WhichColumn).Value = Me.TextBox10.Value
Range(WhichColumn).Value = Me.TextBox5.Value
Range(WhichColumn).Value = Me.TextBox2.Value

End With

End Sub

 

[dmt32]

Hi,
your updates are all using the same variable which will have the value of the the last combobox selection in the code - also, there are couple of typos in your update which will cause you issues.

You should be be able to resolve by changing the variables to arrays

Private Sub CommandButton1_Click()
    Dim Tbl                 As ListObject
    Dim NewRow              As ListRow
    Dim WhichColumn(1 To 3) As Long, BoxIndex(1 To 3) As Long
   
    'get combobox selections listindex
    BoxIndex(1) = Me.ComboBox10.ListIndex + 1
    If BoxIndex(1) < 1 Then BoxIndex(1) = 1
   
    BoxIndex(2) = Me.ComboBox5.ListIndex + 1
    If BoxIndex(2) < 1 Then BoxIndex(2) = 1
   
    BoxIndex(3) = Me.ComboBox2.ListIndex + 1
    If BoxIndex(3) < 1 Then BoxIndex(3) = 1
   
    'set object variable
    Set Tbl = Worksheets("sheet1").ListObjects("Table1")
   
    'Add New row to the table
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)
   
    'get column in table to post value of textbox10 to.
    WhichColumn(1) = Tbl.ListColumns(Choose(BoxIndex(1), "Damaged clamp", _
    "clamp installed incorrectly")).Range.Column
   
    'get column in table to post value of textbox5 to.
    WhichColumn(2) = Tbl.ListColumns(Choose(BoxIndex(2), "Gap Fill (low/high/void)", debris)).Range.Column
   
    'get column in table to post value of textbox2 to.
    WhichColumn(3) = Tbl.ListColumns(Choose(BoxIndex(3), "Frozen", "Bent", "Red line")).Range.Column
   
    'post new record to table
    With NewRow
        .Range(1).Value = Me.TextBox1.Value
        .Range(2).Value = Me.TextBox2.Value
        .Range(3).Value = Me.TextBox3.Value
        .Range(4).Value = Me.TextBox4.Value
        .Range(WhichColumn(1)).Value = Me.TextBox10.Value
        .Range(WhichColumn(2)).Value = Me.TextBox5.Value
        .Range(WhichColumn(3)).Value = Me.TextBox2.Value
    End With
End Sub

But if have many of these then may be alternative approaches can consider that would negate need for all the hard coding.
If still have issues, place copy of your workbook with dummy data on a filesharing site like dropbox & provide a link to it

Dave

 

[Strycure12]

Hello Dave ,

It worked the code needed the arrays to function properly, after about fifteen tests it has worked faultlessly , thank you for the assistance, it is very helpful and appreciated.

 

[dmt32]

Hello Dave ,

It worked the code needed the arrays to function properly, after about fifteen tests it has worked faultlessly , thank you for the assistance, it is very helpful and appreciated.

you are welcome glad suggestion resolved your issue

appreciate your feedback

Dave