Hi all,
The code below works perfectly when run on the active sheet ("Page4+") but when run outside of the active sheet it freezes up my workbook, Does anyone have any ideas why the code might be causing the program to freeze up & if so how to clean it up? Thanks in advance.
P.S. Or maybe an alternative code for copying a "namedrange" from a checked checkbox & deleting that same range if unchecked?
Note: This thread has also been posted on "Excel Forum" - Sorry not allowed to post links :confused2:
Private Sub CheckBox1_Click()Dim NR As LongDim rngFND As RangeWith Sheets("Page4+") Sheets("Page4+").Unprotect Password:="000" Sheets("Test").Unprotect Password:="000" If CheckBox1 = True Then NR = .Cells(.Rows.Count, "G").End(xlUp).Row + 1 Sheets("Test").Range("DryLaidBlocks").Copy .Range("A" & NR) Else On Error Resume Next Set rngFND = .Cells.Find("DRY LAID BLOCKS", LookIn:=xlValues, LookAt:=xlWhole) If Not rngFND Is Nothing Then rngFND.Resize(20).EntireRow.Delete xlShiftUp Else Sheets("Page4+").Protect Password:="000", DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Test").Protect Password:="000", DrawingObjects:=True, Contents:=True, Scenarios:=True MsgBox "Source Not Found?" End If End IfEnd WithEnd Sub
The code below works perfectly when run on the active sheet ("Page4+") but when run outside of the active sheet it freezes up my workbook, Does anyone have any ideas why the code might be causing the program to freeze up & if so how to clean it up? Thanks in advance.
P.S. Or maybe an alternative code for copying a "namedrange" from a checked checkbox & deleting that same range if unchecked?
Note: This thread has also been posted on "Excel Forum" - Sorry not allowed to post links :confused2:
Private Sub CheckBox1_Click()Dim NR As LongDim rngFND As RangeWith Sheets("Page4+") Sheets("Page4+").Unprotect Password:="000" Sheets("Test").Unprotect Password:="000" If CheckBox1 = True Then NR = .Cells(.Rows.Count, "G").End(xlUp).Row + 1 Sheets("Test").Range("DryLaidBlocks").Copy .Range("A" & NR) Else On Error Resume Next Set rngFND = .Cells.Find("DRY LAID BLOCKS", LookIn:=xlValues, LookAt:=xlWhole) If Not rngFND Is Nothing Then rngFND.Resize(20).EntireRow.Delete xlShiftUp Else Sheets("Page4+").Protect Password:="000", DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Test").Protect Password:="000", DrawingObjects:=True, Contents:=True, Scenarios:=True MsgBox "Source Not Found?" End If End IfEnd WithEnd Sub
Last edited: