Need help with "protecting" a cell without sheet protection

klarowe

Active Member
Joined
Mar 28, 2011
Messages
389
I currently have a sheet setup so that the bottom line of the sheet reads the same as the footer, and the footer is set by the text in that line. That way when I need to change the footer I can simply change that line and not have to deal with going into the page setup, etc. I password protected the cell with a Worksheet_Change event and that all works perfectly in preventing the footer from being changed without proper access. The problem I have is that I would like to find a way to copy the cells original value (say on a Worksheet_Activate event) so that if the user changes that lower line and does not have access, that cell value will go back to the original text instead of the change that the user made.

I hope that makes sense. Here is what I currently have. Everything works out great as far as errors, etc. But if the password is entered incorrectly all it does is clear the cell rather than inserting the original text.

Code:
Private Sub Worksheet_Activate()
    FooterText = Sheets("Home").Range("Footer").Text
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Footer")) Is Nothing Then
        Password = InputBox("Please Enter Password to Change Footer", "Change Page Footer")
        If Password = "Atlascopco" Then
            ActiveSheet.PageSetup.CenterFooter = Format(Sheets("Home").Range("Footer").Value)
        Else: MsgBox "You are not authorized to make this change", vbOKOnly, "Password Incorrect"
            Application.EnableEvents = False
            Sheets("Home").Range("Footer").Value = FooterText
            Application.EnableEvents = True
        End If
    End If
End Sub

Thanks in advance for the help!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

klarowe

Active Member
Joined
Mar 28, 2011
Messages
389
I figured it out a different way. Since the footer isn't changed when the wrong password is inserted I used that to "reset" the cell value. This seems to be working perfectly. Sorry to take up space again... lol. I always seem to work it out after I post it... even though I may spend hours trying to work it out before I ask for help...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,043
Messages
5,599,479
Members
414,312
Latest member
mikefire911

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