Hello,

I have Check Boxes (from the Control Toobox toolbar) in my worksheet. Some
of these are linked to cells so that I can write IF statements to see the
status of True or False.

My problem is that when the users click the Clear Form command button, the
tick marks are cleared from all check boxes, except for those that are linked
to a cell. Those ones have the grayed out ticked mark, even though the
Triple State property is False. Here is the part of the code that clears the
check boxes:

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With


Thanks.

I'm betting that your worksheet is protected and those linkedcells are locked.

You could try to unlock those linkedcells and test it again.

Maybe move the linked cells to a (hidden) sheet--or hide that column???

Joyce wrote:

Hello,

I have Check Boxes (from the Control Toobox toolbar) in my worksheet. Some
of these are linked to cells so that I can write IF statements to see the
status of True or False.

My problem is that when the users click the Clear Form command button, the
tick marks are cleared from all check boxes, except for those that are linked
to a cell. Those ones have the grayed out ticked mark, even though the
Triple State property is False. Here is the part of the code that clears the
check boxes:

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

Thanks.

--

Dave Peterson

Hi Dave,

Thanks for your response. The sheet is indeed protected and the linked
cells are unlocked and reside in a hidden column.

What happens is that the TRUE value simply gets cleared and the value in the
actual control property is removed - it doesn't display false - just nothing,
as though it was deleted.

Any other ideas?

Thanks very much.

"Dave Peterson" wrote:

I'm betting that your worksheet is protected and those linkedcells are locked.

You could try to unlock those linkedcells and test it again.

Maybe move the linked cells to a (hidden) sheet--or hide that column???

Joyce wrote:

Hello,

I have Check Boxes (from the Control Toobox toolbar) in my worksheet. Some
of these are linked to cells so that I can write IF statements to see the
status of True or False.

My problem is that when the users click the Clear Form command button, the
tick marks are cleared from all check boxes, except for those that are linked
to a cell. Those ones have the grayed out ticked mark, even though the
Triple State property is False. Here is the part of the code that clears the
check boxes:

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

Thanks.

--

Dave Peterson

First, your code worked fine for me in xl2003 with the linked cells unlocked
(with or without the sheet being protected).

But maybe you could change the linked cell to false instead (or too???).

Option Explicit
Sub testme()
Dim Ctrl As OLEObject
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
On Error Resume Next
.Range(Ctrl.LinkedCell).Value = False
On Error GoTo 0
Ctrl.Object.Value = False
End If
Next Ctrl
End With
End Sub




Joyce wrote:

Hi Dave,

Thanks for your response. The sheet is indeed protected and the linked
cells are unlocked and reside in a hidden column.

What happens is that the TRUE value simply gets cleared and the value in the
actual control property is removed - it doesn't display false - just nothing,
as though it was deleted.

Any other ideas?

Thanks very much.

"Dave Peterson" wrote:

I'm betting that your worksheet is protected and those linkedcells are locked.

You could try to unlock those linkedcells and test it again.

Maybe move the linked cells to a (hidden) sheet--or hide that column???

Joyce wrote:

Hello,

I have Check Boxes (from the Control Toobox toolbar) in my worksheet. Some
of these are linked to cells so that I can write IF statements to see the
status of True or False.

My problem is that when the users click the Clear Form command button, the
tick marks are cleared from all check boxes, except for those that are linked
to a cell. Those ones have the grayed out ticked mark, even though the
Triple State property is False. Here is the part of the code that clears the
check boxes:

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

Thanks.

--

Dave Peterson


--

Dave Peterson

Hi again,

I tried modifying the code, but it still removes the link (which I don't
want it to do) and also removes the Value. Instead of False, it seems to
just change the value from True to nothing.

This results in a Null status, where the check mark is grey.

I'm also using 2003. I'm puzzled as to why it wouldn't work for me, but
works for you.

"Dave Peterson" wrote:

First, your code worked fine for me in xl2003 with the linked cells unlocked
(with or without the sheet being protected).

But maybe you could change the linked cell to false instead (or too???).

Option Explicit
Sub testme()
Dim Ctrl As OLEObject
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
On Error Resume Next
.Range(Ctrl.LinkedCell).Value = False
On Error GoTo 0
Ctrl.Object.Value = False
End If
Next Ctrl
End With
End Sub




Joyce wrote:

Hi Dave,

Thanks for your response. The sheet is indeed protected and the linked
cells are unlocked and reside in a hidden column.

What happens is that the TRUE value simply gets cleared and the value in the
actual control property is removed - it doesn't display false - just nothing,
as though it was deleted.

Any other ideas?

Thanks very much.

"Dave Peterson" wrote:

I'm betting that your worksheet is protected and those linkedcells are locked.

You could try to unlock those linkedcells and test it again.

Maybe move the linked cells to a (hidden) sheet--or hide that column???

Joyce wrote:

Hello,

I have Check Boxes (from the Control Toobox toolbar) in my worksheet. Some
of these are linked to cells so that I can write IF statements to see the
status of True or False.

My problem is that when the users click the Clear Form command button, the
tick marks are cleared from all check boxes, except for those that are linked
to a cell. Those ones have the grayed out ticked mark, even though the
Triple State property is False. Here is the part of the code that clears the
check boxes:

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

Thanks.

--

Dave Peterson


--

Dave Peterson

You changed the linkedcell itself.

The code I suggested changed the value in the linkedcell.

Joyce wrote:

