Stop warning message from appearing when protecting list tables

Air_Cooled_Nut

New Member
Joined
Oct 8, 2004
Messages
36
I have the following code successfully running, however, once it gets to the bold orange section I get the following warning message for EACH query on the sheet that is being password protected:
The cell or chart that you are trying to change is protected and therefore read-only.
To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password.
Here's my code:
Code:
Private Sub CommandButton1_Click()
'Update the dimensions on the user's sheet.  User added dimensions and placement order must be retained!  Oy!
Dim sFilePath As String, x As Long, sMsg As String, sUseFile As String

On Error GoTo cbErr_Handler  'Handle errors ourselves
'Open the intermediate wkbk, suck its master dimensions into an array, and then close it.
With Application
    .StatusBar = "Pulling master dimensions..."
    .EnableEvents = False
    .ScreenUpdating = False
End With
sFilePath = ThisWorkbook.path & "\..\tools"  'Folder where the master dimension wkbk is
With ActiveWorkbook
    'Unprotect the list table sheets
    .Worksheets("From_Dimen_Feeder").Unprotect gPASS  'Unprotect the sheet

    For x = 1 To .Connections.Count  'Loop through each data connection
        Application.StatusBar = "Updating data connection: " & .Connections.Item(x).Name
        With .Connections(x).ODBCConnection
            'Only change where the data is coming from
            If Left(.Parent.Name, 4) = "Cost" Then
                sUseFile = gCOST_FEED
            Else
                sUseFile = gDIM_FEED
            End If
            .Connection = Array( _
                Array("ODBC;DBQ=" & sFilePath & "\" & sUseFile & ";DefaultDir=" & sFilePath & ";Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Driv"), _
                Array("erId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserC"), _
                Array("ommitSync=Yes;"))
        End With
        .Connections(x).Refresh  'Update the table (refresh the data).  If the file doesn't exist an unhandlable [by VBA] ODBC error will occur.
    Next x

    'Re-protect worksheets
    With .ActiveSheet
[B][COLOR="#DAA520"]        .Protect Password:=gPASS, UserInterfaceOnly:=True, Password:=gPASS, DrawingObjects:=True, Contents:=True, _
             Scenarios:=True, AllowFormattingCells:=True, AllowFiltering:=True[/COLOR][/B]
        .EnableSelection = xlUnlockedCells
    End With

End With
MsgBox "All data feed inputs have been updated.", vbInformation

cbErr_Resume:
    With Application
        .StatusBar = False
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    On Error GoTo 0  'Resume normal error handling
    Exit Sub

cbErr_Handler:
    Call Error_Handler(Err.Number, Err.Description, "cbDimensionUpdate_Click()", "Dimensions sheet")
    sMsg = "Data sheet(s) may not have been updated." & vbCrLf
    sMsg = sMsg & "If you're not connected to the network then this error is expected."
    MsgBox sMsg, vbExclamation, "Problem updating a data sheet"
'    Stop  'for testing
'    Resume Next  'for testing
    GoTo cbErr_Resume
End Sub

I tried Application.DisplayAlerts = False and set calculation to xlManual and neither stopped the message from appearing. What am I doing wrong, how can I insure that message doesn't appear? This is affecting 27 user workbooks.
Though I doubt it matters, here's the Table info for one of the tables:
Usage tab: Enable background refresh and that's it.
Definition tab--
Authentication Settings...: None.
Connection type: Database Query (even though it's hitting an Excel workbook).
Example of Command text from one query:
SELECT Dimen010203.Description, Dimen010203.`FT Program`, Dimen010203.`Project Type`, Dimen010203.`Sub-type`
FROM Dimen010203 Dimen010203
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,215,373
Messages
6,124,562
Members
449,171
Latest member
jominadeo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top