Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non cumbersome way to hide and protect a column?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non cumbersome way to hide and protect a column?
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. wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Non cumbersome way to hide and protect a column?
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 wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HIDE AND PROTECT A CELL | Excel Worksheet Functions | |||
nested IF AND too cumbersome | Excel Worksheet Functions | |||
How do you permanently hide and password protect a column? | Excel Discussion (Misc queries) | |||
hide a column in a worksheet and password protect | Excel Discussion (Misc queries) | |||
Protect hide worksheet | Excel Worksheet Functions |