×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Running macros with worksheet protection

Running macros with worksheet protection

Running macros with worksheet protection

(OP)
Some of you may remember I have made a spreadsheet to keep a championship table.  I will be handing this over to someone in the club, and I want to make sure that they do not accidentally click on a cell containing a formula (which needs to be visible all the time) and deleting the data/formula.  (The user is not too proficient in the use of excel (less than myself), but can input data).  
I have made three macros (record macro), one to sort by Championship position, one to sort by name, and one to sort by riding number.
The problem I have is that to be able to run a macro, the password (for the worksheet protection) needs to be typed in.  The macros have been made in such a way that they lock the worksheet again, however it does mean that the user (inputer) has to have the password.  After you have run one macro, subsequent running any macros to not require the password to be input again.
Is there a way to overcome this problem - for instance is there a way to protect the worksheet, but allow the user to run macros without inputting the password?  Or any other ideas?

Thanks in advance.

RE: Running macros with worksheet protection

If I understand your problem then try this to protect the worksheet

Worksheets("Sheetname").Protect("password")

This protects the sheet with a pasword and requires it to be entered by the user to unprotect the sheet.
You can unprotect the sheet in macro by replacing Protect with Unprotect. Therefore protect and unprotect the sheet as required in the macro without the user ever knowing the password

RE: Running macros with worksheet protection

You could unprotect the sheet in code on the appropriate event and then reprotect at the end of the macro:

CODE

Sub Macro1()
    ActiveSheet.Unprotect
' Rest of your macro here
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

Steam Engine enthusiasts: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources