Macros for checkboxes

M

Min

I'm stilll really struggling with my checkboxes!
How do I write a macro that will show columns on a worksheet when the box is
selected, and hide the columns again when it is deselected?
Many thanks to Dave who has already tried to help, but I need simple
instructions!
 
T

Toppers

I placed a checkbox from the FORMS toobar on a worksheet with C1 as a linked
cell: this shows TRUE/FALSE. It called the following macro in a general
module which hid/unhid columns OK.

Sub Macro1()
Columns("C:F").EntireColumn.Hidden = Range("C1")
End Sub


Does this help?
 
M

Min

I think you may have solved the problem for me - thank you!
I will now go and see if it will work on multiple sheets with a number of
checkboxes.
 
M

Min

Can I also add into the macro something that will make an option button true
when the checkbox is true, and vice versa?
 
B

Billy Liddel

Min

another way to hide an individual column.

Creat a list of the cols to hide, say on a new sheet. I used COls E to H

On the sheet you want to operate add a combo box, I linked it to A1. then I
assigned this macro.

Sub HideCol()
c = Range("A1") + 3
Columns(c).EntireColumn.Hidden = Not Columns(c).EntireColumn.Hidden
End Sub

Maybe this will help another time

Peter
 
T

Toppers

Link the Option Button to the same cell as the Checkbox .. no other macro
required. The Checkbox will set the state of the Option button.

HTH
 
M

Min

Yes, this works. Thank you. But...
I have 6 checkboxes that hide or unhide different columns. I also have an
option button to 'show all'. If the user selects all 6 checkboxes then I want
the option button to be true, otherwise it remains false.
Similarly, if the user selects the option button then I want all 6
checkboxes to be true. Is this possible?
 
T

Toppers

If you check all 6 Checkboxes, the option button isset to TRUE and if you
then uncheck one (or more) of the Check buttons, the Option button is set
off. If Option button selected, all Checkboxes are selected.

Is this OK?

C1:H1 are link cells .for Checkboxes ...

Sub Macro1()
' Check Checkboxes ....
If Application.CountIf(Range("C1:H1"), "TRUE") = 6 Then
Range("B1") = 1
Else
Range("B1") = 0
End If
End Sub

B1 is link cell for Option button

Sub Macro2()
' Check Option button ....
If Range("B1") = 1 Then
Range("C1:H1") = TRUE
End If


End Sub
 
M

Min

You are an absolute star! Thank you so much for all your help!
Hopefully now, I can get on and finish this project.....
 
M

Min

Sorry, I thought I understood this, but I can't seem to make it work.
What do I assign the 2 macros to?
 
M

Min

My apologies for being a bit dim - especially when you are being so helpful.
How do I assign macro1 to all 6 checkboxes that already have their own
macros assigned to them anyway?
I tried grouping them with a group box and assigning macro1 to the group
box, but that doesn't work very well.
 
T

Toppers

If they already have macros assigned you need to add the code I supplied to
check if all 6 are checked. Or call macro from your existing macros.Will
this work?


Sub Your_macro()

Call macro1

......Your code

End Sub


This is the problem of working "blind" as I obviously only have a partial
picture of what you are doing.
 
M

Min

Yes it does work - thank you. I didn't realise that you could 'call' a macro,
I thought they always had to be assigned to something

Thank you for all your help - you have been very patient!
Regards
Min
 
T

Toppers

Glad we got there!

Min said:
Yes it does work - thank you. I didn't realise that you could 'call' a macro,
I thought they always had to be assigned to something

Thank you for all your help - you have been very patient!
Regards
Min
Click to expand...
 
Top