It would be greatly apprecicated if someone could explain how to achievet
the following scenario:

Situation
----------

We have a big spreadsheet highlighing our inventory of yachts with multiple
columns all using AutoFilter pull down menus allowing the user to 'filter'
by particular value in a particular column
eg: Column D filter | Custom | equals ='power' | contains ='sail'

Objective
------------
Is there a simple programatiic way that I can add some kind of button to the
excel spreadsheet which when pressed achieves the following

1. Hides unnecessary columns temporarily

2. Applies a filter to the approprriate column similiar to the filter
outlined above.


I envisage a series of buttons that would allow the end user to view the
data according to eg:

YachtType
Condition
Length
etc

I had considered macros but still faced with:

1. Captureing mouse or keystrokes
2. How do I create button on an excel spreadsheet

Seems a simple programatic solution would be best.

Many thanks in advance
Jason

Show/Hide Columns Temporarily:

First, you'll need to add a control button/object to your spreadsheet.
The following steps will provide a toggling button that will show or
hide columns you define.

1) Right-click in the toolbars section and select "Control Toolbox"
from the shortcut menu.
2) Click the "Command Button" icon on the Control Toolbox toolbar, then
click anywhere on your spreadsheet. A button labeled "CommandButton1"
will appear. Resize and position appropriately.
3) Rename your button by selecting the Name Box (left of the formula
bar, where range names appear) and typing "btnSH".
4) Insert the following code in the Sheet object in VBA (remove leading
..... marks)

Private Sub btnSH_Click()
.....If btnSH.Caption = "Hide Columns" Then
.........'modify the following two lines as necessary for each/all
columns you wish to hide/show
.........Columns("D:D").Select
.........Selection.EntireColumn.Hidden = True
.........btnSH.Caption = "Show Columns"
.....Else
.........Columns("D:D").Select
.........Selection.EntireColumn.Hidden = False
.........btnSH.Caption = "Hide Columns"
.....End If
End Sub

=================================
Programmatically adding custom filters:

Your example won't yield results because you ask for both an exact
match ( equals "power") and a partial match (contains "sail").
However, if you're looking for an OR match (equals "power" OR contains
"sail") then you'd use the following code in your macro:

' Turns on custom filter in Column D
Selection.AutoFilter Field:=4, Criteria1:="=power", Operator:=xlOr,
Criteria2:="=*sail*"
' Turns off custom filter in Column D
Selection.AutoFilter Field:=4

You could create a command button and control macro similar to the one
for hide/show columns to run this code. You'd just change the button
name and replace the show/hide actions with the above lines.
-Glenn Ray

Hi Glenn, firstly thank you for coming back with such an awesome post...!

Part 1
Show/Hide

I did as instructed including changing the caption to 'Hide Columns' however
upon clicking the button I receive:

Device I/O Error

I checked the name of the button (eg btnSH) and it all seems okay... I also
chose a range of A:E .....do you know why I am getting this error?

Again, really appreciated!
Jason
"Glenn" wrote in message
ups.com...
Show/Hide Columns Temporarily:

First, you'll need to add a control button/object to your spreadsheet.
The following steps will provide a toggling button that will show or
hide columns you define.

1) Right-click in the toolbars section and select "Control Toolbox"
from the shortcut menu.
2) Click the "Command Button" icon on the Control Toolbox toolbar, then
click anywhere on your spreadsheet. A button labeled "CommandButton1"
will appear. Resize and position appropriately.
3) Rename your button by selecting the Name Box (left of the formula
bar, where range names appear) and typing "btnSH".
4) Insert the following code in the Sheet object in VBA (remove leading
.... marks)

Private Sub btnSH_Click()
....If btnSH.Caption = "Hide Columns" Then
........'modify the following two lines as necessary for each/all
columns you wish to hide/show
........Columns("D:D").Select
........Selection.EntireColumn.Hidden = True
........btnSH.Caption = "Show Columns"
....Else
........Columns("D:D").Select
........Selection.EntireColumn.Hidden = False
........btnSH.Caption = "Hide Columns"
....End If
End Sub

=================================
Programmatically adding custom filters:

Your example won't yield results because you ask for both an exact
match ( equals "power") and a partial match (contains "sail").
However, if you're looking for an OR match (equals "power" OR contains
"sail") then you'd use the following code in your macro:

' Turns on custom filter in Column D
Selection.AutoFilter Field:=4, Criteria1:="=power", Operator:=xlOr,
Criteria2:="=*sail*"
' Turns off custom filter in Column D
Selection.AutoFilter Field:=4

You could create a command button and control macro similar to the one
for hide/show columns to run this code. You'd just change the button
name and replace the show/hide actions with the above lines.
-Glenn Ray

Whoa - just figured this paticular problem out: If you have VBA window open
in background you get this device error...I will now attempt to implement
part II of Glenn's solution...
wrote in message
...
Hi Glenn, firstly thank you for coming back with such an awesome post...!

