check box for hiding columns

[rodolform]

I´ll like to have a checbox that if marked will hide columns J and K
can somebody help me with the code???

Thanks.

Rodolf

 

[Peter Atherton]

Rodolfo

Sub Hidecols()
Dim r As Range
Set r = Range("J1:K1")
r.EntireColumn.Hidden = Not r.EntireColumn.Hidden
End Sub

Attach the macro to a command button or checkbox after the
macro is pasted into a module.

Regards
Peter

 

[rodolform]

Peter
I'm getting a "Syntax error" in the 3rd. line in 3D

Don´'t know what is wrong.

Thanks for your help

Rodolf

 

[Dave Peterson]

Peter's code worked ok for me when I copied and pasted into my project.

Any chance extra characters got pasted into the project.

 

[anurag.d]

Even i got a syntax error in the macro.
Can anyone tell me how to attach a command button or check box to th
macro.

Thanks

 

[rodolform]

I have copied again, and still got the sintax error...

Could 3d be the problem??? what does it stand for???

Thanks...

 

[Rutgers_Excels]

I was having the same problem (syntax error). When I deleted the "3D
from the code it worked perfectly.

Try that out

 

[rodolform]

Thank you....

That did it.... the code works now.

How can I attach this macro to the checkbox????

Thank's for your quick response....

Rodolf

 

[Rutgers_Excels]

Right Click on the checkbox and assign macro if you are using a checkbo
from the Forms Menu

 

[rodolform]

Thank you Rutgers:

When I right click the chekbox I can't find no option to assign the
macro, could you be more specific??

Thank's for your help.

Rodolfo

 

[Rutgers_Excels]

You must be using a check box from the control toolbox. In this case
you want to make sure that you are in design mode (ie the design mod
icon on your control toolbox toolbar is selected). In design mode
right click on your checkbox and view code. Copy and paste this ove
the existing code and it should work fine.

Private Sub CheckBox1_Click()
Dim r As Range
Set r = Range("J1:K1")
r.EntireColumn.Hidden = Not r.EntireColumn.Hidden
End Sub

Note: Make sure the name of your checkbox is CheckBox1. If not, yo
will need to edit the name of the checkbox in the above code t
correspond with the name of your checkbox.

Hope you figure it out

 

[rodolform]

Thank you.....

It worked fine.


Rodolf

 

[rodolform]

I've found this aplication to be very usefull to keep my Worksheets ver
"clean".

Now I want to do the same (be shown or hiden when clicked) but fo
rows, lets say rows 4 to 10, what do I have to change in the code t
make this happen??.

I'll place the checkbox in A1, when clicked, I want cell A3 to b
selected and then rows 4 to 10 be shown or hiden.

Thank's again for all your help.

Rodolf

 

[Rutgers_Excels]

It's pretty much the same code. The only change will be the rang
(obviously) and what you are hiding. Last time you wanted to hide th
"EntireColumn", this time you want to hide the "EntireRow". You als
want A3 to be selected after they are hidden/unhidden. What you nee
to do is call up the cell and select it (ie Range("A3").Select).


Private Sub CheckBox1_Click()
Dim r As Range
Set r = Range("A4:A10")
r.EntireRow.Hidden = Not r.EntireRow.Hidden
Range("A3").Select
End Su

 

[rodolform]

Thank you for your help.....

It worked all right.

I been thinking of the convinience of learning VBA, do you know a plac
on the Internet that could help me to???


Regards.

Rodolf

 

[Rutgers_Excels]

To be honest with you, I've only started working in VBA a little over
month ago. I never took a class and I never read a book on it. Th
higher-ups at my job want me to build a database but they don't want t
show me how. I thought it would be worth while to try and figure ou
how to write code in VBA. This forum is the best place I can find fo
useful information. I also heard that books like "VBA for Dummies
are very helpful for beginners. I'm just too cheap to dish out the $5
or so

 

[rodolform]

You have been very helpfull,.

I'll take the same aproach as you did and begin to learn VBA by myself.


Thanks again for your help.

Rodolfo

 

[Berend Botje]

The script can be made shorter:

Private Sub CheckBox1_Click()
if checkbox1.value = true then
Range("A4:A10").EntireRow.Hidden = true
Else
Range("A4:A10").EntireRow.Hidden = false
End Sub

Also this should be more comprehensible for somebody that just starte
working with VBA

 

[Berend Botje]

Sorry, forgot the "end if" command.

Private Sub CheckBox1_Click()
if checkbox1.value = true then
Range("A4:A10").EntireRow.Hidden = true
Else
Range("A4:A10").EntireRow.Hidden = false
End if
End Su