Omit forms button functions from sheet protection

[Bob Hustead]

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

 

[HalfAce]

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]

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

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]

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.

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]

Thank you, I'll post a report.

 

[Bob Hustead]

Worked like a champ, thank you!

 

[Bob Hustead]

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]

See if "Select Unlocked Cells" is checked in the protection options.

You may have to add this:

ActiveSheet.EnableSelection = xlNoRestrictions

HTH,

Smitty

 

[Bob Hustead]

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]

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