Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations KootK on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running macros with worksheet protection

Status
Not open for further replies.
Engineeeeeeeeer

Engineeeeeeeeer

Mechanical
Jul 20, 2004
43
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.
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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
 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

vinodnx7
  • Locked
  • Question
count the same text from the file and create new list with number
Replies
19
Views
461
vinodnx7
vinodnx7
Tuckabag
  • Locked
  • Question
Acessing CMD shell in Excel/VBA with keystrokes and copying resultant data 1
Replies
8
Views
473
3DDave
3DDave
skeletron
  • Locked
  • Question
Footers and Page Break boundaries with print-to-PDF
Replies
3
Views
110
3DDave
3DDave
NedGan76
  • Locked
  • Question
Free structural design worksheets 3
Replies
7
Views
415
NedGan76
NedGan76
VH88
  • Locked
  • Question
File System Object won't recognize file name or file path with space | File Not Found Error
Replies
5
Views
193
dik
dik

Part and Inventory Search

Sponsor

Back
Top Bottom