check boxes

[Min]

I have a workbook with a dozen worksheets. On each worksheet there are 6
checkboxes that I have made to hide or unhide different columns. The
checkboxes and the vb for them is exactly the same on each worksheet. Can I
have just one lot of code somewhere that will work on all the sheets, or have
I got to have the code written on each individual sheet, as I have at present?

 

[Dave Peterson]

I think you have a couple of choices...

1. Use checkboxes from the Forms toolbar and assign the same macro to each. In
fact, depending on what you're doing, you could use the checkbox's name and
maybe assign the same macro to all 6 checkboxes on each of the dozens of sheets.

2. If you're using checkboxes from the Control Toolbox toolbar, you may be able
to just use a single common subroutine in a General module--where each
checkbox's click event calls the common routine (passing the nice info to the
common routine).

3. Create a class module that "groups" all your optionbuttons (from the Control
toolbox toolbar) so that you can use a common procedure.

I'm gonna use the last one and it's based on the code on John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip44.htm

I created a new class module (called Class1) with this in it:

Option Explicit
Public WithEvents ChkBoxGroup As MSForms.CheckBox
Private Sub ChkBoxGroup_Click()

Dim mySFX As Long
Dim myAddresses() As Variant

myAddresses = Array("A1:b1", "e1:f1", "i1")

With ChkBoxGroup
If IsNumeric(Right(.Name, 1)) Then
mySFX = Right(.Name, 1)
Else
mySFX = 0
End If

Select Case mySFX
Case Is = 0
'do nothing!
Case 1 To 3 '3 addresses
.Parent.Range(myAddresses(mySFX - 1)).EntireColumn.Hidden _
= CBool(.Value = True)
End Select
End With

End Sub


The I added this to a general module:

Option Explicit
Dim ChkBoxes() As New Class1
Sub Auto_open()
Dim ChkBoxCtr As Long
Dim OLEObj As OLEObject
Dim wks As Worksheet

ChkBoxCtr = 0
For Each wks In ThisWorkbook.Worksheets
For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
ChkBoxCtr = ChkBoxCtr + 1
ReDim Preserve ChkBoxes(1 To ChkBoxCtr)
Set ChkBoxes(ChkBoxCtr).ChkBoxGroup = OLEObj.Object
End If
Next OLEObj
Next wks

End Sub


You have to make sure that the checkboxes are named nicely. Each sheet has to
have the checkboxes that end in a digit (1-6). I used checkbox1, checkbox2,
checkbox3 (I got lazy!).

This line:
myAddresses = Array("A1:b1", "e1:f1", "i1")
corresponded to those 3 checkboxes.

Checkbox1 controlled a:b
checkbox2 controlled e:f
checkbox3 controlled i (a single column)

 

[Min]

Thanks for your help. I will try it tomorrow and see how I get on. I'll
probably be back!
Min

 

[Min]

How do I write a macro that will show some columns when the checkbox is
selected and hide them again when it is deselected?
I have tried this, but it doesn't work...

Sub Macro1()
If CheckBox1 = False Then
Columns("K:U").Select
Selection.EntireColumn.Hidden = True
Range("A2").Select
OptionButton1 = False 'Show all
ActiveWindow.ScrollColumn = 1

Else
Columns("K:U").Select
Selection.EntireColumn.Hidden = False
ActiveWindow.ScrollColumn = 11
Range("K2").Select
End If

End Sub

What am I doing wrong??

Min

 

[Dave Peterson]

You could use something like:

if me.checkbox1.value = true then
me.range("K:U").entirecolumn.hidden = true
else
me.range("K:U").entirecolumn.hidden = false
end if

Or you could just use:

me.range("K:U").entirecolumn.hidden = me.checkbox1.value

I used:

.Parent.Range(myAddresses(mySFX - 1)).EntireColumn.Hidden _
= CBool(.Value = True)

I kind of like this style (personal preference only).