Changes & creation of Code for hiding & unhiding columns & Sheet

[paradise]

Hi,
This is the first time,I am posting my questions here.

Since I am a layman in VBA,hence I would prefer to get a clean code.As enclosed in workbook,in Sheet2,I have 6 check boxes.In 2 check boxes I have VBA code.In this 2 check box can you modify the code being in the case of checked case & in the case of unchecked case.

Secondly,in other 4 check boxes,I would like to get the code separately for each in which there has been clearly mentioned the full information in check boxes what to do ?

With Best Rgds,
Suresh

[p45cal]

paradise, you have cross posted at at least one other site (http://www.excelforum.com/excel-programming-vba-macros/959451-changes-and-creation-of-code-for-hiding-and-unhiding-columns-and-sheet.html), could you provide a link at that and any other sites to here for the reasons given in the following light reading:
http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters



Private Sub CheckBox2_Click()
Range("A:A,E:E,P:P").EntireColumn.Hidden = CheckBox2.Value
End Sub
Private Sub CheckBox3_Click()
Sheets("sp").Range("B:B,D:D,I:I").EntireColumn.Hidden = CheckBox3.Value
End Sub
Private Sub CheckBox4_Click()
Sheets("sp").Visible = Not CheckBox4.Value
End Sub
Private Sub CheckBox5_Click()
For Each sht In ThisWorkbook.Sheets
If Not sht Is Me Then sht.Visible = CheckBox5.Value
Next sht
End Sub
Private Sub sprhide_Click()
Columns("D:K").EntireColumn.Hidden = sprhide.Value
End Sub

[paradise]

Dear Sir,

Thanks for the reply.All it works.

The forum where I have posted was at excelforum dot com and chandoo.org where I marked there solved.When I mentioned the website name,this forum does not allow as it has asked that until 5 posts it won't allow to post other website name.

Hence,extremely sorry for it.

Wth Best Rgds,
Suresh

[paradise]

Dear Sir,

Can you help me to get the code for hiding rows using the check boxes.

Check box 1 >>> hiding rows A1 of Sheet2
Check box 2 >>> hiding rows A1:A5 of Sheet2
Check box 3 >>> hiding rows D7 : D9 of Sheet2
Check box 4 >>> hiding rows E7,F6,I10 of Sheet2
.....................................................................
Check box 5 >>> hiding rows A1 of Sheet"sp"
Check box 6 >>> hiding rows A1:A5 of Sheet"sp"
Check box 7 >>> hiding rows D7 : D9 of Sheet"sp"
Check box 8 >>> hiding rows E7,F6,I10 of Sheet"sp"

Hope my this problem also might get solved.This hiding rows thread I haven't posted in any thread.

With Best Rgds,
Suresh

[p45cal]

In your workbook, you have checkboxes as follows:
sprhide with a caption which includes 'Check Box:2'
CheckBox2 with a caption which includes 'Check Box:3'
CheckBox3 with a caption which includes 'Check Box:4'
CheckBox4 with a caption which includes 'Check Box:5'
CheckBox5 with a caption which includes 'Check Box:6'

So it's a bit confusing.
Are any of the 8 checkboxes in your last post the same checkboxes that already exist? if so, which is which (please use the codenames of the checkboxes (eg.sprhide) rather than what appears on their captions, and do you want to combine the hiding of columns and rows, or change them to hide only rows?
Or are these 8 checkboxes a set of 8 completely new and additional checkboxes?

Also, re:
hiding rows E7,F6,I10 of Sheet"sp"you've specified column letters, I take it that I can ignore these and hide complete rows 7,6 and 10?

[paradise]

Dear Sir,

Thanks for your prompt reply.It is now assumed that the 8 check boxes are completely new.Kindly ignore the column hide check boxes that in the above enclosed workbook as there are not 8 check boxes.Taking this into account,kindly formulate complete code for hiding rows only.I do not know whether Column name A,D,E,F,I is required or not.As per you post,I think column name A,D,E,F,I is not important only rows name is important.You can do it accordingly.Other sheet name is "sp",kindly take this sheet name.

With Best Rgds,
Suresh

P.S : In addition to above,I would very much happy if format of merged column & row hidding vba code is too supplied

[p45cal]

I've run out of time for today, I'll try to respond within 20 hours from now.

[p45cal]

try:
Private Sub CheckBox1_Click()
Rows(1).Hidden = CheckBox1
End Sub

Private Sub CheckBox2_Click()
Range("1:5").EntireRow.Hidden = CheckBox2
End Sub

Private Sub CheckBox3_Click()
Range("7:9").EntireRow.Hidden = CheckBox3
End Sub

Private Sub CheckBox4_Click()
Range("6,7,10").EntireRow.Hidden = CheckBox3
End Sub

Private Sub CheckBox5_Click()
Sheets("sp").Rows(1).Hidden = CheckBox5
End Sub

Private Sub CheckBox6_Click()
Sheets("sp").Range("1:5").EntireRow.Hidden = CheckBox6
End Sub

Private Sub CheckBox7_Click()
Sheets("sp").Range("7:9").EntireRow.Hidden = CheckBox7
End Sub

Private Sub CheckBox8_Click()
Sheets("sp").Range("6,7,10").EntireRow.Hidden = CheckBox8
End Sub



P.S : In addition to above,I would very much happy if format of merged column & row hidding vba code is too supplied
Which columns with which rows?

[paradise]

Hi,

Thanks sir.You may reply whenever you are free there.
I have used your formula in one of my workbook for hiding rows.Since there is formula in different cells, I have applied cells protection thru Review>Protect Sheet.I did not like someone make changes to the formula in my worksheet.Only those cells are left unprotected that are allowed to change.When I use in this condition row hiding formula,I get an error like this :

Run-time error '1004':
Unable to set the Hidden property of the Range Class

The formula applied is as follows :

Private Sub CheckBox1_Click()
Range("34:61").EntireRow.Hidden = CheckBox1
End Sub

secondly,Sheet"sp"-I7 should be hidden i.e Sheet!I7 (Column I & Row 7) in case of single row & column I & Sheet!I8:J10 in case of multiple.

With Best Rgds,
Suresh

[p45cal]

1. I made a mistakes with checkbox4;
Range("6,7,10").EntireRow.Hidden = CheckBox3
should be:
Range("A6,A7,A10").EntireRow.Hidden = CheckBox4

Similar with checkbox8.

2. Regarding protection, you need to have a line such as:
Sheets("Sheet2").Protect userinterfaceonly:=True
which needs to run just once each time the workbook is opened (it doesn't matter if it runs more than once), so you could have it in the workbok_open event in the Thisworkbook code-module.

3. Untested, two lines:
Sheets("sp").range("I7").entirerow.Hidden = checkboxN
Sheets("sp").range("I7").entirecolumn.Hidden = checkboxN

or where multiple rows and columns are involved:
Sheets("sp").range("I8:J10").entirerow.Hidden = checkboxN
Sheets("sp").range("I8:J10").entirecolumn.Hidden = checkboxN


Obviously, adjust N to match the checkbox involved.

[paradise]

Dear Sir,
Thanks for the reply.Still regarding as pointed out by you,
"2. Regarding protection, you need to have a line such as:
Sheets("Sheet2").Protect userinterfaceonly:=True
which needs to run just once each time the workbook is opened (it doesn't matter if it runs more than once), so you could have it in the workbok_open event in the Thisworkbook code-module."

I applied the formula for above SN.2 as said but still did not work.It first asks to unprotect sheet by asking password.After entering the password,the checkbox works.Without entering password it won't works & shows error same as before i.e-
Run-time error '1004':
Unable to set the Hidden property of the Range Class

I want to get operate checkbox without entering the password. Is it possible or not ? If so do let me know.I have enclosed a sample workbook for this to your kind reference.The sheet2 protection password is "password".

Last but not least,

I have currently used for hiding sheet in some other workbook not enclosed here by following formula,

Sub HideSheets()
Dim sh As Worksheet
Dim party As Variant
Dim ext As Variant

For Each sh In Worksheets
party = Worksheets("Input").Range("C2").Value
If sh.Name = "Input" Or sh.Name = "M" + ext Or sh.Name = "List" Or sh.Name = "Output" Then
sh.Visible = True
Else
sh.Visible = False
End If
Next

End Sub

It is working fine.How this code can be modified using check box to be very hidden that cannot be even shown by right click>>unhide way.

Hope my these queries might be enough at the moment.After surfing a lot of web,this website I found very useful for VBA for excel.

Heaps of thanks for your consistent help for giving me time for learning the vba & helping in my daily life to solve the different kinds of problems which I face at the moment.Also,I seek your help in near future too whenever I face problem.

With Best Rgds,
Suresh

[p45cal]

see attached regarding the password and to make a sheet very hidden, change
sh.Visible = False
to
sh.Visible = 2

[paradise]

Dear Sir,

Thanks for your prompt reply.I have seen files as enclosed by you.You have mentioned the password in the code

Sheets("Sheet2").Protect Password:="password", userinterfaceonly:=True

This password anyone can see in the code.Whether this can this be prevented or not ?

Also,you did not replied how sheet/s can be hidden/unhidden by check box say Check Box 1 which currently I was doing with code i.e using macro as mentioned earlier.

Hope this would be suffice at the moment.

As usual,I would be eagerly waiting for your reply. And after receiving these replies, I will mark it solved.


With Best Rgds,

Suresh

[p45cal]

In the project Explorer in the vbe (Ctrl+R if you can't see it), right-click the project concerned and choose nnnnnnnn properties… where nnnnnnn is the name of the vba project, then choose the Protection tab, the rest should be self-explanatory. Not rock solid as far as security is concerned, but fairly OK for the casual user.


Private Sub CheckBox9_Click()
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name = "Input" Or sh.Name = "M" Or sh.Name = "List" Or sh.Name = "Output" Then
If CheckBox9 Then sh.Visible = 2 Else sh.Visible = True
End If
Next
End Sub

[paradise]

Dear Sir,

For the first part,it ok.With Regard to second part, the code you provided did not worked.I have enclosed the file for your kind reference.It has hide both sheet "Output" & "Input".Other sheets are remain unhidden.Since I made the check box in sheet "Input",it itself has got hidden.Becoz, as per my previous code it has hide all the sheets except "Ouput" & "Input" sheet.Now the code you provided did just reverse.

Why I prefer check box, is that by checking & unchecking check box,I can hide all the sheets except "Input" sheet & "Ouput" sheet and without unchecking check box ,it show all the sheets.

With Best Rgds,

Suresh

[p45cal]

If sh.Name <> "Input" And sh.Name <> "M" And sh.Name <> "List" And sh.Name <> "Output" Then
If CheckBox9 Then sh.Visible = 2 Else sh.Visible = True
End If

[paradise]

Dear Sir,
I have applied the code as suggested by you which is as follows :

Private Sub CheckBox9_Click()
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name <> "Input" And sh.Name <> "M" And sh.Name <> "List" And sh.Name <> "Output" Then
If CheckBox9 Then sh.Visible = 2 Else sh.Visible = True
End If
Next
End Sub

And it did work.Thanks again.Can you explain the statement that is highlighted in blue.

With Best Rgds,
Suresh

[p45cal]

sh is each sheet in the workbook taken one at a time.
The four conditions in the first IF are:

sh.Name <> "Input"
sh.Name <> "M"
sh.Name <> "List"
sh.Name <> "Output"

ALL of which have to be True (because of the ANDs) in order for the whole statement to be True and for the second IF to be executed.

So taking two sheets; first, one named "sp", the four statements become:

"sp" <> "Input" (TRUE (because "sp" is not equal to "Input"))
"sp" <> "M" (TRUE)
"sp" <> "List" (TRUE)
"sp" <> "Output" (TRUE)

so that statement:
If sh.Name <> "Input" And sh.Name <> "M" And sh.Name <> "List" And sh.Name <> "Output" Then
becomes:
If TRUE And TRUE And TRUE And TRUE Then
which is clearly all TRUE, so the second IF is executed.


taking another sheet, the sheet named "Output", the four statements become:

"Output" <> "Input" (TRUE)
"Output" <> "M" (TRUE)
"Output" <> "List" (TRUE)
"Output" <> "Output" (FALSE (because "Output" is equal to "Output"))

so that statement:
If sh.Name <> "Input" And sh.Name <> "M" And sh.Name <> "List" And sh.Name <> "Output" Then
becomes:
If TRUE And TRUE And TRUE And FALSE Then
which now is clearly FALSE, so the second IF is not executed.

As a result of the above, the inner IF statement is only ever executed if the sheet's name is not one of the 4 names listed, and says:
If CheckBox9 Then sh.Visible = 2 Else sh.Visible = True
If CheckBox9 is ticked Then sh.Visible = 2 (xlVeryhHidden) Else sh.Visible = True
which will make sh visible or very hidden, depending on the tick being present in the checkbox or not.

[paradise]

Dear Sir,

Thanks.I learned a lot from you.Also,I have already marked this thread as solved.

With Best Rgds,
Suresh