Button to unlock & unhide all columns

T

The doomed

Active Member
Joined
Feb 13, 2008
Messages
263
I have a sheet that upon opening a macro runs to hide a selection of columns based on cell containing certain words (see here).

I now also require the workbook to be protected so that the hidden columns (which Im sure are locked as default when hidden) are locked as are columns N & S. Now, I know I can do this by protecting the sheet and unprotecting the columns I require (ie N & S - with the hidden cells remaining locked anyway).

The issue is, every time the workbook is opened this protection must be in place.

However, I require a button, which could simply reside in A1, that when pressed, will unhide and unlocked all cells. - This is needed as its vital I can easily copy rows to another sheet (and delete from existing sheet) as and when required.

if possible, re-pressing the button would hide and proetct the worksheet however this is not too significant as long as when I close and open the workbook everything is hidden and protected as specified above.

There is no need to password protect anything.


Ive had a good search of the forums and have little bits of info from varuious threads. Any help would, as ever, be much appreciated. :p


Oh and the sheet in question is called 'Open Projects'.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi The Doomed,

Will something like this work for you?

Code: 
If Columns("N").Hidden = True And Columns("S").Hidden = True Then
    ActiveSheet.Unprotect
        Columns("N").Hidden = False
        Columns("S").Hidden = False
            Else
                ActiveSheet.Protect
                Columns("N").Hidden = True
                Columns("S").Hidden = True
End If
 
Upvote 0
Hi Schwarzmanne,

I dont think that will work as N&S will never be hidden - sorry perhaps that wasnt clear (or maybe Ive misinterpreted your code)

Thanks,
Td
 
Upvote 0
Hi,

Which columns are you trying to unhide? If any.

This will protect and unprotect as I believe you require;

Code: 
If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect
        Else
            ActiveSheet.Protect
End If

If you need colums hidden/umhidden you can embed them into this code.

Am I missing the point alltogether?
 
Upvote 0
Hi Again,

To ensure that the protection is in place use upon open place this in your This Workbook section in VBA;

Code: 
Private Sub Workbook_Open()

Sheets("Open Projects").Protect

'Embed your other macro to hide columns based on criteria here

End Sub
 
Upvote 0
Schwarzmanne said:
Hi,

Which columns are you trying to unhide? If any.

This will protect and unprotect as I believe you require;

Code: 
If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect
        Else
            ActiveSheet.Protect
End If

If you need colums hidden/umhidden you can embed them into this code.

Am I missing the point alltogether?
Click to expand...

There are around 20 columns hidden by a macro when the sheet opens. It is the same columns the are hidden every time. Okay Ill add in the workbook_open : protect code that you've outlined.

it is these columns I need to unhide (In need to unhide all hidden coulmns)when pressing a button, as well as unlocking all the entire sheet to allow me to copy and delete selected rows.

Looking at the code posted by cornflakegirl:
Code: 
Sub test()
ActiveSheet.Unprotect
Columns.Hidden = False
End Sub

this appears as if it may be suitable. How do I create a button for this? Id be looking for it in cell A1.


Hope this is making sense
 
Upvote 0
In reply to my own post, the code from cornflakegirl seems to do what Im looking for - as far as unhidding everything is concerned, ive yet to try the protection aspects.

I have the smiley face on the command bar, how can I change this to a button that I can have in the spreadsheet so that all users can see it?
 
Upvote 0
The doomed said:
In reply to my own post, the code from cornflakegirl seems to do what Im looking for - as far as unhidding everything is concerned, ive yet to try the protection aspects.

I have the smiley face on the command bar, how can I change this to a button that I can have in the spreadsheet so that all users can see it?
Click to expand...

Ignore that. Got an icon in use now on the sheet. Thanks!

now to resolve the issues with the protection...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Hide Columns and password protect without locking cell
Replies
1
Views
398
Sergio
S
B
  • Question
Unhide worksheets with password in userform
Replies
2
Views
374
bptaw
B
D
  • Question
Macro will protect worksheet, but will not unprotect
Replies
3
Views
566
rlv01
R
A
  • Question
Excel table protection with formula copying
Replies
2
Views
553
Allie_Excel
A
M
  • Question
Protecting Sheet (1 password) | Protecting Cells Within Same Sheet (2nd Password)
Replies
7
Views
817
Skyybot
Skyybot

Forum statistics

Threads
1,215,045
Messages
6,122,836
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top