Hi again,

I tried modifying the code, but it still removes the link (which I don't
want it to do) and also removes the Value. Instead of False, it seems to
just change the value from True to nothing.

This results in a Null status, where the check mark is grey.

I'm also using 2003. I'm puzzled as to why it wouldn't work for me, but
works for you.

"Dave Peterson" wrote:

First, your code worked fine for me in xl2003 with the linked cells unlocked
(with or without the sheet being protected).

But maybe you could change the linked cell to false instead (or too???).

Option Explicit
Sub testme()
Dim Ctrl As OLEObject
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
On Error Resume Next
.Range(Ctrl.LinkedCell).Value = False
On Error GoTo 0
Ctrl.Object.Value = False
End If
Next Ctrl
End With
End Sub




Joyce wrote:

Hi Dave,

Thanks for your response. The sheet is indeed protected and the linked
cells are unlocked and reside in a hidden column.

What happens is that the TRUE value simply gets cleared and the value in the
actual control property is removed - it doesn't display false - just nothing,
as though it was deleted.

Any other ideas?

Thanks very much.

"Dave Peterson" wrote:

I'm betting that your worksheet is protected and those linkedcells are locked.

You could try to unlock those linkedcells and test it again.

Maybe move the linked cells to a (hidden) sheet--or hide that column???

Joyce wrote:

Hello,

I have Check Boxes (from the Control Toobox toolbar) in my worksheet. Some
of these are linked to cells so that I can write IF statements to see the
status of True or False.

My problem is that when the users click the Clear Form command button, the
tick marks are cleared from all check boxes, except for those that are linked
to a cell. Those ones have the grayed out ticked mark, even though the
Triple State property is False. Here is the part of the code that clears the
check boxes:

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi again,

I just figured out the problem. I want the code available from all
worksheets, so placed it in a module. When I moved it to Workbook, it worked
fine.

Thanks for your help. I really appreciate it.


"Dave Peterson" wrote:

First, your code worked fine for me in xl2003 with the linked cells unlocked
(with or without the sheet being protected).

But maybe you could change the linked cell to false instead (or too???).

Option Explicit
Sub testme()
Dim Ctrl As OLEObject
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
On Error Resume Next
.Range(Ctrl.LinkedCell).Value = False
On Error GoTo 0
Ctrl.Object.Value = False
End If
Next Ctrl
End With
End Sub




Joyce wrote:

Hi Dave,

Thanks for your response. The sheet is indeed protected and the linked
cells are unlocked and reside in a hidden column.

What happens is that the TRUE value simply gets cleared and the value in the
actual control property is removed - it doesn't display false - just nothing,
as though it was deleted.

Any other ideas?

Thanks very much.

"Dave Peterson" wrote:

I'm betting that your worksheet is protected and those linkedcells are locked.

You could try to unlock those linkedcells and test it again.

Maybe move the linked cells to a (hidden) sheet--or hide that column???

Joyce wrote:

Hello,

I have Check Boxes (from the Control Toobox toolbar) in my worksheet. Some
of these are linked to cells so that I can write IF statements to see the
status of True or False.

My problem is that when the users click the Clear Form command button, the
tick marks are cleared from all check boxes, except for those that are linked
to a cell. Those ones have the grayed out ticked mark, even though the
Triple State property is False. Here is the part of the code that clears the
check boxes:

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

Thanks.

--

Dave Peterson


--

Dave Peterson

You code used the activesheet. I don't see how moving it to the ThisWorkbook
module would help (or harm) its execution.

I suspect a different problem--maybe a change you didn't share.

Joyce wrote:

Hi again,

I just figured out the problem. I want the code available from all
worksheets, so placed it in a module. When I moved it to Workbook, it worked
fine.

Thanks for your help. I really appreciate it.

"Dave Peterson" wrote:

First, your code worked fine for me in xl2003 with the linked cells unlocked
(with or without the sheet being protected).

But maybe you could change the linked cell to false instead (or too???).

Option Explicit
Sub testme()
Dim Ctrl As OLEObject
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
On Error Resume Next
.Range(Ctrl.LinkedCell).Value = False
On Error GoTo 0
Ctrl.Object.Value = False
End If
Next Ctrl
End With
End Sub




Joyce wrote:

Hi Dave,

Thanks for your response. The sheet is indeed protected and the linked
cells are unlocked and reside in a hidden column.

What happens is that the TRUE value simply gets cleared and the value in the
actual control property is removed - it doesn't display false - just nothing,
as though it was deleted.

Any other ideas?

Thanks very much.

"Dave Peterson" wrote:

I'm betting that your worksheet is protected and those linkedcells are locked.

You could try to unlock those linkedcells and test it again.

Maybe move the linked cells to a (hidden) sheet--or hide that column???

Joyce wrote:

Hello,

I have Check Boxes (from the Control Toobox toolbar) in my worksheet. Some
of these are linked to cells so that I can write IF statements to see the
status of True or False.

My problem is that when the users click the Clear Form command button, the
tick marks are cleared from all check boxes, except for those that are linked
to a cell. Those ones have the grayed out ticked mark, even though the
Triple State property is False. Here is the part of the code that clears the
check boxes:

'Clear check boxes
With ActiveSheet
For Each Ctrl In .OLEObjects
If TypeName(Ctrl.Object) = "CheckBox" Then
Ctrl.Object.Value = False
End If
Next Ctrl
End With

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson