Solved: Unprotect/Protect when updating/refreshing data

[coliervile]

I want to update my database and refresh a pivot table within my workbook and protect their worksheets while this function is being performed. I know there's a way to Unprotecting/Protecting worksheets while attempting to update/refresh the worksheets but my attempts haven't worked. I want to protect the following worksheets- "OvertimeTotals" and "OvertimeDatabase". The worksheets are refereshed and updated through the "Add Overtime" button on the userform "Overtime Form". Any assistance would be appreciated.

Charlie

[OBP]

You have not got "Protection" set to "on" before the update so there isn't anything to turn off.
After selecting each sheet with your VBA the code for turning it on/off is
Activesheet.Unprotect password:=""
if a password is set it goes between the "".
To put the protection back on use
Activesheet.Protect password:="", DrawingObjects:=True, Scenarios:=True

[coliervile]

This seems to be working for the OvertimeTotals worksheet:

'Refresh Pivot
With Worksheets("OvertimeTotals")
ActiveSheet.Unprotect "secret"
.PivotTables(1).PivotCache.Refresh
' code
ActiveSheet.Protect "PasswordGoesHere"
End With
this is on the userform code "cmdadd".

I'm using excel 2000 if this makes a difference to the code you recommended. I did try your code but it returned an error.

I've also tried numerous time to get the code to work on the OvertimeDatabase and couldn't get it to work. I'm obviously putting it it the wrong place????? I did have the worksheets protected when doing this.

Please look at the attached file.

Charlie

[coliervile]

OBP I appologize, I forwarded you a bad copy of my file. Even though I corrected the incorrect password the "unprotect/protect" doesn't work after the file is reopened....causes an error. Here's my working copy of file without any "unprotect/protect" coding the "OvertimeTotals" and "OvertimeDatabase" worksheets are password protected: password = "secret". Please take a look see and tell me what I'm missing.

Charlie

[OBP]

Charlie, it must be to do with where you are trying to put the code, have a look at this copy of the workbook. It has "Protect" and "Unprotect" command buttons on the "OvertimeDatabase" worksheet which sets and un-sets the protection for that worksheet.
It has the same buttons on the "OvertimeEntry" Worksheet which also set and un-set the protection for the "OvertimeDatabase" worksheet, there is some additional cde on these.
I have not bothered with setting SceenUpdating.

[coliervile]

Thanks for taking the time to work on this. The buttons on the face would be fine if other employees didn't have access to this document. Let me explain: When the Supervisors aren't at work employees get to play "Supervisor". It wouldn't be unusual for an employee to amend the total hours to get more overtime. The employee with the least amount gets called in first. It would be beneficial for the update/refresh be included in the cmdAdd_Click() code on the userform, this way the vba code can have a password and hide the code. I also have a refresh code on the worksheet (OvertimeTotals) tab when clicked it will also refresh the data. I have this on the worksheet tab so that if the database (OvertimeDatabase) worksheet is change from the worksheet toolbar-data-form.

If you have the time please look at the userform code and see what you can come up with.

Thanks again for you help thus far.

Charlie

[OBP]

Charlie, I just put the buttons on the worksheets to show how the VBA worked. You should be able to just copy the VBA on the OvertimeEntry worksheet to your Forms code. Give it a try and if it doesn't work, come back on here and let me know.
If this was an Access Database you could ahve the security set so that only Supervisors could change the data.
You can do the same in Excel by creating a "Log-on" Form but it is hard work compared to Access.

[coliervile]

I'll give it a go and let you know what happens. Thanks again.

[OBP]

Charlie, I did it anyway. I have removed your protection routine as the on/off didn't match.
I have removed the buttons from the Database sheet and you can now remove them from the Entry sheet.

[coliervile]

I tried putting the codes in at different locations and pages without any luck. I may have to come up with a different idea. I'm trying something thats beyond my skill level here.
</IMG></IMG>

[OBP]

Charlie, I have added the code to your Form for you and it works fine the current Password is "QED". It is attached to post #9.

[coliervile]

It works great. I thought I had placed the Unprotect/Protect coding in the same places you did, but I must have benn missing something. When I first tried your suggestion I got a "Compile Error" End With without With. So I removed the "End With" and it worked great. This item is closed and I wish I could buy you a beer/drink for your hard work. "Issue Closed"- Solved

Have a good one-

Charlie

[coliervile]

Also what type of button control did you put on the worksheets- I can't get them off.

[lucas]

Right click on any toolbar and check the visual basic line. You will get a new toolbar. Hover over the buttons until you find one that says "Design Mode" click it then select the buttons and delete them. then click the button again to exit design mode.......

[coliervile]

Thanks "lucas" for responding...I did find it after thinking a bit.

Charlie