#1   Report Post  
Posted to microsoft.public.excel.misc
Min Min is offline
external usenet poster
 
Posts: 40
Default check boxes

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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default check boxes

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 wrote:

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
Min Min is offline
external usenet poster
 
Posts: 40
Default check boxes

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

"Dave Peterson" wrote:

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 wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Min Min is offline
external usenet poster
 
Posts: 40
Default check boxes

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" wrote:

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 wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default check boxes

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).



Min wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check boxes MACAVITY500 Excel Discussion (Misc queries) 5 November 18th 06 01:47 PM
How do I increase the size of check in check boxes Adams, Les Excel Discussion (Misc queries) 0 September 19th 06 02:35 PM
Enable check box in protected sheet + group check boxes Dexxterr Excel Discussion (Misc queries) 4 August 2nd 06 12:00 PM
How do i create a value for check boxes or option boxes Tim wr Excel Discussion (Misc queries) 1 February 9th 06 10:29 PM
Check Boxes vknight Excel Discussion (Misc queries) 1 June 20th 05 11:13 AM


All times are GMT +1. The time now is 03:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"