Quick Start to working with checkboxes etc within a worksheet
 

Morning, all,

Not having done anything like this before, I'd be gtarefful if anyone could
provide me with a quick start to working with checkboxes and other controls
within a worksheet - how to reference them, assign object variables, check
their statuses etc.

Specifically, i want to be able to make the contents of a cell change to
"Yes" or "No", depending on whether a checkbox is checked.

I know it's probably simple, but I haven't done it before, so any help would
be greatly appreciated.

Thanks in advance

Pete

Quick Start to working with checkboxes etc within a worksheet
 

There is a checkbox on the Forms toolbar. And there's a different checkbox on
the Control toolbox toolbar.

I'd use the checkboxes from the Forms toolbar.

Just add a checkbox to the worksheet.
rightclick on it and choose Format Control
on the Control tab, assign a nice linked cell
(Maybe column A of the row that holds the checkbox???)

Then this linked cell will be true or false depending on the "checkedness" of
that checkbox.

If you have to have yes/no, you could use a formula in column B (same row) that
looks like:

=if(a1=true,"yes","no")

And if you find that column A makes the worksheet look ugly, you could always
hide it when you're done creating the worksheet.




Peter Rooney wrote:

Morning, all,

Not having done anything like this before, I'd be gtarefful if anyone could
provide me with a quick start to working with checkboxes and other controls
within a worksheet - how to reference them, assign object variables, check
their statuses etc.

Specifically, i want to be able to make the contents of a cell change to
"Yes" or "No", depending on whether a checkbox is checked.

I know it's probably simple, but I haven't done it before, so any help would
be greatly appreciated.

Thanks in advance

Pete

--

Dave Peterson

Quick Start to working with checkboxes etc within a worksheet
 

Dave,

This is a great help. I'm now looking for a way do do this in VBA so I don't
need to link the object to the target range.

This is what I have so far - I don't get any errors, but the value of
TargetCell doesn't change either!

Any thoughts?

Sub CheckBox()

Dim CheckButton1 As Shape
Set CheckButton1 = Sheets("Sheet1").Shapes("Check Box 2")

Dim TargetCell As Range
Set TargetCell = Sheets("Sheet1").Range("Target1")

On Error GoTo ws_exit:

Application.EnableEvents = False

If CheckButton1 Then
TargetCell.Formula = "Yes"
Else
TargetCell.Formula = "No"
End If

ws_exit:
Application.EnableEvents = True

End Sub


Cheers

Pete





"Dave Peterson" wrote:

There is a checkbox on the Forms toolbar. And there's a different checkbox on
the Control toolbox toolbar.

I'd use the checkboxes from the Forms toolbar.

Just add a checkbox to the worksheet.
rightclick on it and choose Format Control
on the Control tab, assign a nice linked cell
(Maybe column A of the row that holds the checkbox???)

Then this linked cell will be true or false depending on the "checkedness" of
that checkbox.

If you have to have yes/no, you could use a formula in column B (same row) that
looks like:

=if(a1=true,"yes","no")

And if you find that column A makes the worksheet look ugly, you could always
hide it when you're done creating the worksheet.




Peter Rooney wrote:

Morning, all,

Not having done anything like this before, I'd be gtarefful if anyone could
provide me with a quick start to working with checkboxes and other controls
within a worksheet - how to reference them, assign object variables, check
their statuses etc.

Specifically, i want to be able to make the contents of a cell change to
"Yes" or "No", depending on whether a checkbox is checked.

I know it's probably simple, but I haven't done it before, so any help would
be greatly appreciated.

Thanks in advance

Pete

--

Dave Peterson

Quick Start to working with checkboxes etc within a worksheet
 

I'd still try to use a linked cell that was close to the checkbox and use the
position of the checkbox to make the change to the cell.

Then I could use just one macro (still assigned to all the checkboxes, though)
that does all the work.

Option Explicit
Sub testme()

Dim CBX As CheckBox
Dim myCell As Range

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
Set myCell = CBX.TopLeftCell.Offset(0, -1)

If CBX.Value = xlOn Then
myCell.Value = "Yes"
Else
myCell.Value = "No"
End If

