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

trouble referencing and reading checkboxes

P: 21
Hello all,

I am fairly new to VBA and have had limited work with checkboxes before. I have turned an Excel worksheet into a form in which users can enter data and click checkboxes. I want to record the data, including the data within the checkboxes, onto another worksheet. I am having a lot of trouble referencing, much less reading the checkboxes.

I had used the format control to reference a cell, but this is not what I want. I need to be able to create a database to hold all the information recorded. Here is some sample code.

Sub EnterData_Click()
'Record free answer responses
Sheets("new pt data").Range("A2") = Sheets("new pt").Range("lname")
Sheets("new pt data").Range("B2") = Sheets("new pt").Range("fname")

'Record checkbox responses
Sheets("new pt data").Range("C2") = CheckBox1.Value

'Insert row to shift all records down
Sheets("new pt data").Range("A2").EntireRow.Insert
End Sub

Any suggestions?

Thanks
Jun 4 '08 #1
Share this Question
Share on Google+
4 Replies


kadghar
Expert 100+
P: 1,295
well

Im not pretty sure i understood, but to reference a CheckBox inside a worksheet, something like this will do:

Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Shapes("Check Box 1").Select
  2. Selection.Value = True
(since they're shapes, and you have to select it before using the 'value' property)

you can also check its value with something like
Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Shapes("Check Box 1").Select
  2. msgbox Selection.Value 
Save the value as an integer, and then asign it to another checkbox.

HTH
Jun 4 '08 #2

P: 21
Hi kadghar,

Thank you for replying so quickly. Sorry for my unclear post. I want to be able to link a checkbox value of true or false to a cell on another worksheet. Does the checkbox have to be selected to read the value? Usually, I would use the following code to be able to write results from one cell to another:

Range("A2")=Range("A1")

In this example, the value of cell A1 would be copied into the cell A2. How would I copy the value of a checkbox into cell A2?

Thanks for your help!

well

Im not pretty sure i understood, but to reference a CheckBox inside a worksheet, something like this will do:

Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Shapes("Check Box 1").Select
  2. Selection.Value = True
(since they're shapes, and you have to select it before using the 'value' property)

you can also check its value with something like
Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Shapes("Check Box 1").Select
  2. msgbox Selection.Value 
Save the value as an integer, and then asign it to another checkbox.

HTH
Jun 5 '08 #3

kadghar
Expert 100+
P: 1,295
Hi kadghar,

Thank you for replying so quickly. Sorry for my unclear post. I want to be able to link a checkbox value of true or false to a cell on another worksheet. Does the checkbox have to be selected to read the value? Usually, I would use the following code to be able to write results from one cell to another:

Range("A2")=Range("A1")

In this example, the value of cell A1 would be copied into the cell A2. How would I copy the value of a checkbox into cell A2?

Thanks for your help!
You can do that without VB code, there's an option to link it to a cell, but in case you wan to do it via code:

well, yes, it must be selected (i dont know why)
but the code is almost the same:

Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Shapes("Check Box 1").Select
  2. range("a2").value = Selection.Value 
if you want to do it from another sheet, well, something like this will do:

Expand|Select|Wrap|Line Numbers
  1. worksheets("sheet1").Shapes("Check Box 1").Select
  2. worksheets("sheet2").range("A2").value = selection.value
Now just have in mind that "check box 1" its not necessarily the shape's name, since it might change between languages, versions and how many check boxes you have created-deleted yet.

To check its name (since i dont remember if you can check it as a property), just start the Macro Recorder, right click the shape and move it or change its text. you'll see its name in the code you've recorded.

HTH
Jun 5 '08 #4

P: 21
Hi Kadghar,

Brilliant! Thank you very much for your help. It worked just as I wanted.

I had used the option to link it to a cell, but the problem with that is that the cell reference moves as I insert rows, rather than reading to a specific cell. The VB code method bypasses that by reading only to a specified cell.

Thanks again!

You can do that without VB code, there's an option to link it to a cell, but in case you wan to do it via code:

well, yes, it must be selected (i dont know why)
but the code is almost the same:

Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Shapes("Check Box 1").Select
  2. range("a2").value = Selection.Value 
if you want to do it from another sheet, well, something like this will do:

Expand|Select|Wrap|Line Numbers
  1. worksheets("sheet1").Shapes("Check Box 1").Select
  2. worksheets("sheet2").range("A2").value = selection.value
Now just have in mind that "check box 1" its not necessarily the shape's name, since it might change between languages, versions and how many check boxes you have created-deleted yet.

To check its name (since i dont remember if you can check it as a property), just start the Macro Recorder, right click the shape and move it or change its text. you'll see its name in the code you've recorded.

HTH
Jun 5 '08 #5

Post your reply

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