Re-opening .xlsm sheet post saving gives error

ajoy123rocks · Nov 8th 2021

I have a macro-enabled sheet where I perform calculations by unprotecting the sheet and then protecting the sheet post calculations. The sheet has a button "CALCULATE" that triggers calculations.

Scenario:

1. I have performed a calculation and saved the sheet as is.

2 Closed the worksheet and re-opened the same

3. on Clicking "CALCULATE" now it gives error

"Run-time error '1004':

The cell or chart you're trying to change is on a protected sheet.

To make a change, Unprotect the Sheet. You might be requested to enter a password.

Kindly help

**rory** · Nov 8th 2021

Without the code or the workbook, I think helping will be difficult.

ajoy123rocks · Nov 8th 2021

1. Enter the number of teams (say 4)

2 Click generate

This should throw an error and debug will take you to a particular line of code.

Clicking generate again will throw another error and take you to another line of code

Clicking generate for third time will produce the intended result.

Click reset and click on yes. Now save the file and re-open and repeat the above steps to reproduce

**royUK** · Nov 8th 2021

Looks like it's because the sheet is protected. You can unprotect the sheet before the code runs

Code

ActiveSheet.UnProtect "your password"


'your code


Activesheet.Protect "your password"

Or useProtectWithUserInterFaceOnly

**royUK** · Nov 8th 2021

There are other errors in the code as well, e.g.

Code

LastRow = .Cells(.Rows.count, "A").End(xlUp).Row

This will error because it is not within a With ... End With Statement.

ajoy123rocks · Nov 9th 2021

Each subroutine and function has been written within unprotect and protect.

The program works after the initial hickup. The question is why such an issue occurs after i close and open the file?

This line of code is not used in the program.

LastRow = .Cells(.Rows.count, "A").End(xlUp).Row

**royUK** · Nov 9th 2021

Well it shouldn't be there then.

ajoy123rocks · Nov 9th 2021

Yes sir .. The code is still not final. Will remove once finalized.

ajoy123rocks · Nov 9th 2021

Code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Left_score As Integer
Dim Right_score As Integer
'Dim cell As Range

Application.ScreenUpdating = False

    If Not (Application.Intersect(Target, Range("B3,A9:A34")) Is Nothing) Then
    On Error GoTo safe_exit
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                
                'MsgBox 3
                'If .Value <> "" Then
                                
                    .Value = UCase(.Value)
                'End If
                Application.EnableEvents = True
                
            End If
        End With
   End If
   
      If Not (Application.Intersect(Target, Range("F9:F333")) Is Nothing) Then
      On Error GoTo safe_exit
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False


                
                         Left_score = Trim(Split(.Value, "-")(0))
                
                         Right_score = Trim(Split(.Value, "-")(1))
                         
                         'MsgBox Left_score
                         'MsgBox Right_score
                         
                          If (Left_score = 20 And Right_score = 0) Or (Left_score = 0 And Right_score = 20) Then
                                   .Font.ColorIndex = 3
                         
                        Else
                                     .Font.ColorIndex = 1
                         End If
                
                Application.EnableEvents = True

            End If
        End With
    End If

safe_exit:
       Application.EnableEvents = True

End Sub

Display More

The first part of code to change to Upper case is working whereas the second part of changing the color is not. Kindly help

Re-opening .xlsm sheet post saving gives error

Participate now!

Share

Tags