End Sub

The .offset(0,-1) meant that I used a cell one column to the left of the
checkbox.



Peter Rooney wrote:

Dave,

This is a great help. I'm now looking for a way do do this in VBA so I don't
need to link the object to the target range.

This is what I have so far - I don't get any errors, but the value of
TargetCell doesn't change either!

Any thoughts?

Sub CheckBox()

Dim CheckButton1 As Shape
Set CheckButton1 = Sheets("Sheet1").Shapes("Check Box 2")

Dim TargetCell As Range
Set TargetCell = Sheets("Sheet1").Range("Target1")

On Error GoTo ws_exit:

Application.EnableEvents = False

If CheckButton1 Then
TargetCell.Formula = "Yes"
Else
TargetCell.Formula = "No"
End If

ws_exit:
Application.EnableEvents = True

End Sub

Cheers

Pete

"Dave Peterson" wrote:

There is a checkbox on the Forms toolbar. And there's a different checkbox on
the Control toolbox toolbar.

I'd use the checkboxes from the Forms toolbar.

Just add a checkbox to the worksheet.
rightclick on it and choose Format Control
on the Control tab, assign a nice linked cell
(Maybe column A of the row that holds the checkbox???)

Then this linked cell will be true or false depending on the "checkedness" of
that checkbox.

If you have to have yes/no, you could use a formula in column B (same row) that
looks like:

=if(a1=true,"yes","no")

And if you find that column A makes the worksheet look ugly, you could always
hide it when you're done creating the worksheet.




Peter Rooney wrote:

Morning, all,

Not having done anything like this before, I'd be gtarefful if anyone could
provide me with a quick start to working with checkboxes and other controls
within a worksheet - how to reference them, assign object variables, check
their statuses etc.

Specifically, i want to be able to make the contents of a cell change to
"Yes" or "No", depending on whether a checkbox is checked.

I know it's probably simple, but I haven't done it before, so any help would
be greatly appreciated.

Thanks in advance

Pete

--

Dave Peterson


--

Dave Peterson

Quick Start to working with checkboxes etc within a worksheet
 

Dave,

I'm puzzled - if a checkbox is a floating object, how do you specify an
offset to it? Also, Should "Application.caller" be changed to (in My Case)
"Check Box 2"?
I did this, but when I attach the macro to the check box and click it, all
that happens is I am placed in the VBA Editor, but with no error messages.
Now I'm REALLY confused! :-)

Pete

"Dave Peterson" wrote:

I'd still try to use a linked cell that was close to the checkbox and use the
position of the checkbox to make the change to the cell.

Then I could use just one macro (still assigned to all the checkboxes, though)
that does all the work.

Option Explicit
Sub testme()

Dim CBX As CheckBox
Dim myCell As Range

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
Set myCell = CBX.TopLeftCell.Offset(0, -1)

If CBX.Value = xlOn Then
myCell.Value = "Yes"
Else
myCell.Value = "No"
End If

End Sub

The .offset(0,-1) meant that I used a cell one column to the left of the
checkbox.



Peter Rooney wrote:

Dave,

This is a great help. I'm now looking for a way do do this in VBA so I don't
need to link the object to the target range.

This is what I have so far - I don't get any errors, but the value of
TargetCell doesn't change either!

Any thoughts?

Sub CheckBox()

Dim CheckButton1 As Shape
Set CheckButton1 = Sheets("Sheet1").Shapes("Check Box 2")

Dim TargetCell As Range
Set TargetCell = Sheets("Sheet1").Range("Target1")

On Error GoTo ws_exit:

Application.EnableEvents = False

If CheckButton1 Then
TargetCell.Formula = "Yes"
Else
TargetCell.Formula = "No"
End If

ws_exit:
Application.EnableEvents = True

End Sub

Cheers

Pete

"Dave Peterson" wrote:

There is a checkbox on the Forms toolbar. And there's a different checkbox on
the Control toolbox toolbar.

I'd use the checkboxes from the Forms toolbar.

