CODEWITHSUNDEEP
excelexcel-formulavba
Jon white
Jon white

Reputation: 11

worksheet change event only works when region selected - how to adjust to automatic update

the combination of this sub in a module

Sub hithere3()
Dim Rng As Range
Dim Unique As Boolean

For Each Rng In Worksheets("Sheet8").Range("FS3:FS30") 'for each cell     in     your B1 to B30 range, sheet1
Unique = True 'we'll assume it's unique
Lastunique = Worksheets("TRADES").Range("C:C").Find("*",     SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 3 To Lastunique 'for each cell in the unique ID cache
    If Rng.Value = Worksheets("TRADES").Cells(i, 3).Value Then 'we       check    if it is equal
        Unique = False 'if yes, it is not unique
    End If
Next
If Unique Then Worksheets("TRADES").Cells(Lastunique + 1, 3) = Rng 'adds    if it is unique
Next
End Sub

with the loop check in a worksheet change events

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("FS3:FS33")) Is Nothing Then
'Do nothing '
Else
Call hithere3
End If
End Sub

works except it only updates when I select one of the cells in FS3:FS33

Can anyone suggest how this can be overcome?

maybe with a workchange change range selection type from below?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range, n As Long
Dim RngB As Range, RngC As Range
If Target.Column = 2 And Target.Count = 1 And Target.Row > 1 Then
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Set RngB = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
Set RngC = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
ray = Array(RngB, RngC)
For n = 0 To 1
    For Each Dn In ray(n)
        If Not Dn.Address(0, 0) = "C1" And Not Dn.Value = "" Then
            .Item(Dn.Value) = Empty
        End If
    Next Dn
Next n
Range("C2").Resize(.Count) = Application.Transpose(.Keys)
End With
End If

Upvotes: 0

Views: 120

Answers (4)

Jon white
Jon white

Reputation: 11

Finally figured it out, the following code works :

Private Sub Worksheet_calculate()
If Range("FS3:FS33") Is Nothing Then
'Do nothing'
Else
Call hithere3
End If
End Sub

Upvotes: 0

kolcinx
kolcinx

Reputation: 2233

Private Sub Worksheet_Change(ByVal Target As Range) '<<delete the "Selection" from the name of event
If Intersect(Target, Range("FS3:FS33")) Is Nothing Then
'Do nothing '
Else
Call hithere3
End If
End Sub

Upvotes: 0

Kyle
Kyle

Reputation: 2545

If Intersect(Target, Range("FS3:FS33")) Is Nothing is the culprit. You must change Range("FS3:FS33") to whatever range you want to affect this change.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

Use either the worksheet Calculate event or the worksheet Change event:

  1. use Calculate if the range contains formulas
  2. use Change if the cells in the range are changed manually

Upvotes: 1

Related Questions