Home |
Search |
Today's Posts |
|
#1
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
using alt-enter entry in 1st row hides col alphabet headings | Excel Discussion (Misc queries) | |||
can't get to change from portrait to landscape even after clicked. | Excel Discussion (Misc queries) | |||
Alphabet sequencing in a conditional format | Excel Discussion (Misc queries) | |||
Adding cells with alphabet and returning a numbric value | Excel Worksheet Functions | |||
how to assign a value to the alphabet in order to add up letters | Excel Discussion (Misc queries) |