Pesky Autofilter Toggle
 

Help for a VBA newbie – I am taking two databases and using filters t
sort and copy data to one of 24 worksheets depending on filte
criteria. Seems to be going ok but would appreciate some advice o
autofilters. Am currently selecting row then turning on autofilter
however this seems to toggle the filter (if its already on then thi
turns it off & vice versa) so if an autofliter has been left o
somewhere in workbook its back to debugger. How do I switch it of
rather than toggle off ?

I am also using this macro to set the print area for each of my 2
worksheets by first estimating the likely number of rows (columns ar
fixed) but as my database grows I am mindful that I will need to kee
resetting this value. Can I use the COUNTA function to count rows s
that my set print area is more dynamic ?

Finally, was wondering if there is a way in VBA to search for a cel
value and make that cell really stand out (perhaps flash) ? Am usin
conditional formatting (three conditions) for row colours but nee
another condition to highlight specific cell entries.

Many thanks
D:-

--
Digor
-----------------------------------------------------------------------
Digory's Profile: http://www.excelforum.com/member.php...fo&userid=1643
View this thread: http://www.excelforum.com/showthread.php?threadid=27803

Pesky Autofilter Toggle
 

FILTER - this works for me:
MyTab = ActiveSheet.Name
Set w = Worksheets(MyTab)
'if autofilter is on, it will turn if off otherwise it leaves it on
If w.AutoFilterMode Then
Selection.AutoFilter
End If

PRINT AREA - ROWS:
LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

CONDITIONAL FORMATING:
No specific formual but you could set the conditional formating on an
adjacent cell to set background/forecolor based on the value in the cited
cell.

Glen

"Digory" wrote in message
...

Help for a VBA newbie - I am taking two databases and using filters to
sort and copy data to one of 24 worksheets depending on filter
criteria. Seems to be going ok but would appreciate some advice on
autofilters. Am currently selecting row then turning on autofilter -
however this seems to toggle the filter (if its already on then this
turns it off & vice versa) so if an autofliter has been left on
somewhere in workbook its back to debugger. How do I switch it off
rather than toggle off ?

I am also using this macro to set the print area for each of my 24
worksheets by first estimating the likely number of rows (columns are
fixed) but as my database grows I am mindful that I will need to keep
resetting this value. Can I use the COUNTA function to count rows so
that my set print area is more dynamic ?

Finally, was wondering if there is a way in VBA to search for a cell
value and make that cell really stand out (perhaps flash) ? Am using
conditional formatting (three conditions) for row colours but need
another condition to highlight specific cell entries.

Many thanks
D:-)


--
Digory
------------------------------------------------------------------------
Digory's Profile:
http://www.excelforum.com/member.php...o&userid=16439
View this thread: http://www.excelforum.com/showthread...hreadid=278036

Pesky Autofilter Toggle
 

Both work for me

Testing for autofiltermode test if the "dropdown arrows are visible..
(programmatically you can set an autofilter without showing the dropdowns)

Thus you'll need to TEST on the filtermode.

If ActiveSheet.FilterMode Then
ActiveSheet.FilterMode = False
End If


To remove the SHEET's autofilter you COULD also call
RANGE's autofilter method with NO argument

Range("a1").autofilter




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Digory wrote in message
:


Help for a VBA newbie – I am taking two databases and using filters to
sort and copy data to one of 24 worksheets depending on filter
criteria. Seems to be going ok but would appreciate some advice on
autofilters. Am currently selecting row then turning on autofilter –
however this seems to toggle the filter (if its already on then this
turns it off & vice versa) so if an autofliter has been left on
somewhere in workbook its back to debugger. How do I switch it off
rather than toggle off ?

I am also using this macro to set the print area for each of my 24
worksheets by first estimating the likely number of rows (columns are
fixed) but as my database grows I am mindful that I will need to keep
resetting this value. Can I use the COUNTA function to count rows so
that my set print area is more dynamic ?

