name_range auto filter

[Guest]

Hello again,

Is it possible by code, in a specific workbook, that everytime i go and
click/select on the name box, an auto-filter will be ready-made on the
selected name

- only if the selected name refers to a contiguous range of cells (e.g. 5
rows x 16 columns)...otherwise, no need to filter...

really appreciate your bright ideas..

regards,
driller

 

[Dave Peterson]

Maybe...

You could use a workbook_sheetselectionchange event (under ThisWorkbook) that
looks at the range and decides what to do. I think you'd have to loop through
all the names in the workbook to see if the name that was chosen matched the
range that got selected.

This is behind ThisWorkbook:

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Dim myName As Name
Dim TestRng As Range

If Target.Cells.Count = 1 Then Exit Sub
If Target.Areas.Count > 1 Then Exit Sub
If Target.Columns.Count = 1 Then Exit Sub
If Target.Rows.Count = 1 Then Exit Sub

For Each myName In Me.Names
Set TestRng = Nothing
On Error Resume Next
Set TestRng = myName.RefersToRange
On Error GoTo 0

If TestRng Is Nothing Then
'keep looking
Else
If TestRng.Address(external:=True) _
= Target.Address(external:=True) Then
'sounds like it's a match
Sh.AutoFilterMode = False
Target.AutoFilter
'Stop looking
Exit For
End If
End If
Next myName

End Sub

 

[Guest]

really thanks Sir Dave,

I paste the exact code..and i tested...i have two named range named "rang1"
and "rang2"...then select in the name box...nothing happens...i dont know
what im doing wrong...thanks for your time and effort..

regards,
driller
--
regards,
driller

*****
- dive with Jonathan Seagull

 

[Dave Peterson]

It worked for me with global and local names.

Maybe it's time for you to describe what you did and give more details about the
ranges.

 

[Guest]

Thank you Sir Dave,

Definitely i need more tests...
we may relax now about the workbook(s),

your kind effort i appreciate...

--
regards,
driller

*****
- dive with Jonathan Seagull