textbox to show how many checkboxes the user checked off

[MFish]

Hi,

I have a code that works for one checkbox to update the textbox(txtTotalBulk)... But I'm clueless on how to add up the rest of the checkboxes... Code goes

Private Sub bulk1_Click()
Dim i As Integer
    i = 1
    If bulk1.Value = True Then
    txtTotalBulk.Value = i
    Else: End If
    If bulk1.Value = False Then
    txtTotalBulk.Value = i - i
    End If


End Sub

I add this to my other checkbox, bulk2, and the textbox will only refer to a single checkbox and won't add up the rest I click on. Total, I have 30 check boxes. Help, thank you.

 

[Leith Ross]

Hello MFish,

This will work for all of your check boxes provided they are not in a Frame and just on the UserForm.

Private Sub bulk1_Click()


    Dim n As Long
        
        n = CLng(txtTotalBulk)
        
        If ActiveControl.Value = True Then
            n = n + 1
        Else
            n = n - 1
        End If
        
        txtTotalBulk.Value = n


End Sub

If the check boxes are in one or more Frames then you will need to add the check box name to each Sub like this...

Private Sub bulk1_Click()


    Dim n As Long
        
        n = CLng(txtTotalBulk)
        
        If  bulk1.Value = True Then
            n = n + 1
        Else
            n = n - 1
        End If
        
        txtTotalBulk.Value = n


End Sub

Private Sub bulk2_Click()


    Dim n As Long
        
        n = CLng(txtTotalBulk)
        
        If  bulk2.Value = True Then
            n = n + 1
        Else
            n = n - 1
        End If
        
        txtTotalBulk.Value = n


End Sub

 

[MFish]

Hi,

Code:

Private Sub bulk1_Click()


    Dim n As Long
        
        n = CLng(txtTotalBulk)
        
        If ActiveControl.Value = True Then
            n = n + 1
        Else
            n = n - 1
        End If
        
        txtTotalBulk.Value = n


End Sub

Gives me an error code when I click in the checkbox, bulk1... Runtime error '13' Mismatch. Highlights the n=CLng (txttotalbulk) line.

 

[Leith Ross]

Hello MFish,

The error will occur anytime the value in the TextBox is an empty string or non numeric value. This will fix the issue...

Private Sub bulk1_Click()


    Dim n As Long
        
        On Error Resume Next
            n = CLng(txtTotalBulk)
        On Error GoTo 0
        
        If ActiveControl.Value = True Then
            n = n + 1
        Else
            n = n - 1
        End If
        
        txtTotalBulk.Value = n


End Sub

 

[MFish]

Awesome, it works! Thanks.

Now, I have another textbox that I'll need to count the number of comboboxes with values in it.. Return it as a whole number. I have 30 comboboxes that will have a list to choose from. If I select 4 comboboxes with any value, the textbox will return with a value of 4. Similar code I'm assuming?

Code:

Private Sub combobox1_change()


    Dim n As Long
        
        On Error Resume Next
            n = CLng(txtTotalPallet)
        On Error GoTo 0
        
        If combobox1.Value = "*" Then
            n = n + 1
        Else
            n = n - 1
        End If
        
        txtTotalPallet.Value = n


End Sub

 

[MFish]

Alright,

The code above doesn't work how I need it to...

I just need a code that will count the number of comboboxes that are currently being used in the userform. I have 30 comboboxes and I will need to show a value in a textbox that correlates to how many comboboxes will be used. If only 5 are used then in the textbox it will say 5. I'm not worried about what the specific selection is chosen in the combobox just if the combobox is being used only.

 

[Leith Ross]

Hello MFish,

I am not sure I understand the difference between your questions but go ahead and try it. If it fails let me know.

Since you have so many check boxes and at least 2 text boxes to fill with values, you might be better off using an more advanced technique known as Sub-Classing. This technique allows multiple controls to share the same macro code using an event. To help you do this I would need to have a copy of your workbook.

 

[MFish]

Hello MFish,

I am not sure I understand the difference between your questions but go ahead and try it. If it fails let me know.

Since you have so many check boxes and at least 2 text boxes to fill with values, you might be better off using an more advanced technique known as Sub-Classing. This technique allows multiple controls to share the same macro code using an event. To help you do this I would need to have a copy of your workbook.

The code with the checkboxes worked great, I'm now on the last part of "wants". I need to show a value in a textbox with how many comboboxes I have used. I need textbox, txtTotalPallet, to update every time I change a combobox. I have used this code..

Private Sub combobox1_Change()
 Dim n As Long
        
        On Error Resume Next
            n = CLng(txtTotalPallet)
        On Error GoTo 0
        
        If ComboBox1.Value = "" Then
            n = n - 1
        Else
            n = n + 1
        End If
        
        txtTotalPallet.Value = n
End Sub

It works great but the issue I have is every time I may switch a box to edit, it keeps counting up. How can I edit this to my likings?

 

[Leith Ross]

Hello MFish,

I did not forget about you. I had to leave suddenly on business and did not return for several hours. If you can upload a copy of your workbook to a file sharing site then I can examine your code and provide you with an answer. If that option is not available then perhaps you can email a copy to me. Let me know what works best for you.