0

I have a bunch of range names (mostly single cells) in a worksheet. When I change the cell named "Grade" I want its value to appear in another range called "GrdSrchSttng" (6 x 1 range) as the last value. However to do that I need to know that the particular cell named "Grade" changed. The solution to this question does not work.

I tried this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isIn As Boolean
Dim i As Integer
Dim r As Range


Set r = ActiveSheet.Range("GrdSrchSttng")

    If Target.Name.Name = "Search!Grade" Then ' <== this si the line I have the issue with
        Select Case Target.Value
            Case "All"
                r.ClearContents
                r.Cells(1, 1).Value = "All"
            Case ""
                r.ClearContents
                Target.Value = "All"
                r.Cells(1, 1).Value = "All"
            Case Else
                If r(1, 1).Value = "All" Then
                    r.ClearContents
                End If

                i = 1
                Do While r(i, 1).Value <> ""
                    If r(i, 1).Value = Target.Value Then
                        isIn = True
                    End If
                    i = i + 1
                Loop

                If Not isIn Then
                    r(i, 1).Value = Target.Value
                End If
        End Select
    End If
End Sub
Community
  • 1
  • 1
PBD10017
  • 1,031
  • 2
  • 14
  • 22

1 Answers1

1

Personally, using Target.Name.Name is a bit tricky. I prefer using a workaround for this instead, as it achieves basically the same result. Unless you specifically want to track the name of the named range, I suggest doing something like follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Whatever = Range("Grade").Address
    If Target.Address = Whatever Then
        Range("GrdSrchSttng").Cells(1, 6).Value = Target.Value
    End If
End Sub

Screenshots:

Set-up:

enter image description here

Result when Grade is edited:

enter image description here

Let us know if this is amenable or otherwise. :)

WGS
  • 13,969
  • 4
  • 48
  • 51
  • Hi, thanks, nice workaround. I decided to use this one instead of my "next cell checking" as I like that better. Still don't understand why the .name.name did not work. A bit frustrated with that. But anyway, thanks a lot. – PBD10017 Jan 14 '14 at 07:26
  • @BK201 i have the same approach, but during testing i don't get the desired result. Nothing is happening on the sheet. So i didn't post it as answer. Any idea why? :) – L42 Jan 14 '14 at 07:44
  • @L42: `1` Did you add it to the sheet code area? A bit trivial and beyond you but you never know. ;) `2` Can you please post your exact code here as well? :) – WGS Jan 14 '14 at 10:43
  • @BK201 haha im flattered. But yeah, it's on a sheet code. I restarted excel and it works. I just thought i'm missing something. – L42 Jan 14 '14 at 12:41