Uncheck All Checkboxes Before Checking Current Checkbox [SOLVED]

[dparteka]

I have many control toolbar checkboxes on a spreadsheet but need for only one to be checked at any given time. I’d like to be able to click any single checkbox and have it uncheck all checkboxes before actually checking the one I’m currently clicking on. I'm think that adding code to the checkbox would be a good way to go, any ideas out there?

 

[Sam Mathai Chacko]

When you say control toolbar checkboxes, do you mean form controls, or activex controls?


If they are form controls, you can assign the following macro to all of them

[pre]

Sub UncheckAllCheckBoxesInActiveSheet()

Dim chb As CheckBox

For Each chb In ActiveSheet.CheckBoxes
If chb.Name <> Application.Caller Then
chb.Value = 0
End If
Next chb

End Sub

[/pre]

 

[Smallman]

Hi dparteka


To add to Sam's solution if the checkboxes you are using are Active X check boxes then you can use the following.

[pre]

Option Explicit
Private Sub Workbook_Open()
Dim oObj As OLEObject
On Error Resume Next

For Each oObj In ActiveSheet.OLEObjects
If TypeName(oObj.Object) = "CheckBox" Then oObj.Object.Value = 0
Next
ActiveSheet.CheckBoxes = False
End Sub[/pre]
I answered a very similar question on another forum some time ago and you can pick up that post here with a file to show workings if that helps.


http://www.ozgrid.com/forum/showthread.php?t=167944


Hope this helps.


Take care


Smallman


ps this post is for the moderators - putting code between your code

does not produce the desired result. I wish my code to look as it does in the link. I want it to look like this every day of the week. Putting code between 'my code' does not produce an appropriate result either. I have tried every permutation I can think of adding

ol within the ` tags. I went to the link Hui provided and I am getting closer - thanks mate.


That If statement above should be indented. I am sorry to use this post as my conduit but I can not work this out after 45 minutes of testing see below. I will probably lay down another 2-300 posts before the new forum is up and I want my historical coding to look sharp. Can someone please email me.


Marcusinlondon1 AT Yahoo.com


Cool cool. Take it easy


Smallman


EDITED


Done, deleted code as requested. SirJB7.

 

[dparteka]

Thanks guys... greatly appreciated

 

[SirJB7]

@Smallman

Hi!

Just sent an email.

Regards!

 

[Smallman]

Got it thanks will reply in Kind. Can you use your administrative rights to clean the code above. Everything from MyTesting zone would be great.