Finally, was wondering if there is a way in VBA to search for a cell
value and make that cell really stand out (perhaps flash) ? Am using
conditional formatting (three conditions) for row colours but need
another condition to highlight specific cell entries.

Many thanks
D:-)

Pesky Autofilter Toggle
 

pesky editing..

you'll need to TEST on filtermode but SET sheet's autofiltermode...
(filtermode is readonly)

If ActiveSheet.FilterMode Then
ActiveSheet.AutoFilterMode = False
End If


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

keepITcool wrote in message
:

Both work for me

Testing for autofiltermode test if the "dropdown arrows are visible..
(programmatically you can set an autofilter without showing the
dropdowns)

Thus you'll need to TEST on the filtermode.

If ActiveSheet.FilterMode Then
ActiveSheet.FilterMode = False
End If


To remove the SHEET's autofilter you COULD also call
RANGE's autofilter method with NO argument

Range("a1").autofilter




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Digory wrote in message
:


Help for a VBA newbie – I am taking two databases and using filters
to sort and copy data to one of 24 worksheets depending on filter
criteria. Seems to be going ok but would appreciate some advice on
autofilters. Am currently selecting row then turning on autofilter –
however this seems to toggle the filter (if its already on then this
turns it off & vice versa) so if an autofliter has been left on
somewhere in workbook its back to debugger. How do I switch it off
rather than toggle off ?

I am also using this macro to set the print area for each of my 24
worksheets by first estimating the likely number of rows (columns are
fixed) but as my database grows I am mindful that I will need to keep
resetting this value. Can I use the COUNTA function to count rows so
that my set print area is more dynamic ?

Finally, was wondering if there is a way in VBA to search for a cell
value and make that cell really stand out (perhaps flash) ? Am using
conditional formatting (three conditions) for row colours but need
another condition to highlight specific cell entries.

Many thanks
D:-)

Pesky Autofilter Toggle
 

Activesheet.autofiltermode = false

will turn autofilter off if it's on and won't do anything if it's already off.

If you're using a macro to set the print area, you could use something like:

dim LastRow as long
dim wks as worksheet

for each wks in activeworkbook.worksheets
with wks
lastrow = .cells(.rows.count,"A").end(xlup).row
.pagesetup.printarea = .range("a1:x" & lastrow).address(external:=true)
end with
next wks

But if you don't specify any printarea, doesn't excel keep good track of what
should be printed for you?


If you're using conditional formatting, then those conditional formats will
"beat" any "normal" formatting you've put on the cell. And since you only get 3
CF's, I think you'll have to use an alternative approach.

Maybe you could apply the formatting inside some event (worksheet_change or
worksheet_calculate)???

Or maybe you could find the cell in your macro and then just issue a msgbox with
some sort of warning in it??



Digory wrote:

Help for a VBA newbie – I am taking two databases and using filters to
sort and copy data to one of 24 worksheets depending on filter
criteria. Seems to be going ok but would appreciate some advice on
autofilters. Am currently selecting row then turning on autofilter –
however this seems to toggle the filter (if its already on then this
turns it off & vice versa) so if an autofliter has been left on
somewhere in workbook its back to debugger. How do I switch it off
rather than toggle off ?

I am also using this macro to set the print area for each of my 24
worksheets by first estimating the likely number of rows (columns are
fixed) but as my database grows I am mindful that I will need to keep
resetting this value. Can I use the COUNTA function to count rows so
that my set print area is more dynamic ?

Finally, was wondering if there is a way in VBA to search for a cell
value and make that cell really stand out (perhaps flash) ? Am using
conditional formatting (three conditions) for row colours but need
another condition to highlight specific cell entries.

Many thanks
D:-)

--
Digory
------------------------------------------------------------------------
Digory's Profile: http://www.excelforum.com/member.php...o&userid=16439
View this thread: http://www.excelforum.com/showthread...hreadid=278036

--

Dave Peterson