Excel 2013 Password Protection Pissing me off Again !

[spurs]

I have the following macro that runs on SOME machines with excel 2013 but not all.

This macro always works if you step through the macro line by line, but does not work if you run the macro full speed starting with the worksheet protected. If the worksheet starts in unprotected mode it works in full speed.



Sub Inc_Dec()
' This subroutine adds a decimal place to the units
Dim Num_Dec As Integer

Num_Dec = Range("A1").Value

Select Case Num_Dec
Case 0
Dec = "0.0"
Case 1
Dec = "0.00"
Case 2
Dec = "0.000"
Case 3
Dec = "0.0000"
Case 4
Dec = "0.00000"
Case Else
GoTo 100
End Select

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual ' turns off autmatic spreadsheet calculation

Sheets("Test Geometry (in)").Unprotect "1234"
Sheets("Test Geometry (mm)").Unprotect "1234"


Range("A1") = Num_Dec + 1 ' note 6 dec place is the maximum

Range("D19:G19").NumberFormat = Dec
Range("C27:I36").NumberFormat = Dec
Range("G37:G39").NumberFormat = Dec
Range("G41:G41").NumberFormat = Dec
Range("C42:F44").NumberFormat = Dec
Range("G44:I45").NumberFormat = Dec
Range("D48:I52").NumberFormat = Dec

Sheets("Test Geometry (in)").Protect "1234"
Sheets("Test Geometry (mm)").Protect "1234"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

100 End Sub


Any thoughts on how to make this work - and still use password protection on the worksheets?

 

[mole999]

after turning password off, add DoEvents

 

[spurs]

DoEvents did not do anything

I added it after every password unprotect line and again after every protect line

 

[RoryA]

Why do you unprotect two sheets then alter the number format on whatever happens to be the active sheet?

 

[spurs]

Because the macro is initiated from either of the two sheets and I only want the active sheet to be changed.
This worked ok in excel 2003, I found that for excel 2013 I have to first set the active sheet and then unprotect only the active sheet, then it works