Simple CheckBox Question

[JonathanS]

Hi -- I'm working on some VBA in Excel 2000 to automate some spreadsheet tasks. I have a really basic question about checkboxes... I've been using a "toy" program to try to figure out how to get the program to tell which box is checked. The form is created under forms in the VBA IDE (not in the sheet itself)

Sub Macro1()
 
 
Dim count As Integer
count = 0
MyForm.Show
 
    If MyForm.CheckBox1.Value Then
        count = count + 1
 
    End If
 
    If MyForm.CheckBox2.Value = 1 Then
        count = count + 1
 
    End If
 
    If MyForm.CheckBox3.Value = vbChecked Then
        count = count + 1
 
    End If
 
    MsgBox (count)
 
End Sub
 
 

Whenever I run this I get count = 1 no matter what is checked

I have also tried approaches "viewing the code" by right clicking on the control and attempting similar code (except using CheckBox1.Value instead.

If the CheckBox1_Click() is the only way to do it, how can I reference the responses in another macro (like with a global variable, perhaps?)

Apologies for the noob question, but I have searched around for a basic explanation of Checkboxes with a "Hello World" type example and found none.

[kadghar]

(...)
Whenever I run this I get count = 1 no matter what is checked

I have also tried approaches "viewing the code" by right clicking on the control and attempting similar code (except using CheckBox1.Value instead.

If the CheckBox1_Click() is the only way to do it, how can I reference the responses in another macro (like with a global variable, perhaps?)

Apologies for the noob question, but I have searched around for a basic explanation of Checkboxes with a "Hello World" type example and found none.

This is because when you Show the Form, the Macro in the module 'stops running' until you close MyForm.

When you close it, all checkboxes values are set to false. So the first IF wont add anything to Count, the second wont do it as well, but the third will, since vbChecked is not recognized by VBA so its taken as "empty" or in this very case as "False".. this is why the 3rd IF adds 1 to Count.

HTH

[WinblowsME]

' Macro
 
Sub Macro1()
   MyForm.Show
End Sub
 

' UserForm :  MyForm
' Checkbox :  Checkbox1
' Checkbox :  Checkbox2
' Checkbox :  Checkbox3
' Button   :  CommandButton1
 
Private Sub CommandButton1_Click()
   Dim count As Integer
 
   count = 0
 
   If MyForm.CheckBox1.Value Then
      count = 1
      MsgBox count & " is checked."
   End If
 
   If MyForm.CheckBox2.Value = True Then
      count = 2
      MsgBox count & " is checked."
   End If
 
   If MyForm.CheckBox3.Value = True Then
      count = 3
      MsgBox count & " is checked."
   End If
End Sub
 

[kadghar]

(...)   
   count = 0
 
   If MyForm.CheckBox1.Value Then
      count = 1
      MsgBox count & " is checked."
   End If
 
   If MyForm.CheckBox2.Value = True Then
      count = 2
      MsgBox count & " is checked."
   End If
 
   If MyForm.CheckBox3.Value = True Then
      count = 3
      MsgBox count & " is checked."
   End If
End Sub
 

I dont think this will count how many checkboxes are checked,
Count=count+1 was all right, no need to change it.

By the way, I think it's a good idea to use a Sub to show the form, and then, you can use another sub to count the checkboxex (this sub can be in the same module and just CALL it when you need it, or in a command's event in the Form, as WinblowsMe suggests)

[JonathanS]

Ok very cool.

I hadn't realized that the macro was suspended when the form was active.

Now in terms of returning count to the main macro, I've tried declaring it as global (as Variant or as Integer) and it seems to still be outside of the scope of the macro. What is the best way to return this?

I'm not that new to programming, but I just can't seem to "get" VB lol

Thanks very much for help on the prior post and for any help on this one!!

[kadghar]

Ok very cool.

I hadn't realized that the macro was suspended when the form was active.

Now in terms of returning count to the main macro, I've tried declaring it as global (as Variant or as Integer) and it seems to still be outside of the scope of the macro. What is the best way to return this?

I'm not that new to programming, but I just can't seem to "get" VB lol

Thanks very much for help on the prior post and for any help on this one!!

yes, thats a good idea

what i recommend you is to do this

public Count as integer '(make it global)

in macro1:
show the form
msgbox Count

in terminate event of the form:
do the 'count' thing

HTH

[kadghar]

oh, i forgot
I will also recommend you to use

    If MyForm.CheckBox1.Value Then
    count = count + 1        
End If

this way all times you're working with checkboxes.