Part 1
Show/Hide

I did as instructed including changing the caption to 'Hide Columns'
however
upon clicking the button I receive:

Device I/O Error

I checked the name of the button (eg btnSH) and it all seems okay... I
also
chose a range of A:E .....do you know why I am getting this error?

Again, really appreciated!
Jason
"Glenn" wrote in message
ups.com...
Show/Hide Columns Temporarily:

First, you'll need to add a control button/object to your spreadsheet.
The following steps will provide a toggling button that will show or
hide columns you define.

1) Right-click in the toolbars section and select "Control Toolbox"
from the shortcut menu.
2) Click the "Command Button" icon on the Control Toolbox toolbar, then
click anywhere on your spreadsheet. A button labeled "CommandButton1"
will appear. Resize and position appropriately.
3) Rename your button by selecting the Name Box (left of the formula
bar, where range names appear) and typing "btnSH".
4) Insert the following code in the Sheet object in VBA (remove leading
.... marks)

Private Sub btnSH_Click()
....If btnSH.Caption = "Hide Columns" Then
........'modify the following two lines as necessary for each/all
columns you wish to hide/show
........Columns("D:D").Select
........Selection.EntireColumn.Hidden = True
........btnSH.Caption = "Show Columns"
....Else
........Columns("D:D").Select
........Selection.EntireColumn.Hidden = False
........btnSH.Caption = "Hide Columns"
....End If
End Sub

=================================
Programmatically adding custom filters:

Your example won't yield results because you ask for both an exact
match ( equals "power") and a partial match (contains "sail").
However, if you're looking for an OR match (equals "power" OR contains
"sail") then you'd use the following code in your macro:

' Turns on custom filter in Column D
Selection.AutoFilter Field:=4, Criteria1:="=power", Operator:=xlOr,
Criteria2:="=*sail*"
' Turns off custom filter in Column D
Selection.AutoFilter Field:=4

You could create a command button and control macro similar to the one
for hide/show columns to run this code. You'd just change the button
name and replace the show/hide actions with the above lines.
-Glenn Ray

Ok, Glenn, part II is working! Yahoo!!

I have another question relating to the "Hide Columns" which currently
allows me to hide a range eg:

Columns("A:E,0").Select

....is there a way for me to add Ad Hoc Columns onto this range? For instance
this does not work:

Columns("A:E,0, J, Z").Select

As I am now finding that the end user needs the columns 'arranged' in a
certain order....is there perhaps a way to simply rearrange the columns and
apply a disparate hiding technique as above?


Thanks
Jason

wrote in message
...
Whoa - just figured this paticular problem out: If you have VBA window
open
in background you get this device error...I will now attempt to implement
part II of Glenn's solution...
wrote in message
...
Hi Glenn, firstly thank you for coming back with such an awesome
post...!

Part 1
Show/Hide

I did as instructed including changing the caption to 'Hide Columns'
however
upon clicking the button I receive:

Device I/O Error

I checked the name of the button (eg btnSH) and it all seems okay... I
also
chose a range of A:E .....do you know why I am getting this error?

Again, really appreciated!
Jason
"Glenn" wrote in message
ups.com...
Show/Hide Columns Temporarily:

First, you'll need to add a control button/object to your spreadsheet.
The following steps will provide a toggling button that will show or
hide columns you define.

1) Right-click in the toolbars section and select "Control Toolbox"
from the shortcut menu.
2) Click the "Command Button" icon on the Control Toolbox toolbar,
then
click anywhere on your spreadsheet. A button labeled "CommandButton1"
will appear. Resize and position appropriately.
3) Rename your button by selecting the Name Box (left of the formula
bar, where range names appear) and typing "btnSH".
4) Insert the following code in the Sheet object in VBA (remove
leading
.... marks)

Private Sub btnSH_Click()
....If btnSH.Caption = "Hide Columns" Then
........'modify the following two lines as necessary for each/all
columns you wish to hide/show
........Columns("D:D").Select
........Selection.EntireColumn.Hidden = True
........btnSH.Caption = "Show Columns"
....Else
........Columns("D:D").Select
........Selection.EntireColumn.Hidden = False
........btnSH.Caption = "Hide Columns"
....End If
End Sub

=================================
Programmatically adding custom filters:

Your example won't yield results because you ask for both an exact
match ( equals "power") and a partial match (contains "sail").
However, if you're looking for an OR match (equals "power" OR contains
"sail") then you'd use the following code in your macro:

' Turns on custom filter in Column D
Selection.AutoFilter Field:=4, Criteria1:="=power", Operator:=xlOr,
Criteria2:="=*sail*"
' Turns off custom filter in Column D
Selection.AutoFilter Field:=4

You could create a command button and control macro similar to the one
for hide/show columns to run this code. You'd just change the button
name and replace the show/hide actions with the above lines.
-Glenn Ray