VBA code suddleny producing an error

**drgogo** · 04-15-2010, 05:42 AM

Help..

the code listed below, has been working for well over a year, as its the original code that was created when i developed my spreadsheet. Now i have been making improvments through out the year, but never had a problem.

Sub InsertARow()
'
' InsertARow Macro
' Macro recorded 01/09/2008 by v14dmin
'
' Keyboard Shortcut: Ctrl+i

If ActiveSheet.Name <> "Timetabled Service" Then Exit Sub

ActiveSheet.Unprotect ("gideon")
Worksheets("Frequent Calculate").Unprotect ("gideon")
ActiveCell.EntireRow.Insert
ActiveCell.End(xlToLeft).Select
Range("Master_Row").Copy Destination:=ActiveCell


Worksheets("Frequent Calculate").Protect ("gideon")
ActiveSheet.Protect ("gideon"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True


'
End Sub

Now when the code gets excuted, I am presented with a run time error, stating that the sheet im trying to alter is protected etc

The line it then highlights is

Range("Master_Row").Copy Destination:=ActiveCell

The code does what its suppose to do i.e. insert a line from another sheet, then it produces the error, The active sheet is protected, but the sheet where the line comes from is no longer protected. Also I find that two of the cells within the line that is copied over, do not have there formulas in.

Now i cant work out why this is happening, as its never had any problems before

Any help or ideas why... ??

G

**romperstomper** · 04-15-2010, 06:34 AM

Are you sure the password is correct?
Also, do you have any event code in the sheet?

Finally, it will be easier to change your password if you store it in a variable:

Const strPASSWORD As String = "gideon"
Sub InsertARow()
'
' InsertARow Macro
' Macro recorded 01/09/2008 by v14dmin
'
' Keyboard Shortcut: Ctrl+i

If ActiveSheet.Name <> "Timetabled Service" Then Exit Sub

ActiveSheet.Unprotect strPASSWORD
Worksheets("Frequent Calculate").Unprotect strPASSWORD
ActiveCell.EntireRow.Insert
ActiveCell.End(xlToLeft).Select
Range("Master_Row").Copy Destination:=ActiveCell


Worksheets("Frequent Calculate").Protect strPASSWORD 
ActiveSheet.Protect strPASSWORD, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True

End Sub

**drgogo** · 04-15-2010, 06:54 AM

Hello

yes the password is correct, its the same password for all sheets, as i said its the original code and has been working for well over a year.

There is event code on that particular sheet, but i have never had a problem with this sheet before, it just seem to start happening

The code on the sheet is as follows

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="gideon"

    For Each cell In Target
        If Not Intersect(cell, Range("G2:G3000,H2:H3000,I2:J3000,M2:P3000")) Is Nothing Then
            If Application.WorksheetFunction.IsText(cell.Value) Then _
                cell.Value = UCase(cell.Value)
        ElseIf Not Intersect(cell, Range("D2:D10000")) Is Nothing Then
            If cell = "" Then
                Cells(cell.Row, "E") = ""
            Else
                Cells(cell.Row, "E").FormulaArray = "=MAX(IF(ISNUMBER(0+MID(RC[-1],1,ROW(R1:R4))),0+MID(RC4,1,ROW(R1:R4))))" _
                & "+IF(ISNUMBER(MATCH(RIGHT(RC[-1],1),R2C24:R27C24,0)),VLOOKUP(RIGHT(RC[-1],1),R2C24:R27C25,2,0)/10000,0)"
            End If
        End If
    Next cell
    
    ActiveSheet.Protect Password:="gideon"
    Application.EnableEvents = True
End Sub

But as i said, never had problems with this before, the row copies over, but with missing formula and the parent sheet does not protect it self again.

Is there anything wrong witht he code it self..?

**romperstomper** · 04-15-2010, 06:58 AM

If you insert a row, the Change event will fire, and your change event reprotects the sheet. You'll need to disable events while inserting:

Const strPASSWORD As String = "gideon"
Sub InsertARow()
'
' InsertARow Macro
' Macro recorded 01/09/2008 by v14dmin
'
' Keyboard Shortcut: Ctrl+i

If ActiveSheet.Name <> "Timetabled Service" Then Exit Sub

ActiveSheet.Unprotect strPASSWORD
Worksheets("Frequent Calculate").Unprotect strPASSWORD
on error resume next
application.enableevents = false
ActiveCell.EntireRow.Insert
ActiveCell.End(xlToLeft).Select
Range("Master_Row").Copy Destination:=ActiveCell
application.enableevents = True
on error goto 0

Worksheets("Frequent Calculate").Protect strPASSWORD 
ActiveSheet.Protect strPASSWORD, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True

End Sub

**drgogo** · 04-15-2010, 07:18 AM

Strange, that seems to clear the problem, had to rearrange a few other columns, but seems ok, Will have to test it a bit more.

But still cant find out why it decided to stop working, as i said its been working fine, even witht eh event coding...?

Bloody Mircosoft and excel .. lol

thanks very much for the help

G