Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
A correction - the linked cell stays linked in the checkbox properties.
It's the value that is removed and causes the Null. As soon as I reclick the checkbox, but linked cell displays True/False. "Dave Peterson" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
You're absolutely right.
I tested it and am having the same problem. It seems to be caused by the linked cell and protection. If the linked cell is locked and the sheet unprotected, it works fine. Of course, if I protect (which I want to do) and try to click the checkbox, I get an error message because the linked cell is locked. If I unlock it, I can click the box on and off, but when I run the code to clear the checkbox, it simply removes both the link and the status, so ends up with the grayed out check (null). Thanks for your help. "Dave Peterson" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
I've put the linked cells on a separate unprotected worksheet, which seems to
do the trick. Not sure what the conflict is when it's on the same sheet, but this is workable solution. Again, thanks very much for your time and assistance. "Dave Peterson" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
Any chance you have another macro (maybe an event macro) that runs and changes
the false to empty. And I'd check that tiplestate property once more. It really sounds like it's not false. But you can do these as experiments--since you have a way that works. Joyce wrote: I've put the linked cells on a separate unprotected worksheet, which seems to do the trick. Not sure what the conflict is when it's on the same sheet, but this is workable solution. Again, thanks very much for your time and assistance. "Dave Peterson" wrote: 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 -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
Hi Dave,
No, I don't have another macro that runs and changes the false to empty. On the check boxes that aren't linked, it works fine. It's the linked cell that seems to cause the problem. I've also checked the Triplestate property and it's still False. It's just as though the True/False has been deleted in the Value property. It seems to be when the sheet the linked cell resides on is protected it causes the problem. Just not sure why. "Dave Peterson" wrote: Any chance you have another macro (maybe an event macro) that runs and changes the false to empty. And I'd check that tiplestate property once more. It really sounds like it's not false. But you can do these as experiments--since you have a way that works. Joyce wrote: I've put the linked cells on a separate unprotected worksheet, which seems to do the trick. Not sure what the conflict is when it's on the same sheet, but this is workable solution. Again, thanks very much for your time and assistance. "Dave Peterson" wrote: 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 -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
Your original code worked fine for me (as I wrote before) using xl2003.
What version of excel are you using? (If you said, I forgot <vbg.) Joyce wrote: Hi Dave, No, I don't have another macro that runs and changes the false to empty. On the check boxes that aren't linked, it works fine. It's the linked cell that seems to cause the problem. I've also checked the Triplestate property and it's still False. It's just as though the True/False has been deleted in the Value property. It seems to be when the sheet the linked cell resides on is protected it causes the problem. Just not sure why. "Dave Peterson" wrote: Any chance you have another macro (maybe an event macro) that runs and changes the false to empty. And I'd check that tiplestate property once more. It really sounds like it's not false. But you can do these as experiments--since you have a way that works. Joyce wrote: I've put the linked cells on a separate unprotected worksheet, which seems to do the trick. Not sure what the conflict is when it's on the same sheet, but this is workable solution. Again, thanks very much for your time and assistance. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
I did mention it, but no worries :-)
xl 2003. Tried it on 2 different computers, so I imagine it's something in the file. "Dave Peterson" wrote: Your original code worked fine for me (as I wrote before) using xl2003. What version of excel are you using? (If you said, I forgot <vbg.) Joyce wrote: Hi Dave, No, I don't have another macro that runs and changes the false to empty. On the check boxes that aren't linked, it works fine. It's the linked cell that seems to cause the problem. I've also checked the Triplestate property and it's still False. It's just as though the True/False has been deleted in the Value property. It seems to be when the sheet the linked cell resides on is protected it causes the problem. Just not sure why. "Dave Peterson" wrote: Any chance you have another macro (maybe an event macro) that runs and changes the false to empty. And I'd check that tiplestate property once more. It really sounds like it's not false. But you can do these as experiments--since you have a way that works. Joyce wrote: I've put the linked cells on a separate unprotected worksheet, which seems to do the trick. Not sure what the conflict is when it's on the same sheet, but this is workable solution. Again, thanks very much for your time and assistance. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
You've probably spent more time than you want on this--especially since you have
it working. But can you try it on a brand new workbook? If you don't want to, it's ok. Joyce wrote: I did mention it, but no worries :-) xl 2003. Tried it on 2 different computers, so I imagine it's something in the file. <<snipped -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
Hi Dave,
Yes, I had the same thought, so had tried it. But, had the same result. Strange how it would work for you, but not for me... "Dave Peterson" wrote: You've probably spent more time than you want on this--especially since you have it working. But can you try it on a brand new workbook? If you don't want to, it's ok. Joyce wrote: I did mention it, but no worries :-) xl 2003. Tried it on 2 different computers, so I imagine it's something in the file. <<snipped -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox / Linked Cell Problem
Well, thanks for trying.
(I don't have a guess why it worked for me, either.) Joyce wrote: Hi Dave, Yes, I had the same thought, so had tried it. But, had the same result. Strange how it would work for you, but not for me... "Dave Peterson" wrote: You've probably spent more time than you want on this--especially since you have it working. But can you try it on a brand new workbook? If you don't want to, it's ok. Joyce wrote: I did mention it, but no worries :-) xl 2003. Tried it on 2 different computers, so I imagine it's something in the file. <<snipped -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
font color with checkbox linked cel | Excel Discussion (Misc queries) | |||
Checkbox name linked to a cell | Excel Discussion (Misc queries) | |||
Problem when Combo box has linked cell as a number | Excel Programming | |||
checkbox linked to a cell | Excel Programming | |||
spinner linked cell problem | Excel Discussion (Misc queries) |