Just add a checkbox to the worksheet.
rightclick on it and choose Format Control
on the Control tab, assign a nice linked cell
(Maybe column A of the row that holds the checkbox???)

Then this linked cell will be true or false depending on the "checkedness" of
that checkbox.

If you have to have yes/no, you could use a formula in column B (same row) that
looks like:

=if(a1=true,"yes","no")

And if you find that column A makes the worksheet look ugly, you could always
hide it when you're done creating the worksheet.




Peter Rooney wrote:

Morning, all,

Not having done anything like this before, I'd be gtarefful if anyone could
provide me with a quick start to working with checkboxes and other controls
within a worksheet - how to reference them, assign object variables, check
their statuses etc.

Specifically, i want to be able to make the contents of a cell change to
"Yes" or "No", depending on whether a checkbox is checked.

I know it's probably simple, but I haven't done it before, so any help would
be greatly appreciated.

Thanks in advance

Pete

--

Dave Peterson


--

Dave Peterson

Quick Start to working with checkboxes etc within a worksheet
 

First, make sure you used a checkbox from the Forms toobar--not a checkbox from
the Control Toolbox toolbar. And make sure you assigned the macro to the
checkbox.

And if you only have one checkbox to process, you could be specific in the code
(using "check box 2").

But if you have multiple checkboxes (only from the forms toolbar), then you can
use the same code for all of them. Application.caller returns the name of the
checkbox that was clicked. This way you don't need 57 macros for 57 different
checkboxes.

And even though the object floats over the worksheet's cells, it still can be
positioned over cells.

CBX.topleftcell
is the top left cell that is under the checkbox.
cbx.bottomrightcell is the bottom right cell

When you're driving through the intersection of State and Main, you're not
actually in that layer of asphalt. But by using that intersection, we can still
locate you.



Peter Rooney wrote:

Dave,

I'm puzzled - if a checkbox is a floating object, how do you specify an
offset to it? Also, Should "Application.caller" be changed to (in My Case)
"Check Box 2"?
I did this, but when I attach the macro to the check box and click it, all
that happens is I am placed in the VBA Editor, but with no error messages.
Now I'm REALLY confused! :-)

Pete

"Dave Peterson" wrote:

I'd still try to use a linked cell that was close to the checkbox and use the
position of the checkbox to make the change to the cell.

Then I could use just one macro (still assigned to all the checkboxes, though)
that does all the work.

Option Explicit
Sub testme()

Dim CBX As CheckBox
Dim myCell As Range

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
Set myCell = CBX.TopLeftCell.Offset(0, -1)

If CBX.Value = xlOn Then
myCell.Value = "Yes"
Else
myCell.Value = "No"
End If

End Sub

The .offset(0,-1) meant that I used a cell one column to the left of the
checkbox.



Peter Rooney wrote:

Dave,

This is a great help. I'm now looking for a way do do this in VBA so I don't
need to link the object to the target range.

This is what I have so far - I don't get any errors, but the value of
TargetCell doesn't change either!

Any thoughts?

Sub CheckBox()

Dim CheckButton1 As Shape
Set CheckButton1 = Sheets("Sheet1").Shapes("Check Box 2")

Dim TargetCell As Range
Set TargetCell = Sheets("Sheet1").Range("Target1")

On Error GoTo ws_exit:

Application.EnableEvents = False

If CheckButton1 Then
TargetCell.Formula = "Yes"
Else
TargetCell.Formula = "No"
End If

ws_exit:
Application.EnableEvents = True

End Sub

Cheers

Pete

"Dave Peterson" wrote:

There is a checkbox on the Forms toolbar. And there's a different checkbox on
the Control toolbox toolbar.

I'd use the checkboxes from the Forms toolbar.

Just add a checkbox to the worksheet.
rightclick on it and choose Format Control
on the Control tab, assign a nice linked cell
(Maybe column A of the row that holds the checkbox???)

Then this linked cell will be true or false depending on the "checkedness" of
that checkbox.

If you have to have yes/no, you could use a formula in column B (same row) that
looks like:

=if(a1=true,"yes","no")

And if you find that column A makes the worksheet look ugly, you could always
hide it when you're done creating the worksheet.




