Non cumbersome way to hide and protect a column?

[Roland.Case]

Would love to simply hide a colum and use a password when "unhidden".
This is a worksheet wherein people enter data into it, but when
viewed, a column is hidden. Not that simple though.

Here is what I am doing now, but its cumbersome to say the least. Way
to automate or eimpligy this?

Undo protection to add data:
Open document, choose Tools, Protection, Unprotect, Enter password

Unhide hidden column:
F5 to open Go To box
Enter B2, OK
You wont see it but cursur in hidden cell
Choose from the top menu Format | Column | Unhide

Enter new data

Rehide column:
When ready to close choose Format, Column, Hide.

Reprotect document:
Tools, Protection, Protect Sheet, enter clear1, OK, then save document.

 

[Dave Peterson]

Record a macro when you do this.

In fact, record two macros--one to unhide and one to hide.

When you're done, go into the VBE and add this line at the top of the module:

Option Private Module


Then protect your code (with the password--that you'll have to add).
Tools|VBAProject Properties|Protection tab
Lock it and give it a memorable password.

Close the workbook and reopen it.
(You'll see that you can't see the code without the password if you test it.)

Then when you're ready to unhide your column:
alt-f8
(or tools|macro|macros)
type in the name of your macro

The "option private module" means that those macro names won't show up in the
dialog.

========
Be aware that this kind of worksheet protection and even project protection
isn't very secure. If your data really can't be seen by others, don't put it in
the workbook.

And if you have to put it in excel, don't share that workbook with others.

 

[Paul B]

Roland,

You could use a macro to do it, the UnHide macro will unprotect the sheet,
unhide column B and select cell B2, you could then enter your data and after
that run the hide macro to hide column B, protect the sheet and then select
another cell, A1 in this case.

But if you are always putting the data in B2 then try the Put_Data_In_B2
macro, it will bring up an input box to put you data in when you click Ok
the data will be put in B2. Or if you want to put the data in the next row
the next time try the Put_Data_In_ColumnB macro it will bring up and input
box and put the data in column B starting in B2 and then in the next row the
next time it is ran.

Will any of this work?


Const PW As String = "123" 'Change Password Here
Sub UnHide()
'will unprotect the sheet, unhide column B
'And select B2
ActiveSheet.Unprotect Password:=PW
Columns("B:B").EntireColumn.Hidden = False
Range("B2").Select
End Sub


Sub Hide()
'Will hide column B and protect the sheet
'And select A1

'just in case you run it when the sheet is protected
ActiveSheet.Unprotect Password:=PW

Columns("B:B").EntireColumn.Hidden = True
ActiveSheet.Protect Password:=PW
Range("A1").Select
End Sub


Sub Put_Data_In_B2()
'will put data in B2
ActiveSheet.Unprotect Password:=PW
Range("B2").Value = _
InputBox("Enter Your Data", "Enter Data")
ActiveSheet.Protect Password:=PW
End Sub


Sub Put_Data_In_ColumnB()
'will put data in column B
'Starting in B2 and then in the next row
ActiveSheet.Unprotect Password:=PW
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = _
InputBox("Enter Your Data", "Enter Data")
ActiveSheet.Protect Password:=PW
End Sub


To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is at the top-left of the spreadsheet this will
open the VBA editor, in Project Explorer click on your workbook name, if you
don't see it press CTRL + r to open the Project Explorer, then go to insert,
module, and paste the code in the window that opens on the right hand side,
press Alt and Q to close this window and go back to your workbook and press
alt and F8, this will bring up a box to pick the Macro from, click on the
Macro name to run it. If you are using excel 2000 or newer you may have to
change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003