Omit forms button functions from sheet protection

Bob Hustead

New Member
Joined
Jul 20, 2007
Messages
10
Hello,

Earlier today I posted a question, but thought I might not have described the situation that well. I would be glad to post or email my spreadsheet if it would help in answering the question.

By the way, you guys really helped me out with some forms button issues a couple of years ago and I am still making good use of the buttons and macros!

I have forms buttons with macros that will not function if I protect the worksheet, even if I only protect contents. In reading through the help files that came with Excel, it sounds like the problem might be that the macros hide or expose cells. sheet protection seems to prohibit this.

I would really appreciate any help with this.

Thank you,

Bob Hustead
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hello Bob Hustead,
Probably the most common way to solve this would be to have your macro code unprotect
the sheet at the beginning of the code, perform whatever it's designed to do and then
re-protect the sheet at the end of the code.
Certainly there are some cases where this may not be desirable (and those can usually be
solved too, depending on what all is being done in the code and other factors in the sheet, etc.)
but in most cases this is all that needs to be done.

Does that sound like it'll suit your needs? If not, can you post your code and the details about
your sheet that make it undesirable? Someone will be able to help.
 

Bob Hustead

New Member
Joined
Jul 20, 2007
Messages
10
HalfAce,

Thanks for the reply!

I have 1 macro to toggle rows and several for toggling columns. I will paste the togglerows macro and 1 of those used for columns, in that they are all similar.

It seems like what you are suggesting would be the answer. I have no idea what the text would be. If you wouldn't mind pasting into the macros it will be a great help :biggrin:

Sub togglerows()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Columns(7))
For Each cell In rng
If cell.Text = "0.00" Or cell.Text = "" Then
cell.EntireRow.Hidden = Not cell.EntireRow.Hidden
Else
cell.EntireRow.Hidden = False
End If
Next
Application.ScreenUpdating = True
End Sub


Sub ToggleHideB()
With ActiveSheet.Buttons(2)
If .Caption = "Make C thru F visible" Then
Range("C:F").EntireColumn.Hidden = False
.Caption = "Hide C thru F"
Else
Range("C:F").EntireColumn.Hidden = False
Range("C:F").EntireColumn.Hidden = True
.Caption = "Make C thru F visible"
End If
End With
End Sub

It is amazing how long some of us can go around in circles trying to figure some of these things out and how quickly some of you guys can solve them. Some of these things are a big help on a day to day basis.

Thanks again!
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Untested (sorry, work rearing its ugly head) but try these. They should work.
The only thing to keep in mind is your password if you use one.
Code:
Sub ToggleHideB()
ActiveSheet.Unprotect "YourPassword(IfAny)GoesHere"
With ActiveSheet.Buttons(2)
  If .Caption = "Make C thru F visible" Then
    Range("C:F").EntireColumn.Hidden = False
    .Caption = "Hide C thru F"
  Else
    Range("C:F").EntireColumn.Hidden = False
    Range("C:F").EntireColumn.Hidden = True
    .Caption = "Make C thru F visible"
  End If
End With
ActiveSheet.Protect "YourPassword(IfAny)GoesHere"
End Sub


Sub togglerows()
Dim rng As Range
Dim cell As Range
Application.ScreenUpdating = False
ActiveSheet.Unprotect "YourPassword(IfAny)GoesHere"
Set rng = Intersect(ActiveSheet.UsedRange, Columns(7))
  For Each cell In rng
  If cell.Text = "0.00" Or cell.Text = "" Then
    cell.EntireRow.Hidden = Not cell.EntireRow.Hidden
  Else
    cell.EntireRow.Hidden = False
  End If
Next
ActiveSheet.Protect "YourPassword(IfAny)GoesHere"
Application.ScreenUpdating = True
End Sub

Hope it helps.
 

Bob Hustead

New Member
Joined
Jul 20, 2007
Messages
10
Hello HalfAce,

I have discovered something odd.

I have certain cells locked and others unlocked. The code you suggested works fine. However, for some reason I cannot highlight or insert comments into the unlocked cells.

I was wondering if you might have some ideas for overcoming this?

Thank you,

Bob Hustead
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
See if "Select Unlocked Cells" is checked in the protection options.

You may have to add this:

Code:
ActiveSheet.EnableSelection = xlNoRestrictions

HTH,

Smitty
 

Bob Hustead

New Member
Joined
Jul 20, 2007
Messages
10
Thank you,

I am sorry to say this gives rise to other questions.

Where would I insert this? Would it be in the workbook code, or the code for the sheet in question?

Also, is that an x and a numeral 1 in front of NoRestrictions?

Under sheet protection, I do not have "Select Unlocked Cells" as an option. Only, "Contents" "Objects" and "Scenarios".

Thank you again for your help!
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
What version of Excel do you have?

In 2002 & later you have expanded protection options. That code won't do you any good if you have an older version. As for what's in front of "NoRestrictions" it's an x and an l, not a 1. Think "Excel" abbreviated. ;)

Offhand I can't think of what would prevent you from interacting with unlocked cells if you don't have the expanded protection options.

Smitty
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

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
Top