Excel (Mac) Macro Help

[Mark]

I'm a novice in creating macros. I have a large database for softball
officials with a "Helper" column I set up (with a formula help from this
newsgroup) to allow me to select an official and bring up all his games
whether he is on the plate or bases (separate columns). I go to the "Helper"
column, select "Custom" filter, select "Contains", type in the name of the
official, and all games assigned to that official appear. I've played around
with setting up a macro to save me some time but I can't get it to do what I
want. The macro won't seem to allow me to get to the text box without saving
a name in the text box. I wanted to get to the text box and just type in the
name I need each time I set up an umpires schedule for him. Any help here?

-Mark

 

[Jonathan Rynd]

I've played around
with setting up a macro to save me some time but I can't get it to do
what I want. The macro won't seem to allow me to get to the text box
without saving a name in the text box. I wanted to get to the text box
and just type in the name I need each time I set up an umpires
schedule for him. Any help here?

You'd have to write a macro that brings up its own dialog box asking for
the name of the official, and saves it in a variable.

Then include the command that you get when recording, except where it puts
the official's name in quotation marks, you replace it with the variable
containing the name.

 

[Dave Peterson]

I think I'd use another helper cell--right above the cell that has the
concatenation formula.

Then use a worksheet_change event to filter on what I typed in that cell.

I don't recall how your worksheet was set up, but in my test workbook, I put a
filter on columns A:M. And I used column M for my helper cells.

I had headers in row 2 and did my typing in cell M1.

If you like this idea, then rightclick on the worksheet tab that should behave
this way. Select view code and paste this in the code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("m1")) Is Nothing Then Exit Sub
If Me.AutoFilterMode = False Then Exit Sub

If Me.FilterMode = True Then
Me.ShowAllData
End If

If Trim(Target.Value) = "" Then Exit Sub 'after clearing filter.

Me.AutoFilter.Range.AutoFilter _
Field:=13, Criteria1:="*" & Target.Value & "*"

End Sub

Somethings to change.

Me.Range("m1")
will become the cell you type the official's name.

Field:=13
will be the autofilter range column. If you filter from E:M, then M is not
column 13--it's 9--(E=1, F=2, ...M=9).

And me.showalldata
could be removed it you want to keep the existing filter. Say you filtered on
Diamond (baseball/Softball??) and want to see just "smith" for diamond #8, you
wouldn't want to do a .showalldata then.

 

[Dave Peterson]

One more thing. There's an icon that you could add to your favorite toolbar
that shows all the the data quickly (faster than examining each dropdown and
changing it to All and faster than Data|filter|showall.)

It's the Showall button. (Nicely named, huh?)

Tools|customize|Commands Tab|Data Category
drag Showall to a nice spot on your favorite toolbar
(or create a new toolbar with all your new favorites. There's lots of stuff you
could add!)