trouble referencing and reading checkboxes

[lindabaldwin]

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("A 2") = Sheets("new pt").Range("lna me")
Sheets("new pt data").Range("B 2") = Sheets("new pt").Range("fna me")

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

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

Any suggestions?

Thanks

[kadghar]

well

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

[CODE=vb]ActiveSheet.Sha pes("Check Box 1").Select
Selection.Value = True[/CODE]

(since they're shapes, and you have to select it before using the 'value' property)

you can also check its value with something like
[CODE=vb]
ActiveSheet.Sha pes("Check Box 1").Select
msgbox Selection.Value [/CODE]

Save the value as an integer, and then asign it to another checkbox.

HTH

[lindabaldwin]

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")=Ran ge("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:

[CODE=vb]ActiveSheet.Sha pes("Check Box 1").Select
Selection.Value = True[/CODE]

(since they're shapes, and you have to select it before using the 'value' property)

you can also check its value with something like
[CODE=vb]
ActiveSheet.Sha pes("Check Box 1").Select
msgbox Selection.Value [/CODE]

Save the value as an integer, and then asign it to another checkbox.

HTH

[kadghar]

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")=Ran ge("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:

[CODE=vb]ActiveSheet.Sha pes("Check Box 1").Select
range("a2").val ue = Selection.Value [/CODE]

if you want to do it from another sheet, well, something like this will do:

[CODE=vb]worksheets("she et1").Shapes("C heck Box 1").Select
worksheets("she et2").range("A2 ").value = selection.value[/CODE]

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

[lindabaldwin]

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:

[CODE=vb]ActiveSheet.Sha pes("Check Box 1").Select
range("a2").val ue = Selection.Value [/CODE]

if you want to do it from another sheet, well, something like this will do:

[CODE=vb]worksheets("she et1").Shapes("C heck Box 1").Select
worksheets("she et2").range("A2 ").value = selection.value[/CODE]

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