lockdown sheet but use macro

orsm6

Active Member
Joined
Oct 3, 2012
Messages
496
Office Version
  1. 365
Platform
  1. Windows
Hi all - i built a simple wb that is a survey... 1 question, yes or no buttons.

the button that is pressed adds one to another sheet (tally of answers) then saves the workboook.
when the option is selected a shape appears over the yes/no buttons saying thanks for your response and then goes away after a minute.

here is my problem and question.:
problem: people being people keep being a**holes and modifying the buttons, layout etc such as flipping buttons around, deleting texts.

question: is there a way to stop this? can i protect all of the contents from being modified but somehow still run the macro? i have seen where a code can call to unlock with password, then run macro, then relock with password via VBA....

thanks for your help and hope that made sense. cheers legends.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Yes, that is exactly what you need to do. It is very common to protect the contents of a workbook to ensure the user can only add or retrieve information in the correct way. Not because the are a**holes, but because people are generally not well disciplined in using spreadsheets and filling out information.

I normally add two macros in my macros: one to disable the protection, one to reestablish the protection.
In the VBA editor:
Add a new macro module
1637588700407.png

Copy / paste the following two macros there
VBA Code:
Option Explicit

Const sPW As String = "MyPassword"  ' <<<<<<< Replace by the password you use to protect the sheets

Sub ProtectSheet(wsWS As Worksheet)
' protect worksheet
' the settings here allow for sorting and filtering of tables and the use of pivottables

    wsWS.Protect Password:=sPW, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub

Sub UnprotectSheet(wsWS As Worksheet)
' unprotect worksheet
    wsWS.Unprotect sPW
End Sub
Then in your other macros called by the button, start the macro with:
VBA Code:
UnprotectSheet Sheets("DataSheet")  ' <<<<<< Use the sheet name of the sheet to be unprotected
and end it with
VBA Code:
ProtectSheet Sheets("DataSheet")  ' <<<<<< Use the sheet name of the sheet to be protected
so you get something like:
VBA Code:
Sub myMacro()

    ' unprotect sheet to allow changes
    UnprotectSheet Sheets("DataSheet")  ' <<<<<< Use the sheet name of the sheet to be unprotected
    
    
    '  the rest of my code comes here
    '...
    
    ' protect the sheet before control is given back to the user
    ProtectSheet Sheets("DataSheet")  ' <<<<<< Use the sheet name of the sheet to be protected
End Sub

Read all the comments to understand and modify to suit
 
Upvote 0
Solution
Yes, that is exactly what you need to do. It is very common to protect the contents of a workbook to ensure the user can only add or retrieve information in the correct way. Not because the are a**holes, but because people are generally not well disciplined in using spreadsheets and filling out information.

I normally add two macros in my macros: one to disable the protection, one to reestablish the protection.
In the VBA editor:
Add a new macro module View attachment 51803
Copy / paste the following two macros there
VBA Code:
Option Explicit

Const sPW As String = "MyPassword"  ' <<<<<<< Replace by the password you use to protect the sheets

Sub ProtectSheet(wsWS As Worksheet)
' protect worksheet
' the settings here allow for sorting and filtering of tables and the use of pivottables

    wsWS.Protect Password:=sPW, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub

Sub UnprotectSheet(wsWS As Worksheet)
' unprotect worksheet
    wsWS.Unprotect sPW
End Sub
Then in your other macros called by the button, start the macro with:
VBA Code:
UnprotectSheet Sheets("DataSheet")  ' <<<<<< Use the sheet name of the sheet to be unprotected
and end it with
VBA Code:
ProtectSheet Sheets("DataSheet")  ' <<<<<< Use the sheet name of the sheet to be protected
so you get something like:
VBA Code:
Sub myMacro()

    ' unprotect sheet to allow changes
    UnprotectSheet Sheets("DataSheet")  ' <<<<<< Use the sheet name of the sheet to be unprotected
   
   
    '  the rest of my code comes here
    '...
   
    ' protect the sheet before control is given back to the user
    ProtectSheet Sheets("DataSheet")  ' <<<<<< Use the sheet name of the sheet to be protected
End Sub

Read all the comments to understand and modify to suit
thanks Sijpie

great post.

back on people being people: they were flipping buttons, stretching or shrinking them, deleting page titles.

the behavior is extremely childish. the spreadsheet was on a Microsoft surface, and literally all they had to do was tap either the YES or the NO button. the person clearly had more understanding of how it works more than an average user.

but again, thanks heaps for this info. always get great help here and it is much appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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