View Single Post
  #4   Report Post  
AlfD
 
Posts: n/a
Default

Hi!

This uses VBA and need not take long to assemble.

Put 26 little rectangles from the drawing toolbar at the top of your
worksheet - so that in due course that part can be frozen there.
Easiest way I know is to make 1 to suit my taste on colour size font
etc and copy/paste the rest.

Be meticulous about their "names" which will come up in the Name Box as
Rectangle 1, Rectangle 2 etc. We are going to use those to match A - Z.

Somewhere out of the way (say, column Q) put A,B,....,Z in a column.
These will be the captions for the rectangles.

Right select the first rectangle and in the formula bar type =Q1 where
Q1 is where you stored "A": enter. The rectangle should now have an A
in it. Edit if you are not happy, but I suggest you leave that till
later when you can do them all at one go.

Paste this macro into a module (Insert Module)

Public Sub FindAlpha()

Dim CallerID As String
Dim N As Integer
Dim sCaption As String
Dim R As VbMsgBoxResult

CallerID = Application.Caller
N = Val(Right(CallerID, 2)) + 64
sCaption = Chr(N)

Dim i As Integer
i = 1
While i < 2000
If UCase(Left(Range("A" & i), 1)) = sCaption Then
Range("A" & i).Select
Exit Sub
End If
i = i + 1
Wend
R = MsgBox("No entries for the letter " & sCaption, vbOKOnly)

End Sub

I put the <2000 condition in as both safeguard (infinite loops) and
laziness (you could find the length of the column instead)

Finally, connect all of the 26 buttons to this macro. Shift-click on
all 26: then right-click on them and select Assign macro. Choose
FindAlpha from the list.

Have fun!

Alf