Hide unhide column with clickbox and password [Solved/Closed]

Stormdronk 3 Posts Thursday August 18, 2011Registration date August 19, 2011 Last seen - Aug 18, 2011 at 10:05 AM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Aug 19, 2011 at 05:47 AM
Hello, All

Please can you help, is there a way that you can hide/unhide colmns with a click box, I have the code to group ungroup when the sheet are protected, but the I have hiden colmns that I only want some users to access.

Thanks for everyone giving sparetime to help on this forum!!!



See more 

5 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 18, 2011 at 06:01 PM
0
Thank you
and where is this box ?
Stormdronk 3 Posts Thursday August 18, 2011Registration date August 19, 2011 Last seen - Aug 18, 2011 at 07:13 PM
0
Thank you
Hi,

The box is in the corner of my sheet"Cost Analasys". In this sheet E:D are hiden. In the click box there are code that hide unhide the colmns when the sheet is protected.

I want to know if you can ad a password to the box when you click on it, enter the password and then only the macro runs.

Code in box:

Sub hide()
ActiveSheet.Unprotect
If Columns("D:G").EntireColumn.Hidden = False Then
Columns("D:G").EntireColumn.Hidden = True
ElseIf Columns("D:G").EntireColumn.Hidden = True Then
Columns("D:G").EntireColumn.Hidden = False
End If
ActiveSheet.Protect
End Sub

Thanks
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 18, 2011 at 09:44 PM
The only way I can think off ( does not mean that there might not be other way) is that
1. you protect sheet
2. disable allow hide/show column in protection
3. on check of box, run the code to verify that password is a good one.
4. If the password is good, disable the protection, and make column visible
5 if the password is bad, keep the protection and keep the column hidden
Stormdronk 3 Posts Thursday August 18, 2011Registration date August 19, 2011 Last seen - Aug 19, 2011 at 03:51 AM
0
Thank you
Hi, solved the problem,

A check box from the Forms controls is used and is linked to cell A1.
The check box returns a True or False value which can be tested and, as applicable, then display a message box prompting for a password.

Sub Show_Columns() 
      
    Dim strPassword As String 
      
    Select Case Range("A1").Value 
          
    Case True 
        strPassword = Application.InputBox("Enter the password", Type:=2) 
        If strPassword = "MyPassWord" Then 
            Range("G1:J1").EntireColumn.Hidden = False 
        Else 
            MsgBox ("Invalid Password"), vbCritical 
            Range("A1").Value = False 
        End If 
          
    Case False 
        Range("E1:M1").EntireColumn.Hidden = True 
          
    End Select 
      
End Sub 
When the password prompt appears enter: MyPassWord

Works grate!!!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 19, 2011 at 05:47 AM
But I think if I select the cells, I can choose to say unhide column and there would not be any need for any password., It may not be important in this case or it may be. Just thought that to share with you,