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



## Strycure12 (Dec 27, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

Strycure12 said:


> 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 (Dec 30, 2022)

Hello Dave ,

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



> Private Sub ComboBox16_Change()
> 
> Label19.Caption = ComboBox16.Value
> 
> ...


----------



## dmt32 (Dec 30, 2022)

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 (Jan 2, 2023)

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


----------



## dmt32 (Jan 3, 2023)

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 (Jan 5, 2023)

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 (Jan 5, 2023)

Strycure12 said:


> 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


----------

