About Us

Dave Peterson

Glad you found a way!

JeffK wrote:

me again Dave, Don't worry about this post :)

I've never used a form button before but figured it out to solve this
problem. I recorded a macro to unprotect and Show all then protect it once
again. Then inserted a form button a the top and programed is using the new
macro.

Somehow it works :)

"JeffK" wrote:

Hi Dave,

Sorry to keep drawing from the well but you've been very helpful and my next
question refers to the autofilter you've helped me setup.

I'm using Excel 2003 which allows you to let users access autofilter when
the file is protected. I would like the users the ability to "show All" in
case they need to adjust a previous entry that's been filtered.

The problem is when the file is protected, "Use Autofilter" is selected, the
option from the Date-Filter-Autofiler and Show All are grey'd out.

I don't want the users the ability to unprotect the sheet.

Any suggestions

"Dave Peterson" wrote:

You may want to consider adding another column that contains a formula that
returns a true/false. Then you could make that formula as complex as you want
and filter on that column.

Or you could consider using advanced filter.

If you want to try it manually, Debra Dalgleish has a bunch of notes starting
he
http://contextures.com/xladvfilter01.html

If you want to try it via code:

Option Explicit
Sub Auto_Open()

Dim wks As Worksheet
Dim TempWks As Worksheet
Dim RngToFilter As Range
Dim CritRange As Range
Dim LastRow As Long

'change the name to what you need.
Set wks = Worksheets("SheetNameHere")

Set TempWks = Worksheets.Add

With wks
'remove any existing filter arrows
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set RngToFilter = .Range("A7", .Cells(LastRow, "A"))
End With

With TempWks
'set up criteria range
.Range("A1").Value = wks.Range("a7").Value
.Range("a2").Formula = "=""="""
.Range("a3").Value = "In progress"
.Range("a4").Value = "Approved"
Set CritRange = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

RngToFilter.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=CritRange, _
copytorange:="", _
Unique:=False

Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True

End Sub

JeffK wrote:

That's too bad, thanks for all your help.

"Dave Peterson" wrote:

If you're using xl2003 or below, then you only get 2 criteria. I'm not sure
about xl2007.

So you could change the criteria2 parm to use "approved" instead of blanks.

JeffK wrote:

I want to add "approved" to the list for sorting. I've tried adding to the
macro but I keep getting it wrong:

RngToFilter.AutoFilter field:=1, Criteria1:="In Progress", _
Operator:=xlOr, Criteria2:=""

Could you help me a bit further.

Much appreciated.

"Dave Peterson" wrote:

These two lines determine the extent to filter:

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

So if you wanted to use column Z to determine the last row, you could use:

LastRow = .Cells(.Rows.Count, "Z").End(xlUp).Row

And if you wanted to use row 4 to determine the last column:
LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column

And since your data starts in row 8, then the last header row will be 7, so
you'd have to use this line:

Set RngToFilter = .Range("A7", .Cells(LastRow, LastCol))

JeffK wrote:

Thanks for the links, will be spending some time with those.

"I also determined the range to filter by looking at entries in column A and
row
1. If you have to use a different column or row, change those lines with
..end(xlup) and .end(xltoleft)."

My data starts in A8 with column A having the values that will be filtered
but I don't know what to change in your instruction above.

I copies the code as is to the workbook but nothing happens when I open the
file.

"Dave Peterson" wrote:

In xl2003 menus.

Tools|Macro|Record new macro
Type in: Auto_Open
in the macro name
and choose to store macro: "in this workbook"

You'd end up with code that would probably work--but may fail some times.

Instead of that, how about using this:

Option Explicit
Sub Auto_Open()

Dim wks As Worksheet
Dim RngToFilter As Range
Dim LastRow As Long
Dim LastCol As Long

'change the name to what you need.
Set wks = Worksheets("SheetNameHere")

With wks
'remove any existing filter arrows
.AutoFilterMode = False

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set RngToFilter = .Range("A1", .Cells(LastRow, LastCol))

RngToFilter.AutoFilter field:=1, Criteria1:="In Progress", _
Operator:=xlOr, Criteria2:=""
End With
End Sub

You'll have to put the code into your workbook's project. And change the name
of the worksheet.

You'll notice that I removed any existing filter arrows and applied them where I
wanted--just in case someone was doing something with MY <vbg data!

I also determined the range to filter by looking at entries in column A and row
1. If you have to use a different column or row, change those lines with
..end(xlup) and .end(xltoleft).

And I filtered from A1:(lastcol)(lastrow) looking for "in progress" or empty (to
match your posts).

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Macros will have to be enabled when the user opens the workbook.

And if you're using xl2007, make sure you save it as a workbook that can contain
macros (.xls or .xlsm or .xlsb (I think)).

JeffK wrote:

"Record a macro when you do it manually" sorry but my excel talent is
limited, how do I go about doing this?

"Dave Peterson" wrote:

Record a macro when you do it manually.

(Re)name that recorded Macro to "Auto_Open()"

JeffK wrote:

Thanks for your response Dave. I'm familiar with autofilter, but I would
like to program it so it will do a filter automatically when the file opens.

"Dave Peterson" wrote:

It sounds like you could use data|filter|autofilter (xl2003 menus) to hide/show
the rows you want.

The rows actually hide (not a rowheight of 1), but that's been ok for me.

Select your data (include the last header row)
data|filter|autofilter
Then use the arrow keys in the last header row to show/hide what you want.

Debra Dalgleish shares some getstarted tips:
http://contextures.com/xlautofilter01.html

JeffK wrote:

I have a Spreadsheet that as time goes on will grow quite large. In Column
A, the data entry person can choose from 4 options (In progress, Sold,
Cancelled, Declined). I would like to have all rows (starting from row 8) to
collapse to a height=1 except for those that are "In Progress".

I would be helpful if the rows would only collapse when file is closed and
reopened.

Your help is appreciated.

--

Dave Peterson
.

--

Dave Peterson
.

--

Dave Peterson
.

--

Dave Peterson
.

--

Dave Peterson
.

--

Dave Peterson
.

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
collapse an expand rows using VB	soinx	Excel Programming	7	July 16th 09 12:35 PM
how do i expand and collapse rows	office wreck!	New Users to Excel	7	June 14th 09 08:41 PM
How do I move the expand/collapse for rows and columns in Excel?	JP	Excel Worksheet Functions	0	May 4th 07 02:40 AM
Programming Outlines (expand/collapse rows) in Excel	[email protected]	Excel Programming	0	January 17th 07 10:33 PM
Expand/Collapse Rows	[email protected]	Excel Worksheet Functions	0	May 18th 06 09:23 AM

Menu

About Us