Worksheet Procedure question

[Noz2k]

So I have a bit of code which currently runs on the worksheet_selectionchange procedure, which updates the values in a form with the values from specified worksheet.

The worksheet to look for is dictated by the value in "E3". However I want this data to be amendable, so I only want it to change when "E3" is changed, not when there is a selection change anywhere in the worksheet.

Where do I put the code in order to make it so that it only runs when "E3" is changed.

 

[Peter_SSs]

You put it in the sheet module (same as the Worksheet_SelectionChange code). Without knowing specific details of your code, it could go something like this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E3")) Is Nothing Then
        'other code here
    End If
End Sub

One thing to be careful of is if your code makes changes to the sheet, your code needs to disable 'events' while it runs so that the code doesn't keep re-calling itself.

 

[littleiitin]

I am sorry Peter has already answered.

 

[Noz2k]

One thing to be careful of is if your code makes changes to the sheet, your code needs to disable 'events' while it runs so that the code doesn't keep re-calling itself.

How do I do this, because I think I already made this error before reading your post, and I now can't select any cells in my workbook.

 

[Noz2k]

Also here is the code I am trying to run

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsf         As Worksheet, _
    p           As String, _
    ste         As Range, _
    ste1        As Long, _
    rng         As Range, _
    rng1        As Long
    
Set wsf = Sheets("FSheet")
p = "Week" & wsf.Range("E5")

With Sheets(p).Range("A3:AE200")
Set ste = .Find(wsf.Range("E3"))
ste1 = ste.Column
End With

Y = 7
Do Until Y = 29
On Error Resume Next
With Sheets(p).Range("A1:A200")
Set rng = .Find(wsf.Range("A" & Y))
rng1 = rng.Row
End With
wsf.Range("E" & Y).Value = Sheets(p).Cells(rng1, ste1).Value

Y = Y + 2
 
Loop
Y = 7
With Sheets(p).Range("A3:AE200")
Set ste = .Find(wsf.Range("E3"))
ste1 = ste.Column
End With
Do Until Y = 27
On Error Resume Next
With Sheets(p).Range("A1:A200")
Set rng = .Find(wsf.Range("G" & Y))
rng1 = rng.Row
End With
wsf.Range("K" & Y).Value = Sheets(p).Cells(rng1, ste1).Value
Y = Y + 2

Loop
End Sub

 

[Hermanito]

Basically, you would need to add the red lines to what Peter already gave you:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E3")) Is Nothing Then
       [COLOR=red] Application.EnableEvents = False
[/COLOR]        'other code here
        [COLOR=red]Application.EnableEvents = True
[/COLOR]    End If
End Sub

 

[Noz2k]

Doesn't seem to be working (Note, in this example it's when E5 changes that the code should run, it's not a mistake)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsf         As Worksheet, _
    p           As String, _
    ste         As Range, _
    ste1        As Long, _
    rng         As Range, _
    rng1        As Long
 
Set wsf = Sheets("FSheet")
If Not Intersect(Target, Range("E5")) Is Nothing Then
Application.EnableEvents = False
p = "Week" & wsf.Range("E5")
 
With Sheets(p).Range("A3:AE200")
Set ste = .Find(wsf.Range("E3"))
ste1 = ste.Column
End With
 
Y = 7
Do Until Y = 29
On Error Resume Next
With Sheets(p).Range("A1:A200")
Set rng = .Find(wsf.Range("A" & Y))
rng1 = rng.Row
End With
wsf.Range("E" & Y).Value = Sheets(p).Cells(rng1, ste1).Value
 
Y = Y + 2
 
Loop
Y = 7
With Sheets(p).Range("A3:AE200")
Set ste = .Find(wsf.Range("E3"))
ste1 = ste.Column
End With
Do Until Y = 27
On Error Resume Next
With Sheets(p).Range("A1:A200")
Set rng = .Find(wsf.Range("G" & Y))
rng1 = rng.Row
End With
wsf.Range("K" & Y).Value = Sheets(p).Cells(rng1, ste1).Value
Y = Y + 2
Loop
Application.EnableEvents = True
End If
End Sub

 

[Hermanito]

What exactly doesn't seem to be working?

Can you describe in detail what you are trying to do, because trying to follow your code is making my head spin...

 

[Peter_SSs]

I haven't studied your code but if it isn't firing at all, it could be that your 'events' have become disabled. In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter. Then try making changes in your sheet again.

 

[Noz2k]

Tried but no luck, other bits of code are still working.

Basically my work book has a number of sheets named "week1" through to "week52".

The cell in "E5" refers to the week number, and then uses this to set the sheet(P). In other cells on the sheets are different row headings. The code uses these headings to search. for the appropriate row in SheetP, this is the rng variable.

"E3" is a combobox with the 10 column headings in it. The ste variable is used to find the appropriate columns in sheet(P)

It then uses both rng and ste to (row and column) to find the specific cell, and then posts this value into the appropriate box in FSheet.


However I need this procedure to call whenever cell E3 or cell E5 change, because they are then refering to either a different sheet or a different column in the sheet.

Essentially having this on the selection change procedure earlier effectively did this, however I also have a button at the bottom of the page which does the reverse and updates the week sheet. So I need to be able to amend what gets called back, which is why I need to make the change only when E3 or E5 specifically are changed