Prevent recursive procedure call - Copy activecell to selection 1

[Extinct]

Hi,

My question is 2-fold.

What I want is when I make a selection on a worksheet and then enter a value in the active cell that the value is automaically copied to the other cells of the selection.

I tried to do this with a procedure. Here is the code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Row > 1 And Target.Column > 1 And Sh.Index >= 3 And Sh.Index <= 9 And bUserchange Then

For Each c In Selection.Cells
c.Value = ActiveCell.Value
Next c

End If
End Sub

The problem is that the line
c.Value = ActiveCell.Value
again calls this procedure which results in an infinite loop of calling the same procedure from within itself.

So if anyone can tell me how to prevent this &quot;internal&quot; procedure call or any other way to adapt my code or another way to copy the data from the activecell to the other cells of the selection I would be very thankfull

 

[Extinct]

Yes, I found a solution, but if anyone has an easier way to copy data from the activecell to the selection let me know.

FYI here is the adapted code which works

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Row > 1 And Target.Column > 1 And Sh.Index >= 3 And Sh.Index <= 9 Then
If (Target.Row = ActiveCell.Row And Target.Column = ActiveCell.Column) Then
For Each c In Selection.Cells
If (c.Row <> ActiveCell.Row Or c.Column <> ActiveCell.Column) Then
c.Value = ActiveCell.Value
End If
Next c
End If
End If
End Sub

 

[JohnYingling]

To prevent recursion

Private Sub .....
Static blnbusy as boolean
if blnBusy then exit sub
blnbusy = True
...... do your thing
blnBusy = false
End Sub

http://www.VBResizer.com

Generate Forms/Controls Resizing/Tabbing Class

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[Extinct]

The Final procedure. The previous one was quite slow. Thanks for the tip JuohnYingling

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Static blnbusy As Boolean
If blnbusy Then Exit Sub
blnbusy = True
If Target.Row > 1 And Target.Column > 1 And Sh.Index >= 3 And Sh.Index <= 9 Then

Selection.FillRight
Selection.FillDown

End If
blnbusy = False
End Sub

 

[acron]

If you have a range of cells selected, you can enter a value in all of thme at once by making th entry using Ctrl-Shift Enter at th esame time, instead of just Enter.

A.C.

 

[DaleWatson123321]

A.C.

Good tip. Thanks, and a ===>STAR

Extinct,

Here's an &quot;assist&quot;...

Sub Fill_Range()
var1 = Range(&quot;num1&quot.Value
ActiveCell.Value = var1
Selection.FormulaArray = ActiveCell.Value
End Sub

...where &quot;num1&quot; is a named cell from where you pick up your variable to enter, first into the ActiveCell and then into the currently selected range.

Hope this help. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Loomah]

Extinct
Although you have your solution, for future reference if you add the following lines it should do away with your recurring loop

Application.EnableEvents = False
'do your bits here!
Application.EnableEvents = True

To add to Acron's post, if you run ot of fingers, CTRL+ENTER will have the same effect, though it isn't exactly the same thing! Can't explain as arrays are involved!!

;-)
Happy Friday
If a man says something and there are no women there to hear him, is he still wrong?
&quot;...Three Lions On A Shirt...&quot;