Excel Stops Responding when running this simple code

[Ritzyy]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim row As Range
Dim cell As Range

myvar = "A1:A" & ThisWorkbook.Sheets("Sheet3").Range("A5") - ThisWorkbook.Sheets("Sheet3").Range("A3")
For Each row In Range(myvar).Rows
For Each cell In row.Cells
cell = 1
Next cell
Next row

End Sub




I can't work out what I've done wrong... sometimes I get an error wiht the myvar line.. other times it runs fine but breaks excel.

all it's trying to do is insert 1 in 94 rows. (ThisWorkbook.Sheets("Sheet3").Range("A5") - ThisWorkbook.Sheets("Sheet3").Range("A3") = 94)

 

[Damon Ostrander]

Hi Ritzyy,

Try turning off events so it doesn't call itself recursively each time it sets a cell to 1:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim row As Range
Dim cell As Range

Application.EnableEvents = False
myvar = "A1:A" & ThisWorkbook.Sheets("Sheet3").Range("A5") - ThisWorkbook.Sheets("Sheet3").Range("A3")
For Each row In Range(myvar).Rows
For Each cell In row.Cells
cell = 1
Next cell
Next row
Application.EnableEvents = True

End Sub

 

[Damon Ostrander]

Hi again Ritzyy,

Just a suggestion. Here is a little simpler way of accomplishing the same thing:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim WS3        As Worksheet
   Dim iRow       As Long
   Dim LastRow    As Long
   
   Set WS3 = ThisWorkbook.Sheets("Sheet3")
   
   LastRow = WS3.Range("A5") - WS3.Range("A3")
   
   Application.EnableEvents = False
   For iRow = 1 To LastRow
      Cells(iRow, "A") = 2
   Next iRow
   Application.EnableEvents = True
   
End Sub

Damon

 

[Ritzyy]

Hi again Ritzyy,

Just a suggestion. Here is a little simpler way of accomplishing the same thing:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim WS3        As Worksheet
   Dim iRow       As Long
   Dim LastRow    As Long
   
   Set WS3 = ThisWorkbook.Sheets("Sheet3")
   
   LastRow = WS3.Range("A5") - WS3.Range("A3")
   
   Application.EnableEvents = False
   For iRow = 1 To LastRow
      Cells(iRow, "A") = 2
   Next iRow
   Application.EnableEvents = True
   
End Sub

Damon

You're a genius mate. Thanks!
I'm still relatively new to this whole excel macro thing.

With events and stuff, I just had worksheet selection change as an example but i've been meaning to make it so when another worksheet in the same workbook changes a certain cell value this will update itself. Is there a way to have an event condition for this worksheet which relies on eg. A5 from Sheet2 changing value?

Thanks again man

 

[Damon Ostrander]

Event condition relies on e.g. A5.

Hi again Ritzyy,

Yes, this is what the Target parameter is for. Target is the range that was changed and caused the Worksheet Change event to fire. For example, if you want your previous event to fire only if A5 changes value the code would be

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim WS3        As Worksheet
   Dim iRow       As Long
   Dim LastRow    As Long

   If Target.Address <> "$A$5" Then Exit Sub
   
   Set WS3 = ThisWorkbook.Sheets("Sheet3")
   
   LastRow = WS3.Range("A5") - WS3.Range("A3")
   
   Application.EnableEvents = False
   For iRow = 1 To LastRow
      Cells(iRow, "A") = 1
   Next iRow
   Application.EnableEvents = True
   
End Sub

Of course this code would turn right around and set the value of A5 to 1, which probably isn't what you want. Also you mentioned A5 in Sheet2. I'm assuming this code resides in Sheet2's code module--otherwise it wouldn't fire Sheet2's Change event. If you want a worksheet Change event to fire no matter what sheet is changed you must place it in the ThisWorkbook code module, and in your code you should check which sheet the change occurred to as well as which cell.

I hope this helps.

Damon

You're a genius mate. Thanks!
I'm still relatively new to this whole excel macro thing.

With events and stuff, I just had worksheet selection change as an example but i've been meaning to make it so when another worksheet in the same workbook changes a certain cell value this will update itself. Is there a way to have an event condition for this worksheet which relies on eg. A5 from Sheet2 changing value?

Thanks again man