VBA Excel - collecting data in userform from program in module

Question

I am creating by code a userform that contain labels, textboxes and comboboxes per lines (see picture below). The number of line is variable (so is the number of comboboxes). I also create 3 buttons: OK, Cancel, Add. "Cancel" unload the form, "Add" opens another Userform to update the source of the comboboxes and "OK" put the infos, the user selected in the combobox, into a cell in the worksheet. All the buttons Call another Sub to execute:

With Userface.codemodule
        .insertlines 1, "end sub"
        .insertlines 1, "call Cansub"
        .insertlines 1, "Private Sub Canbt_Click()"
End With

'Validate  and register
With Userface.codemodule
        .insertlines 1, "end sub"
        .insertlines 1, "Call Oksub"
        .insertlines 1, ""
        .insertlines 1, "Public Sub Okbt_Click()"
End With

'add another category and/or reason
With Userface.codemodule
        .insertlines 1, "end sub"
        .insertlines 1, "call Addsub"
        .insertlines 1, "Private Sub Addbt_Click()"
End With

Application.VBE.MainWindow.Visible = True
VBA.UserForms.Add(Userface.Name).Show

Everything is working but the "OK" button. I don't want to use with userface.codemodule.insertlines 1,"" To write the whole thing. that is why I am calling a sub.

Here is the Public OKsub() the button calls:

Dim Addentr As Range
Dim Line As Long
Dim Cbtxt As String

Line = 0
Line = Range(Range("J1").End(xlDown).Offset(1, 0), Range("A1").End(xlDown)).Rows.Count
Set Addentr = ActiveWorkbook.Sheets("Combined").Range(Range("J1").End(xlDown).Offset(1, 0), Range("A1").End(xlDown))

For i = 0 To Line - 1
    Cbtxt = UserForm1.Controls("Cbo_A" & i).Value
    MsgBox "Combobox CBo_A" & i & " = " & Cbtxt

    If IsEmpty(Cbtxt) = False Then
        Addentr.Cells(i + 1, 10).Value = Cbtxt
    End If

    Cbtxt = UserForm1.Controls("Cbo_B" & i).Text

    If IsEmpty(Cbtxt) = False Then
        Addentr.Cells(i + 1, 11).Value = Cbtxt
    End If

Next i

MsgBox "Entries fulfilled"

The code runs without error BUT the "Cbtxt" value is empty as the msgbox shows here Most of the "solutions" I found speak of doing this once by giving the value to a public variable before moving to the worksheet. But, in my case, the number of input is changing so I have to include that into a loop. I cannot dedicated one variable per combobox (or can I?... or not).

I hope you will understand me guys. Thanks for the support!

Alright: I move the msgbox inside the If isempty()= false then... and it is appearing. that prove teh Cbtxt isn't empty but the variable is like Cbtxt = " " — MCircular
Would it help grouping your comboboxes inside a frame, then refering to the frame.controls instead of ALL of the controls...? — Badda_Bing
which combobox gets highlighted: UserForm1.Controls("Cbo_A" & i).BackColor = RGB(255, 255, 0) ? (I=1) — EvR
@Badda_Bing I want to get the value of EACH combobox. In the screenshot they are the same but in reality they won't be. every combobox will have its own value. That is why I named them with a number with a previous` For...Next` loop. The left one is 'Cbo_A & i' the right one is 'Cbo_B & i' with "i" as integer. — MCircular
@EvR The one in the message box (CBo_A1) is the second from the top in the "Equipment" column. They are numbered from 0 to... until needed. But it doesn't matter: All of them return nothing from the variable. — MCircular

MCircular MCircular · Accepted Answer · 2018-07-08T20:27:23

Right... I tried many things but it seems nothing else than the following is working. I wrote the code into the userform code module

With Userface.CodeModule
        .insertlines 1, "end sub"
        .insertlines 1, ""
        .insertlines 1, "call Cansub"
        .insertlines 1, "MsgBox ""Entries fulfilled"""
        .insertlines 1, "    Next i"
        .insertlines 1, "        End If"
        .insertlines 1, "            Addentr.Cells(i + 1, 11).Value = Cbtxt"
        .insertlines 1, "        If IsEmpty(Cbtxt) = False Then"
        .insertlines 1, "        Cbtxt = me.Controls(""Cbo_B"" & i).Value"

        .insertlines 1, "        End If"
        .insertlines 1, "           Addentr.Cells(i + 1, 10).Value = Cbtxt"
        .insertlines 1, "        If IsEmpty(Cbtxt) = False Then"
        .insertlines 1, "        Cbtxt = me.Controls(""Cbo_A"" & i).Value"

        .insertlines 1, "   For i = 0 To Line - 1"
        .insertlines 1, "Line = Range(Range(""J1"").End(xlDown).Offset(1, 0), Range(""A1"").End(xlDown)).Rows.Count"
        .insertlines 1, "Set Addentr = ActiveWorkbook.Sheets(""Combined"").Range(Range(""J1"").End(xlDown).Offset(1, 0), Range(""A1"").End(xlDown))"
        .insertlines 1, "Line = 0"
        .insertlines 1, "Dim Addentr As Range"
        .insertlines 1, "Dim Line As Long"
        .insertlines 1, "Dim Cbtxt As String"
        .insertlines 1, "Public Sub Okbt_Click()"
End With

`

NB: You have to read the program from bottom to top. I hate using .insertlines because it takes too much time to allocate the lines everytime. But I discovered than if you do like this you just copy/paste and it write the code upsidedown.

Thank you for those who tried to help.

VBA Excel - collecting data in userform from program in module

1 Answers