Check Combobox is empty

[bcmk29]

With the below code, I need 2 help.

1. I would like to check if all the Combobox has value or not. In the screenshot, you can see I've created 2 new Combobox statically. When I execute this code the MsgBox throws the value Label and the code exits. I'm not sure where it's fetched from and the code doesn't alert for blank Combobox.

2. I would like to record the value selected in the new Combobox to cell S10. As I did for the previous 9 cells.

Private Sub CommandButton2_Click()
'UserForm.Show vbModeless
Dim c As Control
    For Each c In Me.Controls
        If TypeName(c) = "Text Box" Or TypeName(c) = "ComboBox" Or TypeName(c) = "CommandButton" Then
            If c.Value = "" Then
            MsgBox "Please fill all the blank field(s)"
            GoTo ok1
    Exit For
            Else: End If
        Else
        MsgBox TypeName(c)
        GoTo ok
        End If
    Next c
ok:
Sheets("Sheet3").Range("S2").Value = ComboBox99.Value
Sheets("Sheet3").Range("S3").Value = ComboBox22.Value
Sheets("Sheet3").Range("S4").Value = ComboBox33.Value
Sheets("Sheet3").Range("S5").Value = ComboBox44.Value
Sheets("Sheet3").Range("S6").Value = ComboBox55.Value
Sheets("Sheet3").Range("S7").Value = ComboBox66.Value
Sheets("Sheet3").Range("S8").Value = ComboBox77.Value
Sheets("Sheet3").Range("S9").Value = ComboBox88.Value
Unload DataMap
Call DataImport
ok1:
End Sub

 

[dmt32]

Hi,
based on code posted, see if this update helps

Private Sub CommandButton2_Click()
    Dim arr()       As Variant
    Dim i           As Long
   
    ReDim arr(1 To 8) '< increase size if additional combobox(es) added
   
    For i = 1 To UBound(arr)
        With Me.Controls("ComboBox" & Choose(i, 99, 22, 33, 44, 55, 66, 77, 88))
            If Len(.Value) > 0 Then
                arr(i) = .Value
            Else
                .SetFocus
                MsgBox "Please fill all the blank field(s)", 48, "Entry Required"
                Exit Sub
            End If
        End With
    Next i
   
    ThisWorkbook.Worksheets("Sheet3").Range("S2").Resize(, UBound(arr)).Value = arr
   
    Call DataImport
    Unload Me
End Sub

you have not shown it in your code but assuming your additional combobox has is default name (ComboBox + suffix) then just add the the suffix number where shown in BOLD in the Choose list.
Also, increase the size of the array.

Dave

 

[bcmk29]

Hi,
based on code posted, see if this update helps

Private Sub CommandButton2_Click()
    Dim arr()       As Variant
    Dim i           As Long
  
    ReDim arr(1 To 8) '< increase size if additional combobox(es) added
  
    For i = 1 To UBound(arr)
        With Me.Controls("ComboBox" & Choose(i, 99, 22, 33, 44, 55, 66, 77, 88))
            If Len(.Value) > 0 Then
                arr(i) = .Value
            Else
                .SetFocus
                MsgBox "Please fill all the blank field(s)", 48, "Entry Required"
                Exit Sub
            End If
        End With
    Next i
  
    ThisWorkbook.Worksheets("Sheet3").Range("S2").Resize(, UBound(arr)).Value = arr
  
    Call DataImport
    Unload Me
End Sub

you have not shown it in your code but assuming your additional combobox has is default name (ComboBox + suffix) then just add the the suffix number where shown in BOLD in the Choose list.
Also, increase the size of the array.

Dave

Thanks your code works fine, but the challenge is I'm not sure how many new Combobox will be added (1 to 8) is the default, and the rest is based on need and user choice. I can't keep changing the code every time.
Also, This code "ThisWorkbook.Worksheets("Sheet3").Range("S2").Resize(, UBound(arr)).Value = arr" only pastes the value selected in first combobox (99) but not the rest and newly added box as well.

 

[dmt32]

Hi,
as stated, solution is based off code you posted & will post user selection to specified range

example shows control names posted from array (arr)

21-10-2022.xls
	S	T	U	V	W	X	Y	Z
1
2	ComboBox99	ComboBox22	ComboBox33	ComboBox44	ComboBox55	ComboBox66	ComboBox77	ComboBox88
Sheet3

if you are doing something or wanting to do different like creating controls on the fly, then need to explain further & share what you have with forum

Dave

 

[bcmk29]

Hi,
as stated, solution is based off code you posted & will post user selection to specified range

example shows control names posted from array (arr)

21-10-2022.xls
	S	T	U	V	W	X	Y	Z
1
2	ComboBox99	ComboBox22	ComboBox33	ComboBox44	ComboBox55	ComboBox66	ComboBox77	ComboBox88
Sheet3

if you are doing something or wanting to do different like creating controls on the fly, then need to explain further & share what you have with forum

Dave

Thanks, I overlooked the output, the code pastes the selection to a specified Range by Row how do I change it to a column from S2?
Also, How do I make the arr static without limiting (1 To 8) and (i, 99, 22, 33, 44, 55, 66, 77, 88)?

 

[dmt32]

Thanks, I overlooked the output, the code pastes the selection to a specified Range by Row how do I change it to a column from S2?

replace with this line

ThisWorkbook.Worksheets("Sheet3").Range("S2").Resize(UBound(arr)).Value = Application.Transpose(arr)

Also, How do I make the arr static without limiting (1 To 8) and (i, 99, 22, 33, 44, 55, 66, 77, 88)?

I think you mean dynamic?
Your code shows fixed controls & solution is based on this - You will have to share / explain what it is you are doing to offer any further ideas
it would be helpful if could place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it

Dave

 

