Macros with protected sheets

[momentumons]

Hello! I have a model that has two macros in it that both basically copy and paste data. How can I get these to run but also protect the sheets so that no one accidentally makes changes to these (they should be making changes in the master sheets and using the macros to copy the data)...

My FIRST MACROS IS:

Sub REFRESH_DATA_TAB()
'
' REFRESH_DATA_TAB Macro
' ctrl alt f5 - to run the power query
'

'
ActiveWorkbook.RefreshAll
End Sub

(It activates a power query from another workbook)

MY SECOND MACRO IS:

Sub ARCHIVE_FCST()
'
' ARCHIVE_FCST Macro
'

'
End Sub

(It's a series of recorded steps)


Thanks!

 

[Yongle]

One minor amendment

amend
ActiveWorkbook.RefreshAll
to
ThisWorkbook.RefreshAll

If it is not working after that,
- remove all sheet protection
- then record a macro whilst manually refreshing ONLY the query on sheet DATA
- check that it did update the data
- post the code (click on <vba\> and paste the code between the tags)

 

[momentumons]

Hi @Yongle . No I don't think so? Not sure what that is but I haven't done it on purpose!

 

[Yongle]

Record a macro as suggested in previous post

 

[momentumons]

Thanks @Yongle . I am just trying to do that... not sure how, The only way I can get the power query to refresh is by recording ctrl alt f5 ....? Sorry! I'm not too good at this. I really appreciate your help.

The ctrl alt f5 macro works...

Sub ctrlaltf5()
'
' ctrlaltf5 Macro
'

'
    ActiveWorkbook.RefreshAll
End Sub

 

[Yongle]

NO - please refresh only ONE table

 

[momentumons]

Hi @Yongle - sorry the only way I know how to refresh the power query is via refresh all? :/

 

[Yongle]

You could try something like this and replace the name with the name of your query

Dim cn As WorkbookConnection
For Each cn In ThisWorkbook.Connections
    If cn = "Power Query – WhatEverItIsCalled" Then cn.Refresh
Next cn

 

[CephasOz]

If you want to stop users from changing things, but still allow your macro code to do so, then in ThisWorkbook you can paste:

' Workbook : Open
Private Sub Workbook_Open()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        wks.Protect UserInterfaceOnly:=True
    Next wks
End Sub

 

[momentumons]

Hi @CephasOz thank you! That sounds like what I need... but I can't seem to make it work. Can you help please?

I don't really know how to write any VBA code. The way I was recording the macro to refresh the power query was simply to "record macro" of a pressing ctrl alt f5 and then ending the recording This works. However, if I protect the sheet, I can't then get that macro to work.

I've tried to edit the working macro by adding the code you suggested - but it's not working. It no longer refreshes the data.

Any ideas? Thanks!

' Workbook : Open
Private Sub Workbook_Open()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        wks.Protect UserInterfaceOnly:=True
    Next wks
End Sub

Sub REFRESH_SIMPLE()
'
' REFRESH_SIMPLE Macro
'
    ActiveWorkbook.RefreshAll
End Sub

 

[momentumons]

@CephasOz - I actually want to protect all the tabs eventually and will have a few other macros that I also need to run (again all my macros are simple recordings of steps I have performed). So can you help me solve this problem in a way that will allow me to replicate it for all the other tabs