Protection/Cell Locking...can't copy... aaarrgh!!

[Dubbledex]

Hi anyone,

I have been stuck for ages with a VBA excel conundrum. If anyone can help, that would be amazing, as I have run out of ideas

Basically, I have created an equipment list form that fits onto 1 page.
Sometimes, there is a lot of equipment, so it needs to be extended. I created a macro that copies a line, and pastes the whole lot down, and it seems to work fine.

Now, here is the issue!
I don't want people to be able to accidently delete the formula in some cells, so I used the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rng As Range
For Each rng In Target.Cells
If rng.HasFormula Then
Activesheet.Protect Password:="lt5t5", UserInterfaceOnly:=True
Exit Sub

Else
'Activesheet.Protect


End If
Next rng
End Sub


But when this is running, because one of the cells that is getting copy/pasted down has formula, it doesn't copy and paste the formula down.

I also tried unlocking the cell in format cell protection, but it doesn't seems to help.

I also tried to unlock the activesheet range just before extending it:


Sub extend_equipment_list()
Activesheet.Range("H2").Value = "2"
If Activesheet.Range("H3").Value = "1" Then
Else
Activesheet.Range("A22:G22").Locked = False
Activesheet.Range("A22:G22").Select
Selection.Copy
Activesheet.Range("A22:G56").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Activesheet.Range("A22").Select
Activesheet.Range("H3").Value = "1"

'ActiveSheet.Range("F22").Formula = "=D22*E22"
'Range("F22").AutoFill Destination:=Range("F23:F57")

Activesheet.PageSetup.PrintArea = "A1:G67"
With Activesheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 2
End With



End If
End Sub


Does anyone have any ideas how I can get around this issue?

Thanks!

Dubbledex

 

[Datsmart]

You can add some lines of code to your "Worksheet_SelectionChange" code to only work with one cell at a time:

    '** Only look at single cell changes
    If Target.Count > 1 Then Exit Sub

Better yet, avoid "Selecting" in your "extend_equipment_list" code altogether, then your event code will not fire.
Hard to make suggestions for that code, not able to determine exactly what it is doing.
It is inserting cells in rows 22 to 56. Will it always be those rows?