[bcmk29]

replace with this line

ThisWorkbook.Worksheets("Sheet3").Range("S2").Resize(UBound(arr)).Value = Application.Transpose(arr)

I think you mean dynamic?
Your code shows fixed controls & solution is based on this - You will have to share / explain what it is you are doing to offer any further ideas
it would be helpful if could place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it
Dave

Thanks for the code, transpose works now.

I need to import a set of data from a worksheet of user choice to my worksheet. My worksheet header is mentioned left in the screenshot as label under Default Header and these are mandatory, I'm importing the Data Header from the worksheet user selected under Import Header as Combobox which is mentioned right in the screenshot. Combobox (1 to8) are prefixed in the userform. The user needs to select the corresponding field associated with the Default Header. However, users can also add an additional Combobox to import additional data. In this screenshot, 2 blank Combobox are created dynamically using Add Header.

So, How do I make the arr dynamic without limiting (1 To 8) and (i, 99, 22, 33, 44, 55, 66, 77, 88)?

 

[dmt32]

However, users can also add an additional Combobox to import additional data. In this screenshot, 2 blank Combobox are created dynamically

Can you post the code that creates the controls on the fly

Dave

 

[bcmk29]

Can you post the code that creates the controls on the fly

Dave

Here you go!

Private Sub CommandButton1_Click()
Dim theLabel As Object
Dim Height As Long
Dim n, m As Integer
IH = Sheets("Sheet3").Cells(Rows.Count, 16).End(xlUp).Row
DH = Sheets("Sheet3").Cells(Rows.Count, 21).End(xlUp).Row

Sheets("Sheet3").Range("R1").Value = Sheets("Sheet3").Range("R1").Value + 1
n = Sheets("Sheet3").Range("R1").Value
    Height = 202
    Set theComboBox = DataMap.Controls.Add("Forms.ComboBox.1", True)
    With theComboBox
        .Name = "ComboBox" & n
        .Left = 17
        .Height = 20
        .Width = 92
        .Top = Height + (25 * n)
        .RowSource = "Sheet3!U10:U" & DH
    End With
    Set theComboBox = DataMap.Controls.Add("Forms.combobox.1", True)
    Height = 200
    With theComboBox
        .Name = "Combobox" & n + 1
        .Left = 114
        .Width = 107
        .Top = Height + (25 * n)
        .RowSource = "Sheet3!P2:P" & IH
    End With
    Height = 225
    With CommandButton1
        .Left = 30
        .Top = Height + (25 * n)
    End With
    Height = 225
    With CommandButton2
        .Left = 126
        .Top = Height + (25 * n)
    End With
    Height = 305
    DataMap.Height = Height + (25 * n)
    Height = 260
    With Label9
        .Caption = n & " Field Added"
        .Left = 86
        .Top = Height + (25 * n)
    If n > 1 Then
    .Caption = n & " Fields Added"
    End If
    End With
End Sub

 

[dmt32]

not fully tested but see if this update does what you want

Private Sub CommandButton2_Click()
    Dim arr()       As Variant
    Dim AddHeader   As Long, i As Long
    
    AddHeader = Val(Me.Tag)
    
    ReDim arr(1 To 8 + AddHeader)
    
    For i = 1 To UBound(arr)
        
        If i < 9 Then
            'mandatory controls
            With Me.Controls("ComboBox" & Choose(i, 99, 22, 33, 44, 55, 66, 77, 88))
                If Len(.Value) > 0 Then
                    arr(i) = .Value
                Else
                    MsgBox "Please fill all the blank field(s)", 48, "Entry Required"
                    .SetFocus
                    Exit Sub
                End If
            End With
            
        Else
            'addeded headers
            arr(i) = Me.Controls("ComboBox" & i - 8).Value
            
        End If
        
    Next i
    
    'post to range
    ThisWorkbook.Worksheets("Sheet3").Range("S2").Resize(UBound(arr)).Value = Application.Transpose(arr)
    
    Call DataImport
    Unload Me
End Sub

You will also need to replace your code that creates comboboxes on the fly with following

Private Sub CommandButton1_Click()
    Dim theLabel    As Object
    Dim Height      As Long, n As Long
    Static m        As Long
    
    IH = Sheets("Sheet3").Cells(Rows.Count, 16).End(xlUp).Row
    DH = Sheets("Sheet3").Cells(Rows.Count, 21).End(xlUp).Row
    
    Sheets("Sheet3").Range("R1").Value = Sheets("Sheet3").Range("R1").Value + 1
    n = Sheets("Sheet3").Range("R1").Value
    Height = 202
    Set theComboBox = Me.Controls.Add("Forms.ComboBox.1", True)
    m = m + 1
    With theComboBox
        .Name = "ComboBox" & m
        .Left = 17
        .Height = 20
        .Width = 92
        .Top = Height + (25 * n)
        .RowSource = "Sheet3!U10:U" & DH
    End With
    Set theComboBox = Me.Controls.Add("Forms.combobox.1", True)
    m = m + 1
    Height = 200
    With theComboBox
        .Name = "Combobox" & m
        .Left = 114
        .Width = 107
        .Top = Height + (25 * n)
        .RowSource = "Sheet3!P2:P" & IH
    End With
    Height = 225
    With CommandButton1
        .Left = 30
        .Top = Height + (25 * n)
    End With
    Height = 225
    With CommandButton2
        .Left = 126
        .Top = Height + (25 * n)
    End With
    Height = 305
    Me.Height = Height + (25 * n)
    Height = 260
    With Label9
        .Caption = n & " Field Added"
        .Left = 86
        .Top = Height + (25 * n)
        If n > 1 Then
            .Caption = n & " Fields Added"
        End If
    End With
    Me.Tag = m
End Sub

Dave