Excel宏在宏完成并且工作表已锁定后尝试进行更改。

huangapple go评论81阅读模式
英文:

Excel macro trying to make changes after macro is finished and worksheet is locked

问题

I've been protecting an Excel document from being edited since users keep breaking it. While this Excel document shouldn't be able to be broken this easily in the first place; it is outside my scope to fix it. I am just here to provide a temporary solution.

The issue
When users execute the refresh macro, it locks the worksheet too quickly, resulting in an error that it can't make changes since the cell or chart is protected. The strange thing is that the module for locking the sheet only gets executed at the end. Does anyone know why this is happening?

My Code

  1. Sub Verversen()
  2. '
  3. ' Verversen Macro
  4. '
  5. Call Module11.unlockcells
  6. Range("J12").Select
  7. ActiveWorkbook.RefreshAll
  8. ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort. _
  9. SortFields.Clear
  10. ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort. _
  11. SortFields.Add Key:=Range("AllStockSQL[[#Headers],[#Data],[Batch]]"), SortOn _
  12. :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  13. With ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort
  14. .Header = xlYes
  15. .MatchCase = False
  16. .Orientation = xlTopToBottom
  17. .SortMethod = xlPinYin
  18. .Apply
  19. End With
  20. 'Call Module12.Refresh_All_Data_Connections
  21. ' Range("G31").Select
  22. ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
  23. ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
  24. ' ActiveWorkbook.RefreshAll
  25. Call Module9.lockcells
  26. End Sub

Module 9

  1. Public Sub lockcells()
  2. Worksheets("Batch Checker").Range("F4:N200").Locked = True
  3. ActiveSheet.Protect Password:="temp", UserInterfaceOnly:=True, AllowUsingPivotTables:=True
  4. End Sub

Module 11

  1. Public Sub unlockcells()
  2. ActiveSheet.Unprotect Password:="temp"
  3. Worksheets("Batch Checker").Range("F4:N200").Locked = False
  4. End Sub

I've tried putting in a wait timer; I've turned off the background refresh except the option "Refresh this connection on Refresh All"; I've tried to put DoEvents before; I've tried creating different modules which I'd call in. Nothing works, possibly I am overlooking something simple as I am quite a novice in VBA.

英文:

I've been protecting a Excel document from being edited since users keep breaking it.
While this excel document shouldn't be able to be broken this easily in the first place; it is outside my scope to fix it. I am just here to provide a temporary solution.

The issue
When users execute the refresh macro it locks the worksheet to fast; in turn it gives a the error that it can't make changes since the cell or chart is protected.
Strange thing is that the module for locking the sheet only gets executed at the end.
Anyone knows why this is happening?

My Code

  1. Sub Verversen()
  2. '
  3. ' Verversen Macro
  4. '
  5. Call Module11.unlockcells
  6. Range("J12").Select
  7. ActiveWorkbook.RefreshAll
  8. ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort. _
  9. SortFields.Clear
  10. ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort. _
  11. SortFields.Add Key:=Range("AllStockSQL[[#Headers],[#Data],[Batch]]"), SortOn _
  12. :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  13. With ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort
  14. .Header = xlYes
  15. .MatchCase = False
  16. .Orientation = xlTopToBottom
  17. .SortMethod = xlPinYin
  18. .Apply
  19. End With
  20. 'Call Module12.Refresh_All_Data_Connections
  21. ' Range("G31").Select
  22. ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
  23. ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
  24. ' ActiveWorkbook.RefreshAll
  25. Call Module9.lockcells
  26. End Sub

Module 9

  1. Public Sub lockcells()
  2. Worksheets("Batch Checker").Range("F4:N200").Locked = True
  3. ActiveSheet.Protect Password:="temp", UserInterfaceOnly:=True, AllowUsingPivotTables:=True
  4. End Sub

Module 11

  1. Public Sub unlockcells()
  2. ActiveSheet.UNprotect Password:="temp"
  3. Worksheets("Batch Checker").Range("F4:N200").Locked = False
  4. End Sub

I've tried putting in a wait timer;
I've turned off the background refresh except option Refresh this connection on Refresh All;
I've tried to put DoEvent before;
I've tried creating different modules which I'd call in
Nothing works, possibly I am overlooking something simple as I am quite a novice in VBA.

答案1

得分: 0

以下是翻译好的部分:

解决方案由Emilio Silva提供。

在锁定模块之前,我放置了Application.CalculateFull和Application.CalculateUntilAsyncQueriesDone。

模块9

  1. Public Sub lockcells()
  2. Application.CalculateFull
  3. Application.CalculateUntilAsyncQueriesDone
  4. Worksheets("Batch Checker").Range("A4:N200").Locked = True
  5. Worksheets("Batch Checker").Protect Password:="已编辑", UserInterfaceOnly:=True, AllowUsingPivotTables:=True
  6. End Sub
英文:

Solution was given by Emilio Silva.

In the lock module before it locks I put Application.CalculateFull and Application.CalculateUntilAsyncQueriesDone.

Module 9

  1. Public Sub lockcells()
  2. Application.CalculateFull
  3. Application.CalculateUntilAsyncQueriesDone
  4. Worksheets("Batch Checker").Range("A4:N200").Locked = True
  5. Worksheets("Batch Checker").Protect Password:="REDacted", UserInterfaceOnly:=True, AllowUsingPivotTables:=True
  6. End Sub

huangapple
  • 本文由 发表于 2023年5月18日 00:14:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76274161.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定