Macro Assigned to Shape Changes Active Sheet

[RB2129]

I have a macro that changes the decimal places shown on a column of data in two worksheets. Running the macro from View Macros works fine.

Once the macro is assigned to a shape or form control, running the macro then changes the active sheet.

This only occurs when the macro unprotects then protects the sheets but this is required for the macro to function.

I tried removing the Unprotect and Protect code from the macro. The active sheet does not change but the sheets are protected so this cannot be removed.

The issue only occurs both when the protection is on and when the macro is assigned to a shape or to a form control.

The following recreates the problem. Run from Sheet1 via a shape or form control, active sheet stays as Sheet1. Run from a shape in Sheet2, active sheet moves to Sheet1.

Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.Unprotect Password:="test"
ws.Range("B3").NumberFormat = "#,##0"
ws.Protect Password:="test"

Set ws = Sheets("Sheet2")
ws.Unprotect Password:="test"
ws.Range("B3").NumberFormat = "#,##0"
ws.Protect Password:="test"

 

[Jaafar Tribak]

Hi and welcome to the forum.
Don't know why that happens but you could workaround this issue as follows:


Dim oSh As Worksheet
Dim ws As Worksheet

Application.ScreenUpdating = False
Set oSh = ActiveSheet.Shapes(Application.Caller).Parent

Set ws = Sheets("Sheet1")
ws.Unprotect Password:="test"
ws.Range("B3").NumberFormat = "#,##0"
ws.Protect Password:="test"

Set ws = Sheets("Sheet2")
ws.Unprotect Password:="test"
ws.Range("B3").NumberFormat = "#,##0"
ws.Protect Password:="test"

oSh.Activate