| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
| Excel 2007 Auto Filter Filter | Excel Discussion (Misc queries) | |||
| Limit filter options in Auto Filter | Excel Discussion (Misc queries) | |||
| MIN with name_range? | Excel Worksheet Functions | |||
| Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
Hybrid Mode
