Help with a double loop VBA

[daneasaur]

Hi, I am just getting back into coding vb again and am researching a little to write a small program for work.

I am trying to bring data from multiple comboboxes and paste them into cells on a worksheet.

I can do this fine for singular items but when I am making a loop to input a lot of data it doesn't seem to work

I am using the following code:
Dim Modules as ComboBox
For s = 1 To 8
For p = 8 To 15
Set Module = Me.Controls.Item("ModuleBox" & s)
Worksheets("Overview").Cells(2, p).Value = Modules.Value
Next p
Next s

I have tried to google for an answer but I am probably searching the wrong info as I'm sure this isn't a niche bit of code.

Any help would be greatly appreciated!

 

[daneasaur]

Sorry the code should be:
Dim Modules as ComboBox
For s = 1 To 8
For p = 8 To 15
Set Modules = Me.Controls.Item("ModuleBox" & s)
Worksheets("Overview").Cells(2, p).Value = Modules.Value
Next p
Next s

 

[My Aswer Is This]

So if I understand:
You have several Comboboxes on the same Worksheet
And you want to take the select value in the Combobox and enter that value somewhere on the active sheet is that correct?

 

[daneasaur]

So if I understand:
You have several Comboboxes on the same Worksheet
And you want to take the select value in the Combobox and enter that value somewhere on the active sheet is that correct?

That is correct, so it would be combobox1 value in cell 4, box2 value in cell 5 and so on

 

[My Aswer Is This]

You said:
That is correct, so it would be combobox1 value in cell 4, box2 value in cell 5 and so on

There is no "Cell 4"

This script will load all the values into Column A of the active sheet:

Private Sub CommandButton1_Click()
Dim Ctrl As Object
Dim i As Long
For Each Ctrl In ActiveSheet.OLEObjects
        If TypeName(Ctrl.Object) = "ComboBox" Then
           i = i + 1
        Cells(i, 1).Value = Ctrl.Object.Text
        End If
    Next
End Sub

 

[daneasaur]

Oh ok I will try the code you posted
I thought my code would set the column as B and then rows 8 through 15 with the value of comboboxes 1 through 8?

 

[My Aswer Is This]

If you want the values in column B try this:

Private Sub CommandButton1_Click()
'Modified  11/9/2022  11:50:13 PM  EST
Dim Ctrl As Object
Dim i As Long
ActiveSheet.Columns(2).Clear
For Each Ctrl In ActiveSheet.OLEObjects
        If TypeName(Ctrl.Object) = "ComboBox" Then
           i = i + 1
        Cells(i, "B").Value = Ctrl.Object.Text
        End If
    Next
End Sub

 

[daneasaur]

After re reading the code this morning I realised where I went wrong and it is because of my lack of understanding of the double loop
Instead I just kept one loop with a 's = s + 1' line after the values are changed

 

[My Aswer Is This]

After re reading the code this morning I realised where I went wrong and it is because of my lack of understanding of the double loop
Instead I just kept one loop with a 's = s + 1' line after the values are changed

Hope you have what you want. I did not know exactly what you wanted but showed you an example.
My script looks for all Comboboxes and puts their values in column A and then I used B
Glad you're learning to program using Vba
If you need more help let me know. Take care