PDA

View Full Version : refresh all on protected sheet



percy4
05-07-2009, 01:27 AM
Hi all,

I’ve spent hours on trying to work this code out but without any luck. Could you please assist?

This code works fine and I get the messagebox saying “data refreshed” but then I get following errormessage directly from excel.

The cell or chart you are trying to change is protected and therefore read only.

Help me and become my hero =)

Best regards
Per

I run this code on file opening:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="test", _
userinterfaceonly:=True
Next ws
End Sub



Sub refresh()
MsgBox "Data will now be refreshed. It may take up to 60 seconds."
Dim ws As Worksheet
'Dim qt As QueryTable
Dim wb As Workbook
Dim sourcefile As String
sourcefile = "G:\Purchase\Blockorder calculations interior display\2009 autumn\Masterlistan\Copy of Historik v1 arbetskopia.xls"
Application.StatusBar = "Macro running, please wait....."
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="test"
Next ws

Set wb = Workbooks.Open(sourcefile)
ThisWorkbook.RefreshAll
'On Error Resume Next
Debug.Print ThisWorkbook.Name
'For Each qt In ws.QueryTables
' qt.BackgroundQuery = False
' qt.Refresh
' Next qt
'Next ws
DoEvents
With wb
.Save
.Close
End With
Set wb = Nothing



For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="test", _
DrawingObjects:=True, Contents:=True, Scenarios:=True, _
userinterfaceonly:=True, AllowSorting:=True, AllowFiltering:=True
ws.EnableSelection = xlNoSelection
Next ws

'ws.EnableAutoFilter = "true"
'ws.EnableSelection = xlNoRestrictions

'ThisWorkbook.Sheets(1).Select
'Range("A1").Select
MsgBox "Data refreshed!"
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

Bob Phillips
05-07-2009, 02:10 AM
I couldn't reproduce it. I didn't have the workbook with the queries, but that is closed before the error anyway.

percy4
05-07-2009, 02:20 AM
I couldn't reproduce it. I didn't have the workbook with the queries, but that is closed before the error anyway.

Hi Xld,

You are my guiding hand!!

The workbook containing the queries is thisworkbook(a masterlist) the workbook that is being opened has many links to thisworkbook so the refresh is much faster if that one is opened.

I've been fooling around a little and found out that when I uncheck enable background query in the data range properties dialouge it works fine. Does anyone know why that is so?

Bob Phillips
05-07-2009, 03:40 AM
Well I didn't feel I was helping, but if my mumblings gave you a steer all to the good :)

Could it be that have background refresh set it is continuing the macro whilst the query is still running, and the sheet is protected before it finishes, so it then tries to write to a protected sheet.

percy4
05-07-2009, 05:59 AM
Hi my code now works after querysettings but sadly another problem came up. The graphic is all messed up on all sheets but the one that was chosen before the refresh macro was started. By messed up I mean that the scrollbars cant be moved and it looks like you used freeze panes. What the hell is this?

Has anyone had any previous encounters of this problem?

Best regards
Per

percy4
05-08-2009, 12:20 AM
Hi again,

That graphic bug I was talking about is actually a window property issue. Excel shows the “maximize” view but the actual active window is just “restore view”. So everything outside the “restore view” is dead and dosen’t change when I scroll up and down.

I hopes this makes any sense what I am trying to explain.

Does anyone know how to fix this issue?

Bob Phillips
05-08-2009, 01:52 AM
Just so that you don't think I am ignoring you, but personally, without the query it is difficult to envisage, and I am certainly not aware of the problem myself.

percy4
05-08-2009, 03:08 AM
Just so that you don't think I am ignoring you, but personally, without the query it is difficult to envisage, and I am certainly not aware of the problem myself.

I totally understand!

The strange thing is that the windows work just fine if you (after running the refresh macro) switch to restore veiw and then switch back to maximize again. Is this any way to do this automaticlly via my macro?

Thanks once again XLD!