asked on
VBA Code to keep user from Changing the Protection of a Sheet from Protected to Unprotected
I have code that does a data refresh. All of the cells are protected with the exception of the last 3 (SubmittedDate, AcceptedDate2 and Comments2).
In the code it unprotects the sheet to do the data refresh, and then once the refresh is complete, it protects the sheet again. The only problem with this is a user can click on Review, Protect Sheet, and it will allow them to unprotect the sheet if they want to, which I don't want them to be able to do this. Is there a way around this?
Thanks,
gdunn59
Here is the entire Refresh Code:
In the code it unprotects the sheet to do the data refresh, and then once the refresh is complete, it protects the sheet again. The only problem with this is a user can click on Review, Protect Sheet, and it will allow them to unprotect the sheet if they want to, which I don't want them to be able to do this. Is there a way around this?
Thanks,
gdunn59
Here is the entire Refresh Code:
Private Sub cmdRefreshSQLData3ed_Click()
' RefresheBillDatafromSQLSP Macro
' RefresheBillDatafromSQLSP
'
Dim i As Long
Dim wb1 As Excel.Workbook
Dim k As Long
ActiveWorkbook.Worksheets("E-Bill Tracking").Activate
ActiveWorkbook.ActiveSheet.Unprotect Password:="eBillStatus"
i = 8
k = 2
Dim ctrl As CommandBarControl
Dim cb As CommandBar
Application.CommandBars(1).Controls("Tools").Controls("Protection").Enabled = False
' Warn user that existing data in Columns M, N and O will be deleted
YesNo = MsgBox("Any existing data in SubmittedDate, DateAccepted2 and Comments2 will be deleted. Do you wish to proceed?", vbYesNo + vbCritical, "WARNING!")
If YesNo = vbNo Then
Range("A8").Select
ActiveSheet.Protect
ActiveSheet.Protect AllowFiltering:=True
Exit Sub
Else
'remove any filters prior to refreshing the data
If ActiveWorkbook.ActiveSheet.FilterMode Or _
ActiveWorkbook.ActiveSheet.AutoFilterMode Then _
ActiveWorkbook.ActiveSheet.ShowAllData
Range("A8").Select
' Remove any existing data in Columns M, N and O
Do Until i > Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If Cells(i, 13).Value <> "" Or Cells(i, 14).Value <> "" Or Cells(i, 15).Value <> "" Then
Cells(i, 13).Value = ""
Cells(i, 14).Value = ""
Cells(i, 15).Value = ""
k = k + 1
End If
i = i + 1
Loop
For Each objconnection In ThisWorkbook.Connections
'Get current background-refresh value
bBackground = objconnection.OLEDBConnection.BackgroundQuery
'Temporarily disable background-refresh
objconnection.OLEDBConnection.BackgroundQuery = False
'Refresh this connection
objconnection.Refresh
'Set background-refresh value back to original value
objconnection.OLEDBConnection.BackgroundQuery = bBackground
Next
End If
Range("L8").Select
ActiveWorkbook.ActiveSheet.Protect
ActiveSheet.Protect AllowFiltering:=True
End Sub
VBAMicrosoft Excel
Last Comment
gdunn59
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Sorry for the misunderstanding.
I do the same thing in one of my sheets, and I thought for sure it locked out the user. Maybe I have another option/setting enabled. I'll double-check my setup shortly.
I do the same thing in one of my sheets, and I thought for sure it locked out the user. Maybe I have another option/setting enabled. I'll double-check my setup shortly.
ASKER
Ok. Thanks!
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Doug,
That didn't make a difference. It still doesn't prompt for a password if I click on the Review Menu and Protect Sheet. Although when I initially Protect the sheet, I put in a password.
It works initially before I run the Refresh, but once the refresh is done, it loses the password.
Thanks,
gdunn59
That didn't make a difference. It still doesn't prompt for a password if I click on the Review Menu and Protect Sheet. Although when I initially Protect the sheet, I put in a password.
It works initially before I run the Refresh, but once the refresh is done, it loses the password.
Thanks,
gdunn59
ASKER
I just need to figure out why it loses the password on the protect after the refresh.
Anyone have any clues?
I've tried everything.
Thanks,
gdunn59
Anyone have any clues?
I've tried everything.
Thanks,
gdunn59
g,
I'm afraid not.
I'm afraid not.
When you unprotect the sheet using the password, you're not just unprotecting it - you are also removing the password.
That means you have to (re)assign the password at the end of your code. Mine does that, and it immediately prompts for the password when I select Unprotect Sheet from the right-click menu.
That means you have to (re)assign the password at the end of your code. Mine does that, and it immediately prompts for the password when I select Unprotect Sheet from the right-click menu.
That's what I was saying but it sounded like it still didn't work for him.
ASKER
Yes, that's what I was doing in my initial post. That's why I asked for assistance because it wasn't working.
No, you didn't do that in your initial post. Here are the last 3 lines from your initial post:
You clearly did not include the password.
ActiveWorkbook.ActiveSheet.Protect
ActiveSheet.Protect AllowFiltering:=True
End Sub
You clearly did not include the password.
Right. See my post, I noted just the last few lines of code but added the password in bold. I thought he was saying that he tried what I suggested but it didn't work. This would make sense as the problem which was why I was at a loss.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
byundt,
I tried your suggestion:
ActiveWorkbook.ActiveSheet
But I'm getting this error:
The following data range failed to refresh:
RefreshSQLData_3ed_1
Continue to refresh all?
OK Cancel
If I click OK it does the refresh, and the if I click on the Review, Unprotect Sheet it prompts for the password (so that is working now), but I don't want the user to have to see the error about the refresh and have to refresh it manually.
Thanks,
gdunn
I tried your suggestion:
ActiveWorkbook.ActiveSheet
But I'm getting this error:
The following data range failed to refresh:
RefreshSQLData_3ed_1
Continue to refresh all?
OK Cancel
If I click OK it does the refresh, and the if I click on the Review, Unprotect Sheet it prompts for the password (so that is working now), but I don't want the user to have to see the error about the refresh and have to refresh it manually.
Thanks,
gdunn
ASKER
Ok. I got it everything to work. I ended up having to add the unprotect code to line 51 right before it does the refresh.
Here is my final code:
Thanks everyone!
Here is my final code:
Private Sub cmdRefreshSQLData3ed_Click()
' RefresheBillDatafromSQLSP Macro
' RefresheBillDatafromSQLSP
'
Dim i As Long
Dim wb1 As Excel.Workbook
Dim k As Long
ActiveWorkbook.Worksheets("E-Bill Tracking").Activate
ActiveWorkbook.ActiveSheet.Unprotect Password:="eBillStatus"
i = 8
k = 2
' Warn user that existing data in Columns M, N and O will be deleted
YesNo = MsgBox("Any existing data in SubmittedDate, DateAccepted2 and Comments2 will be deleted. Do you wish to proceed?", vbYesNo + vbCritical, "WARNING!")
If YesNo = vbNo Then
Range("A8").Select
ActiveWorkbook.ActiveSheet.Protect Password:="eBillStatus", AllowFiltering:=True
Exit Sub
Else
'remove any filters prior to refreshing the data
If ActiveWorkbook.ActiveSheet.FilterMode Or _
ActiveWorkbook.ActiveSheet.AutoFilterMode Then _
ActiveWorkbook.ActiveSheet.ShowAllData
Range("A8").Select
' Remove any existing data in Columns M, N and O
Do Until i > Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If Cells(i, 13).Value <> "" Or Cells(i, 14).Value <> "" Or Cells(i, 15).Value <> "" Then
Cells(i, 13).Value = ""
Cells(i, 14).Value = ""
Cells(i, 15).Value = ""
' k = k + 1
End If
i = i + 1
Loop
For Each objconnection In ThisWorkbook.Connections
'Get current background-refresh value
bBackground = objconnection.OLEDBConnection.BackgroundQuery
'Temporarily disable background-refresh
objconnection.OLEDBConnection.BackgroundQuery = False
'Refresh this connection
ActiveWorkbook.ActiveSheet.Unprotect Password:="eBillStatus"
objconnection.Refresh
'Set background-refresh value back to original value
objconnection.OLEDBConnection.BackgroundQuery = bBackground
Next
End If
Range("L8").Select
ActiveWorkbook.ActiveSheet.Protect Password:="eBillStatus", AllowFiltering:=True
End Sub
Thanks everyone!
Consider routinely using the UserInterfaceOnly parameter when you protect a worksheet. This parameter doesn't change worksheet protection as far as the user is concerned, but instead allows macros to have a freer hand in making changes. In many cases, macros can do what they need without unprotecting the worksheet. It doesn't work for every worksheet manipulation by a macro (so testing is required), but every time you can avoid the need to unprotect a worksheet the better.
ActiveWorkbook.ActiveSheet.Protect Password:="eBillStatus", AllowFiltering:=True, UserInterfaceOnly:=True
ASKER
byundt,
When I had the UserInterfaceOnly parameter in there I got a Application Defined Error. We're not able to change any of the references. Our IT Department has those locked down.
Thanks,
gdunn59
When I had the UserInterfaceOnly parameter in there I got a Application Defined Error. We're not able to change any of the references. Our IT Department has those locked down.
Thanks,
gdunn59
ASKER
Thank you everyone!
I already have that in my code I posted.
I don't think you understand what I mean.
The code is protecting and unprotecting when needed, but my concern is that the user can still go to the menu Review and toggle protect/unprotect. That's what I want to avoid. I did assign a password, but after the refresh is done, I can still click on the Review Menu and unprotect the sheet if I want to, and it doesn't prompt for a password.