Check Combobox is empty

B

bcmk29

Board Regular
Joined
Oct 20, 2022
Messages
55
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

VBA Code: 
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


1666346757872.png
 
Sort by date Sort by votes
dmt32 said:
not fully tested but see if this update does what you want

VBA Code: 
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

VBA Code: 
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
Click to expand...
Hi Dave,

Your code works well for the existing 8 combo boxes when I create a new one it doesn't verify whether it's blank or not and the challenge is with the below line of code to my knowledge. I added 1 next to 88 and it works well if I create 1 new Combobox. If I add two Combobox still it doesn't work unless I add value 2 to the code. Also, once after I add 1, and 2 to the code and proceeded without adding any new Combobox the next time it still throws me an error. How do we change these number dynamic?

VBA Code: 
With Me.Controls("ComboBox" & Choose(i, 99, 22, 33, 44, 55, 66, 77, 88))
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
bcmk29 said:
Hi Dave,

Your code works well for the existing 8 combo boxes when I create a new one it doesn't verify whether it's blank or not
Click to expand...
sorry, I thought additional comboboxes were optional

try this update

VBA Code: 
Private Sub CommandButton2_Click()
    Dim arr()       As Variant
    Dim AddHeader   As Long, i As Long
    Dim IsComplete  As Boolean
   
    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))
                IsComplete = Len(.Value) > 0
                If IsComplete Then arr(i) = .Value Else .SetFocus: Exit For
            End With
           
        Else
            'added headers
            With Me.Controls("ComboBox" & i - 8)
                IsComplete = Len(.Value) > 0
                If IsComplete Then arr(i) = .Value Else .SetFocus: Exit For
            End With
        End If
       
    Next i
   
    If IsComplete Then
        'post to range
        ThisWorkbook.Worksheets("Sheet3").Range("S2").Resize(UBound(arr)).Value = Application.Transpose(arr)
       
        Call DataImport
        Unload Me
       
    Else
       
        MsgBox "Please fill all the blank field(s)", 48, "Entry Required"
       
    End If
End Sub

your other error I suspect is as a result of the index value in sheet3.Range("R1") not being re-set to 0 (zero) when you close the form

try this updated code & see if resolves

VBA Code: 
Private Sub CommandButton1_Click()
    Dim theLabel    As Object
    Dim Height      As Long, IH As Long, DH As Long
    Static m        As Long, n As Long
   
    IH = Sheets("Sheet3").Cells(Rows.Count, 16).End(xlUp).Row
    DH = Sheets("Sheet3").Cells(Rows.Count, 21).End(xlUp).Row
   
    n = n + 1
   
    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
 
Last edited:
Upvote 0
Solution
dmt32 said:
sorry, I thought additional comboboxes were optional

try this update

VBA Code: 
Private Sub CommandButton2_Click()
    Dim arr()       As Variant
    Dim AddHeader   As Long, i As Long
    Dim IsComplete  As Boolean
  
    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))
                IsComplete = Len(.Value) > 0
                If IsComplete Then arr(i) = .Value Else .SetFocus: Exit For
            End With
          
        Else
            'added headers
            With Me.Controls("ComboBox" & i - 8)
                IsComplete = Len(.Value) > 0
                If IsComplete Then arr(i) = .Value Else .SetFocus: Exit For
            End With
        End If
      
    Next i
  
    If IsComplete Then
        'post to range
        ThisWorkbook.Worksheets("Sheet3").Range("S2").Resize(UBound(arr)).Value = Application.Transpose(arr)
      
        Call DataImport
        Unload Me
      
    Else
      
        MsgBox "Please fill all the blank field(s)", 48, "Entry Required"
      
    End If
End Sub

your other error I suspect is as a result of the index value in sheet3.Range("R1") not being re-set to 0 (zero) when you close the form

try this updated code & see if resolves

VBA Code: 
Private Sub CommandButton1_Click()
    Dim theLabel    As Object
    Dim Height      As Long, IH As Long, DH As Long
    Static m        As Long, n As Long
  
    IH = Sheets("Sheet3").Cells(Rows.Count, 16).End(xlUp).Row
    DH = Sheets("Sheet3").Cells(Rows.Count, 21).End(xlUp).Row
  
    n = n + 1
  
    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
Click to expand...
Dave, thanks for the revised code and your suggestion both worked well as expected.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
314
bdt
B
R
  • Question Question
Having Trouble getting this code in VBA to work. Attempting to download a list of PDFs
Replies
1
Views
125
6StringJazzer
6StringJazzer
M
  • Question Question
What am I doing Wrong?
Replies
6
Views
261
MBarrett
M
K
  • Question Question
VBA combobox data
Replies
2
Views
522
KingGoku
K
C
  • Solved
VBA - Checking for Duplicates
Replies
2
Views
834
cwhaley1
C

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top