Solved: Worksheet Protection Vs Macro

[mike31z]

I have created a file with 5 work sheets ( I removed 2 worksheets to reduce file size) on each of the work sheet their are some cells with formula's that inter act with cells the anyone using the file can enter data and get results based on the input.

I have Uploaded my file as a zip file so I hope my macro will work. I hope.
I did not protect any sheet but, I marked the cell I don't want protected. If a sheet is protected I did not enter a password.

My Problem I can not get my macro to run when I protect the worksheet.

I have unlocked the cell under "Format, Cells, Protection" for cells E2:H121 and cellls K2:k121 on the handicap work sheet. I deleted a couple of other sheet to reduce the size of the file, there content is simular to the Handicap work sheet.

How can I get a worksheet to run a macro while the work sheet is protected in order to protect all the formulas.

mike in wisconsin

[lucas]

Mike,
try putting this in your thisworkbook module....not a standard module....then save and close and re-open the workbook:
Private Sub Workbook_Open()
Worksheets("Handicap").Protect Password:="", userinterfaceonly:=True
End Sub

The UserInterfaceOnly argument sets it to allow macros to run on the worksheet, but prevent users from editing "locked" fields.

[mike31z]

I am Sorry but, I am having a hard time creating or finding a "workbook module" when I try this I just get another new blank workbook.
Also in your example you put Handicap in quotes as the primary worksheet if I wanted to add a second worksheet would the correct entry be "


Private Sub Workbook_Open()
Worksheets("Handicap", "Reg").Protect Password:="", userinterfaceonly:=True
End Sub


Thank You,
Mike in wisconsin

[lucas]

Hi Mike,
In the vbe where you have your code.....on the left side look for the project explorer. It will say Project-VBA Project at the top. If it is not there go to view and select project explorer to make it visable. Look for the thisworkbook module and be sure it's below the vba project(Mike31z.xls)
Double click on it and paste the code there.

Use this for multiple sheets:


Private Sub Workbook_Open()
Worksheets("Handicap").Protect Password:="", Userinterfaceonly:=True
Worksheets("Reg").Protect Password:="", userinterfaceonly:=True
End Sub

[mike31z]

Thank you for both Lesson.
1. an easy way to protect the formulas and run macro.
2. Showing me the "Project Explorer" that helped me a lot.

I have implemented the "Private Sub" on my complete file and it works just excellent.
Now my only problem will be when somebody wants to change a locked cell instead of entering the data in the correct worksheet.

Thank You

Mike in Wisconsin