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:
[VBA]Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="test", _
userinterfaceonly:=True
Next ws
End Sub[/VBA]
[VBA]
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
[/VBA]
Reply With Quote
Originally Posted by xld
