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?
Here is the entire Refresh Code:
Private Sub cmdRefreshSQLData3ed_Click()' RefresheBillDatafromSQLSP Macro' RefresheBillDatafromSQLSP'Dim i As LongDim wb1 As Excel.WorkbookDim k As LongActiveWorkbook.Worksheets("E-Bill Tracking").ActivateActiveWorkbook.ActiveSheet.Unprotect Password:="eBillStatus"i = 8k = 2Dim ctrl As CommandBarControlDim cb As CommandBarApplication.CommandBars(1).Controls("Tools").Controls("Protection").Enabled = False' Warn user that existing data in Columns M, N and O will be deletedYesNo = 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 ODo 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 + 1LoopFor 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 = bBackgroundNextEnd IfRange("L8").SelectActiveWorkbook.ActiveSheet.ProtectActiveSheet.Protect AllowFiltering:=TrueEnd Sub
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.