Peter Rooney wrote:

Morning, all,

Not having done anything like this before, I'd be gtarefful if anyone could
provide me with a quick start to working with checkboxes and other controls
within a worksheet - how to reference them, assign object variables, check
their statuses etc.

Specifically, i want to be able to make the contents of a cell change to
"Yes" or "No", depending on whether a checkbox is checked.

I know it's probably simple, but I haven't done it before, so any help would
be greatly appreciated.

Thanks in advance

Pete

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Quick Start to working with checkboxes etc within a worksheet
 

Dave,

Thanks for this - my brain is getting nicely full...

I'll give it a go in the morning and get back to you.

Regards

Pete



"Dave Peterson" wrote:

First, make sure you used a checkbox from the Forms toobar--not a checkbox from
the Control Toolbox toolbar. And make sure you assigned the macro to the
checkbox.

And if you only have one checkbox to process, you could be specific in the code
(using "check box 2").

But if you have multiple checkboxes (only from the forms toolbar), then you can
use the same code for all of them. Application.caller returns the name of the
checkbox that was clicked. This way you don't need 57 macros for 57 different
checkboxes.

And even though the object floats over the worksheet's cells, it still can be
positioned over cells.

CBX.topleftcell
is the top left cell that is under the checkbox.
cbx.bottomrightcell is the bottom right cell

When you're driving through the intersection of State and Main, you're not
actually in that layer of asphalt. But by using that intersection, we can still
locate you.



Peter Rooney wrote:

Dave,

I'm puzzled - if a checkbox is a floating object, how do you specify an
offset to it? Also, Should "Application.caller" be changed to (in My Case)
"Check Box 2"?
I did this, but when I attach the macro to the check box and click it, all
that happens is I am placed in the VBA Editor, but with no error messages.
Now I'm REALLY confused! :-)

Pete

"Dave Peterson" wrote:

I'd still try to use a linked cell that was close to the checkbox and use the
position of the checkbox to make the change to the cell.

Then I could use just one macro (still assigned to all the checkboxes, though)
that does all the work.

Option Explicit
Sub testme()

Dim CBX As CheckBox
Dim myCell As Range

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
Set myCell = CBX.TopLeftCell.Offset(0, -1)

If CBX.Value = xlOn Then
myCell.Value = "Yes"
Else
myCell.Value = "No"
End If

End Sub

The .offset(0,-1) meant that I used a cell one column to the left of the
checkbox.



Peter Rooney wrote:

Dave,

This is a great help. I'm now looking for a way do do this in VBA so I don't
need to link the object to the target range.

This is what I have so far - I don't get any errors, but the value of
TargetCell doesn't change either!

Any thoughts?

Sub CheckBox()

Dim CheckButton1 As Shape
Set CheckButton1 = Sheets("Sheet1").Shapes("Check Box 2")

Dim TargetCell As Range
Set TargetCell = Sheets("Sheet1").Range("Target1")

On Error GoTo ws_exit:

Application.EnableEvents = False

If CheckButton1 Then
TargetCell.Formula = "Yes"
Else
TargetCell.Formula = "No"
End If

ws_exit:
Application.EnableEvents = True

End Sub

Cheers

Pete

"Dave Peterson" wrote:

There is a checkbox on the Forms toolbar. And there's a different checkbox on
the Control toolbox toolbar.

I'd use the checkboxes from the Forms toolbar.

Just add a checkbox to the worksheet.
rightclick on it and choose Format Control
on the Control tab, assign a nice linked cell
(Maybe column A of the row that holds the checkbox???)

Then this linked cell will be true or false depending on the "checkedness" of
that checkbox.

If you have to have yes/no, you could use a formula in column B (same row) that
looks like:

=if(a1=true,"yes","no")

And if you find that column A makes the worksheet look ugly, you could always
hide it when you're done creating the worksheet.




Peter Rooney wrote:

Morning, all,

Not having done anything like this before, I'd be gtarefful if anyone could
provide me with a quick start to working with checkboxes and other controls
within a worksheet - how to reference them, assign object variables, check
their statuses etc.

Specifically, i want to be able to make the contents of a cell change to
"Yes" or "No", depending on whether a checkbox is checked.

I know it's probably simple, but I haven't done it before, so any help would
be greatly appreciated.

Thanks in advance

Pete

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Quick Start to working with checkboxes etc within a worksheet
 

Dave,

I've gone through this again this morning, and now my tiny brain has
effected knowledge transfer - thank you VERY much - green tick for you, sir!
:-)

Cheers

Pete



"Dave Peterson" wrote:

First, make sure you used a checkbox from the Forms toobar--not a checkbox from
the Control Toolbox toolbar. And make sure you assigned the macro to the
checkbox.

And if you only have one checkbox to process, you could be specific in the code
(using "check box 2").

But if you have multiple checkboxes (only from the forms toolbar), then you can
use the same code for all of them. Application.caller returns the name of the
checkbox that was clicked. This way you don't need 57 macros for 57 different
checkboxes.

And even though the object floats over the worksheet's cells, it still can be
positioned over cells.

CBX.topleftcell
is the top left cell that is under the checkbox.
cbx.bottomrightcell is the bottom right cell

When you're driving through the intersection of State and Main, you're not
actually in that layer of asphalt. But by using that intersection, we can still
locate you.



Peter Rooney wrote:

Dave,

I'm puzzled - if a checkbox is a floating object, how do you specify an
offset to it? Also, Should "Application.caller" be changed to (in My Case)
"Check Box 2"?
I did this, but when I attach the macro to the check box and click it, all
that happens is I am placed in the VBA Editor, but with no error messages.
Now I'm REALLY confused! :-)

Pete

"Dave Peterson" wrote:

I'd still try to use a linked cell that was close to the checkbox and use the
position of the checkbox to make the change to the cell.

Then I could use just one macro (still assigned to all the checkboxes, though)
that does all the work.

Option Explicit
Sub testme()

Dim CBX As CheckBox
Dim myCell As Range

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
Set myCell = CBX.TopLeftCell.Offset(0, -1)

If CBX.Value = xlOn Then
myCell.Value = "Yes"
Else
myCell.Value = "No"
End If

End Sub

The .offset(0,-1) meant that I used a cell one column to the left of the
checkbox.



Peter Rooney wrote:

Dave,

This is a great help. I'm now looking for a way do do this in VBA so I don't
need to link the object to the target range.

This is what I have so far - I don't get any errors, but the value of
TargetCell doesn't change either!

Any thoughts?

Sub CheckBox()

Dim CheckButton1 As Shape
Set CheckButton1 = Sheets("Sheet1").Shapes("Check Box 2")

Dim TargetCell As Range
Set TargetCell = Sheets("Sheet1").Range("Target1")

On Error GoTo ws_exit:

Application.EnableEvents = False

If CheckButton1 Then
TargetCell.Formula = "Yes"
Else
TargetCell.Formula = "No"
End If

ws_exit:
Application.EnableEvents = True

End Sub

Cheers

Pete

"Dave Peterson" wrote:

There is a checkbox on the Forms toolbar. And there's a different checkbox on
the Control toolbox toolbar.

I'd use the checkboxes from the Forms toolbar.

Just add a checkbox to the worksheet.
rightclick on it and choose Format Control
on the Control tab, assign a nice linked cell
(Maybe column A of the row that holds the checkbox???)

Then this linked cell will be true or false depending on the "checkedness" of
that checkbox.

If you have to have yes/no, you could use a formula in column B (same row) that
looks like:

=if(a1=true,"yes","no")

And if you find that column A makes the worksheet look ugly, you could always
hide it when you're done creating the worksheet.




Peter Rooney wrote:

Morning, all,

Not having done anything like this before, I'd be gtarefful if anyone could
provide me with a quick start to working with checkboxes and other controls
within a worksheet - how to reference them, assign object variables, check
their statuses etc.

Specifically, i want to be able to make the contents of a cell change to
"Yes" or "No", depending on whether a checkbox is checked.

I know it's probably simple, but I haven't done it before, so any help would
be greatly appreciated.

Thanks in advance

Pete

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson