By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,982 Members | 1,932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,982 IT Pros & Developers. It's quick & easy.

Simple CheckBox Question

P: 37
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)

Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2.  
  3.  
  4. Dim count As Integer
  5. count = 0
  6. MyForm.Show
  7.  
  8.     If MyForm.CheckBox1.Value Then
  9.         count = count + 1
  10.  
  11.     End If
  12.  
  13.     If MyForm.CheckBox2.Value = 1 Then
  14.         count = count + 1
  15.  
  16.     End If
  17.  
  18.     If MyForm.CheckBox3.Value = vbChecked Then
  19.         count = count + 1
  20.  
  21.     End If
  22.  
  23.     MsgBox (count)
  24.  
  25. End Sub
  26.  
  27.  
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.
Jan 23 '08 #1
Share this Question
Share on Google+
6 Replies


kadghar
Expert 100+
P: 1,295
(...)
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
Jan 23 '08 #2

P: 58
Expand|Select|Wrap|Line Numbers
  1. ' Macro
  2.  
  3. Sub Macro1()
  4.    MyForm.Show
  5. End Sub
  6.  
Expand|Select|Wrap|Line Numbers
  1. ' UserForm :  MyForm
  2. ' Checkbox :  Checkbox1
  3. ' Checkbox :  Checkbox2
  4. ' Checkbox :  Checkbox3
  5. ' Button   :  CommandButton1
  6.  
  7. Private Sub CommandButton1_Click()
  8.    Dim count As Integer
  9.  
  10.    count = 0
  11.  
  12.    If MyForm.CheckBox1.Value Then
  13.       count = 1
  14.       MsgBox count & " is checked."
  15.    End If
  16.  
  17.    If MyForm.CheckBox2.Value = True Then
  18.       count = 2
  19.       MsgBox count & " is checked."
  20.    End If
  21.  
  22.    If MyForm.CheckBox3.Value = True Then
  23.       count = 3
  24.       MsgBox count & " is checked."
  25.    End If
  26. End Sub
  27.  
Jan 23 '08 #3

kadghar
Expert 100+
P: 1,295
Expand|Select|Wrap|Line Numbers
  1. (...)   
  2.    count = 0
  3.  
  4.    If MyForm.CheckBox1.Value Then
  5.       count = 1
  6.       MsgBox count & " is checked."
  7.    End If
  8.  
  9.    If MyForm.CheckBox2.Value = True Then
  10.       count = 2
  11.       MsgBox count & " is checked."
  12.    End If
  13.  
  14.    If MyForm.CheckBox3.Value = True Then
  15.       count = 3
  16.       MsgBox count & " is checked."
  17.    End If
  18. End Sub
  19.  
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)
Jan 23 '08 #4

P: 37
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!!
Jan 23 '08 #5

kadghar
Expert 100+
P: 1,295
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
Jan 23 '08 #6

kadghar
Expert 100+
P: 1,295
oh, i forgot
I will also recommend you to use

Expand|Select|Wrap|Line Numbers
  1.     If MyForm.CheckBox1.Value Then
  2.     count = count + 1        
  3. End If
this way all times you're working with checkboxes.
Jan 23 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.