Go to A - Z

I have a rather long Excel spreadsheet that I frequently search on titles in one column. But it's sometimes more effective to do an inexact search and to just eyeball a region of the sheet.

I thought it would be nice to add command buttons [A] through [Z] near the top for crude-but-fast searching. You click "S" and it takes you to the first entry that begins with "S".

In VB6, I'd just make a control array of 26 command buttons, probably in a frame, and the programming would be rather easy (do until Left$ is . . .).

But with no control arrays in Excel VBA, I guess that each command click event would have to call the same sub and pass its key letter.

I"m guessing that there's a premade control that presents 26 letters and returns the one clicked ("Letter Picker"?) but I would need help finding it.

Is there such a control, or do I have to create it?

Are you searching this table manually? Why not enable AutoFilter and do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Static LastValue As String
Dim s As Worksheet
Set s = ActiveSheet
If LastValue <> Range("B1").Text And s.AutoFilterMode Then

If s.Range("B1").Text <> "" Then
s.Range("FilterRange").AutoFilter field:=1, Criteria1:="=" & s.Range("B1").Text & "*", Operator:=xlAnd
Else
s.Range("FilterRange").AutoFilter field:=1, Criteria1:="=*", Operator:=xlAnd
End If
End If
LastValue = s.Range("B1").Text
Set s = Nothing
End Sub

And whatever you put in B1 will turn into a "Begins With" criterial on the first field (column) of the autofilter table. Of course you could change it so whatever you typed into the box was the whole criterion, i.e. Criteria1:="=" & s.Range("B1").Text & "*" changed to Criteria1:=s.Range("B1").Text. Then you could put ">b" and everything above the first item starting with b would disappear.

If you want to stick with your original idea of twenty-six buttons, I just posted an example of the "standard" VBA-OO-workaround for the no-control-array issue in this thread (http://www.xtremevbtalk.com/showthread.php?t=297521) (past #6). It's basically the same as Squirm's "Control/Class arrays - WithEvents! (http://www.xtremevbtalk.com/showthread.php?t=137599&highlight=withevents)" (and with ads! grrr!) solution, but with an Excel-twist.

But I'd go with a filtering solution here, as suggested by darkforcesjedi. :)

Exactly, I'd go along with DFJ and Cas here.

If you were doing this 'manually' then the two ways that immediately spring to mind are:
(1) Using an autofilter
(2) Using Find

So, taking the Worksheet_Change event from #2 as an example, the following (basic outlines) lend themselves:

'autofilter option (as already outlined in #2):
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
If AutoFilterMode Then
AutoFilter.Range.AutoFilter Field:=1, Criteria1:="=" & Range("B1").Text & "*", Operator:=xlAnd
End If
End If
End Sub



'find option
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFound As Range
If Not Intersect(Target, Range("B1")) Is Nothing Then
Set rngFound = Range("A:A").Find( _
what:=Range("B1").Value & "*", _
lookat:=xlWhole)

If Not rngFound Is Nothing Then
rngFound.Select
End If

End If
End Sub


So given the above options, the 26 buttons in a frame with control array workaround, whilst completely do-able, does seem like a lot of work here...

Just my 2 cents.
Colin

The other option I would suggest is adding the letters to 26 CommandBarButtons on a user-defined CommandBar. Each CommandBarButton can be assigned a macro. You can create a handler. Then the CommandBarButton controls can behave like a control array.

Public Sub p(intin As Integer)
MsgBox Chr(intin)
End Sub


Sub CreateCommandBar()

Dim k As CommandBar, b As CommandBarControl

On Error Resume Next
Set k = CommandBars("MyCustomBar")
On Error GoTo 0

If k Is Nothing Then
Set k = CommandBars.Add("MyCustomBar")
End If
k.Visible = True

For Each b In k.Controls
b.Delete
Next

For Index = 65 To 90

With k.Controls.Add(msoControlButton)
.Visible = True
.Caption = Chr(Index)
.Style = msoButtonCaption
.OnAction = Replace("p(#)", "#", Index)
End With

Next

Set k = Nothing

End Sub

Thank you all. I'll definitely try some of these suggestions.