name_range auto filter
 

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
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

name_range auto filter
 

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



driller wrote:

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
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

--

Dave Peterson

name_range auto filter
 

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" wrote:

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



driller wrote:

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
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

--

Dave Peterson

name_range auto filter
 

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.

driller wrote:

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" wrote:

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



driller wrote:

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
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

--

Dave Peterson


--

Dave Peterson

name_range auto filter
 

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



"Dave Peterson" wrote:

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.

driller wrote:

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" wrote:

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



driller wrote:

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
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

--

Dave Peterson


--

Dave Peterson