Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
using alt-enter entry in 1st row hides col alphabet headings Stephen S Excel Discussion (Misc queries) 2 March 10th 05 08:52 AM
can't get to change from portrait to landscape even after clicked. linda1960w Excel Discussion (Misc queries) 0 February 28th 05 04:57 PM
Alphabet sequencing in a conditional format craftwoodman Excel Discussion (Misc queries) 7 January 3rd 05 01:41 AM
Adding cells with alphabet and returning a numbric value Irv Excel Worksheet Functions 4 December 23rd 04 10:01 PM
how to assign a value to the alphabet in order to add up letters Robert Horne Excel Discussion (Misc queries) 1 December 10th 04 08:15 PM


All times are GMT +1. The time now is 10:34 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"