Worksheet_Change not helping 2

[shavenlunatic]

Hi,

I'd like to start by thanking everyone here who has helped me out in the past, you guys have been a massive help to me over the past year or so. And now I'm back for more!

basically, I have an excel sheet with a validation listbox

the sequence of events I want to happen are:

listbox on sheet1 (C3) changes - causing a cell next to the listbox to change to the relevant row number (this part works fine)

script runs and makes sheet1.cells(y, x) = sheet2.cells(y, x) (this bit is a bit sketchy as I'll explain below)


I used this basic script:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Address = "$C$3" Then
    rownum = Sheet1.Cells(4, 7)
    Sheet1.Cells(22, 8) = Sheet2.Cells(rownum, 11)
    Sheet1.Cells(27, 8) = Sheet2.Cells(rownum, 12)
    Sheet1.Cells(32, 8) = Sheet2.Cells(rownum, 13)
    Sheet1.Cells(37, 8) = Sheet2.Cells(rownum, 14)
    Sheet1.Cells(52, 4) = Sheet2.Cells(rownum, 15)
    Sheet1.Cells(63, 4) = Sheet2.Cells(rownum, 16)
    Sheet1.Cells(65, 4) = Sheet2.Cells(rownum, 17)
End If    
End Sub

The above code goes crazy due to the fact taht as soon as it hits the second line in the If statement, it re-activates the Worksheet_Change sub and takes about 2 mins to run through before it eventually gets bored.

Now, if I place

Sheet1.Cells(1, 1).Activate

in the first line of the if statement, it speeds it up but it obviously still activates the worksheet_Change sub on each line, it just doesn't step through the If statement so it is un-noticeable as far as speed is concerned but stepping through it you can see it jumping around allover the place.

I know this is a truly terrible way of doing it and I am sure there is a more sensible way of working it (I am even convinced that I have done it before.. but my memory fails me)

Any advice?


________
clueless

 

[PHV]

Have a look at the Application.EnableEvents property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shavenlunatic]

ah.. never thought of that.. so simple now you've pointed it out.

Cheers PHV

________
clueless

 

[stefen054]

hi

use the below code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Exit
Application.EnableEvents = False
If Target.Address = "$C$3" And Target.Cells.Count < 2 Then
Sheet1.Cells(1, 1).Select
rownum = Sheet1.Cells(4, 7)
Sheet1.Cells(22, 8) = Sheet2.Cells(rownum, 11)
Sheet1.Cells(27, 8) = Sheet2.Cells(rownum, 12)
Sheet1.Cells(32, 8) = Sheet2.Cells(rownum, 13)
Sheet1.Cells(37, 8) = Sheet2.Cells(rownum, 14)
Sheet1.Cells(52, 4) = Sheet2.Cells(rownum, 15)
Sheet1.Cells(63, 4) = Sheet2.Cells(rownum, 16)
Sheet1.Cells(65, 4) = Sheet2.Cells(rownum, 17)
End If
Application.EnableEvents = True
Exit Sub

Err_Exit:
Application.EnableEvents = True
End Sub

Stefen