Designing non-traditional interfaces with Excel's built-in tools
Each of the following solutions takes very little work -- and
each is implemented strictly in Excel. In addition, the coding
techniques demonstrate some VBA programming methodologies that are
themselves quite non-traditional.
For a variety of reasons, one expects a solution designed in
Excel to conform closely to the defaults provided by Excel.
That often results in worksheet based solutions that rely on a
tabular grid or a VBA solution with a userform with some number of
buttons and various controls in shades of grey. However, as
this tutorial demonstrates, it is possible to create solutions that
are close to the consumer's expectation of the interface for a
particular device (a telephone, in this case).
The problem at hand is to develop a mechanism to convert phone "numbers"
that contain letters into pure numbers. Yes, the task is
seemingly trivial and can be accomplished with Excel formulas,
albeit non-trivial ones. At the same time, this seemed an
ideal candidate to illustrate some less-traditional user interfaces
coupled with equally non-mainstream programming techniques.
The user interfaces of the three solutions discussed below are
shown in Figure 1. The first UI uses shapes and an image.
The second is simply Excel cells formatted to create the desired
effect. The last should be easily recognizable as a userform,
albeit with all the grey removed.
 |
 |
 |
Figure 1 -- User
interfaces for the three solutions
All the solutions use VBA code.
However, as we will see the coding itself is as non-traditional as
it is simple.
1 The zip file contains three
files, each of which contains one of the above solutions. Each
file contains VBA macros and it is up to you to decide if you want
to open the file or not.
Option 1: Shapes
Option 2: Worksheet
Option 3: Userform
The final effect is the combination of the
Oval shape from the Drawing toolbar.
with an image of the handset. The latter was either from the
Microsoft Clipart collection or from a search of Google images.
To create a circle, click the Oval shape in the toolbar, hold
down the SHIFT button and drag to draw.
To align objects, select them, then from the
Drawing toolbar select
Draw | Align or Distribute > and the
appropriate alignment option.
To add text, select the object, right-click and select the Add
Text item. Set the font and font size appropriately and for
the object set the Margins as desired (double-click
the object, then in the resulting dialog box, click the
Margins tab, and set the various
margins).
| The fill used for each shape was a dual color
effect. To accomplish this select the object, then select
Edit | Format Autoshape... (or
simply double-click the object).
In the resulting dialog box, select the
Colors and Lines tab. From the Color drop-down
select Fill Effects... |
 |
| In the Fill Effects dialog box, select the
Two Colors option and
set the colors as desired. |
 |
Once one circle is completed, duplicate it to create the others,
the easiest way being to copy it, paste 11 times, and make the
necessary changes to each.
| Name the oval that will contain the result
(it's the only white object) as Result. To
do this, select the object, then type the name in the Name Box
and press ENTER. |
 |
Next, add the macros. How many lines of code do you think
we need to add? How about 2 lines? Well, other than the
subroutine declarations. It turns out we need a few more since
once we protect the worksheet, the code cannot update the Results
shape without unprotecting the sheet first. In a standard
module, add the following:
Option Explicit
Sub clearResult()
With ActiveSheet
.Unprotect
.Shapes("Result").TextFrame.Characters.Text = ""
.Protect
End With
End Sub
Sub aDigit(aDig As String)
With ActiveSheet
.Unprotect
With .Shapes("Result").TextFrame.Characters
.Text = .Text & aDig
End With
.Protect
End With
End Sub
Put the final touches to the display. Delete all the sheets
in the workbook other than the one with the telephone pad design. To remove most of the typical Excel
interface elements, select
Tools | Options... Then, from the
View tab, uncheck the options for:
Gridlines,
Row & column headers,
Horizontal scroll bar,
Vertical scroll bar, and
Sheet tabs.
Accomplishing the effect of raised buttons and a sunken result
space requires the combination of three separate steps.
First, leave alternate rows and columns blank. Reduce
their respective widths or heights. For those rows and
columns that will become 'buttons' make them reasonably large.
Re-adjust these once again to get the best effect after completing
the other steps.
Second, the raised effect requires the following: Use a light
shade for the patterns for all the cells in the region. For
each cell that will be a button, draw a white border on the top
and left edges, and a dark border for the right and bottom edges.
[For a sunken effect as in the Result field, reverse the
white-dark selections. However, for a reason that will
become clear in a bit, we will have to reformat the border colors
for the result field.]
Third, for the finishing touch, enclose the overall region that
will be the telephone dialpad with a thick border. Create
the shadow effect by making the next right column and bottom row
very narrow and filling the cells with a black pattern.
To enter the letters and the number on a separate line, use the
ALT+ENTER combination. Remember to use ALT+ENTER even for
those cells that contain only a single character (the 1, the zero,
and the dash sign). This is important since the VBA code
relies on the presence of the character corresponding to ALT+ENTER.
Next, widen column A and row 1 so that the telephone pad appears
more towards the center of the monitor.
Create the space that will contain the result with the following:
First, merge cells B3:G3. Select them, the right-click, select
Format Cells... and from the Alignment tab check the option Merge
Cells. Name this merged cell as Sheet1!Result --
assuming the worksheet is named Sheet1.
The result cell borders must be formatted slightly differently
than the other cells. This is because when Excel selects a
cell it "reverses" the border color after accounting for the color
of the cell's interior. So, when selected, a white border will
become pink. Since we will always have the result cell as the
selected cell, the white should be the result after Excel applies it
reverse the border color algorithm. Since white becomes pink,
start with a pink border and Excel will reverse it to white!
Also, name the cell that contains the word "Clear" as CLR.
The final formatting touches are similar to the previous option.
Delete all the sheets in the workbook other than the one with the
telephone pad design. Remove most of the typical Excel
interface elements: Select
Tools | Options... Then, from the
View tab, uncheck the options for:
Gridlines,
Row & column headers,
Horizontal scroll bar,
Vertical scroll bar, and
Sheet tabs.
Next, the code.
We will use the worksheet's SelectionChange event procedure to
detect which cell the user clicked. The code will use the
value of the cell to figure out what digit to add to the result
cell. In addition, it will always leave the result cell as the
selected cell. This is important since the SelectionChange
event is not triggered if the user selects an already-selected cell.
A discussion of how this code is non-mainstream is left to a
separate section since it would not quite fit in with the UI
emphasis here and it would make this section incredibly long.
So, here's the code without a detailed explanation.
Insert class module and name it clsWKS. In it, insert the
code below:
Option Explicit
Dim WithEvents WKS As Worksheet
Property Set aWS(uWS As Worksheet)
Set WKS = uWS
End Property
Private Sub WKS_SelectionChange(ByVal Target As Range)
Dim aName As Name
If Target.Cells.Count > 1 Then GoTo ErrXIT
On Error Resume Next
Set aName = Target.Name
On Error GoTo 0
If aName Is Nothing Then
ElseIf InStr(1, aName.Name, "CLR") > 0 Then
WKS.Range("Result").Value = ""
GoTo NormalXIT
End If
Dim vbNewLineLoc As Integer
vbNewLineLoc = InStr(1, Target.Value, Chr(10))
If vbNewLineLoc < 1 Then GoTo ErrXIT
WKS.Range("result").Value = WKS.Range("result") & _
Mid(Target.Value, vbNewLineLoc + Len(Chr(10)), 1)
GoTo NormalXIT
ErrXIT:
Beep
GoTo NormalXIT
NormalXIT:
On Error Resume Next
Application.EnableEvents = False
WKS.Range("result").Select
Application.EnableEvents = True
End Sub
In the ThisWorkbook module, insert the code:
Option Explicit
Dim WSManager As clsWKS
Private Sub Workbook_Open()
Set WSManager = New clsWKS
Set WSManager.aWS = ActiveWorkbook.Sheets(1)
End Sub
Private Sub resetHandler()
Set WSManager = Nothing
End Sub
The resetHandler code is needed if one wants to make any change
to the worksheet design. Run it before making worksheet
changes.
This section discusses the userform design and presents the code
without any discussion. As above, the code discussion is
deferred to elsewhere. See xxx for an explanation of how it
all works. One note though. The entire implementation is
through objects. Each task is performed by the object best
suited to that task (well, actually, by the code in the class module
that defines the object).
To test the final solution, from Excel run the showKeypad macro
(use ALT+F8 to display the Macros dialog box, then double click the
showKeypad entry). Typically, I would use a menu item or a
toolbar but implementing that interface would needlessly distract
from the focus of this section.
Create a default userform. Reshape it so that it is longer
than wider. Change the background color to white. To do
so, select the userform, then click F4 to bring up the Properties
pane. In there set the BackColor to white. Also change
the name to UF1 and the caption to something more appropriate than
Userform1.
Each of the controls is a label with the exception of the field
in which the user can enter a phone number, which is a Textbox.
For the labels that are not green, click each and from the
Properties pane set the BackStyle to transparent. Name the
label that will contain the result as Result.
In creating the green labels, it may be more efficient to create
one label, format it as desired, then copy-and-paste to make
additional labels.
For those labels with a green background, set the BackColor to an
appropriate light color. In those labels, to separate the
letters on one row from the number on the 2nd row, use the
CTRL+ENTER combination. Format the height and width to get the
desired effect.
Format the font for each label by clicking the Font entry in the
Properties pane, then clicking the ...button that shows up on the
right to call up the Format Font dialog box.
Name each label as Key1, Key2, etc. Name the label with the
hyphen as KeyMinus and the label with the empty caption as Clear.
Optionally, use a caption for that key that reads Clear
or something along those lines.
The code required to make the userform work is presented here
without discussion. See xxx for an explanation of how it all
works. The only point worth noting here is that the
implementation is exclusively through objects. The only piece
that isn't is the code needed to show the userform. And, that
too would be part of an object if I'd created a menu for this
solution. But, the code to create a menu and handle the user
selections would have needlessly distracted from the focus of the
solution. The result is the one-liner standard module.
The code in the UF1 userform's module:
Option Explicit
Dim allLabels As Collection, _
Translator As clsTranslator
Private Sub Clear_Click()
Me.UserText.Text = ""
End Sub
Private Sub UserForm_Initialize()
Dim aCtrl As MSForms.Control, LabelWithEvents As clsLabel
Set Translator = New clsTranslator
Set allLabels = New Collection
For Each aCtrl In Me.Controls
If TypeOf aCtrl Is MSForms.Label And Left(aCtrl.Name, 3) = "Key" Then
Set LabelWithEvents = New clsLabel
Set LabelWithEvents.aLabel = aCtrl
allLabels.Add LabelWithEvents, LabelWithEvents.aLabel.Name
End If
Next aCtrl
End Sub
Private Sub UserText_Change()
Dim i As Integer, Rslt As String
For i = 1 To Len(Me.UserText.Text)
Rslt = Rslt & Translator.Translate(Mid(Me.UserText.Text, i, 1))
Next i
Me.Result.Caption = Rslt
End Sub
Public Sub insertText(aText As String)
Dim Temp As String, Rslt As String
With Me.UserText
Temp = .Text
Rslt = Left(Temp, .SelStart) & aText
If .SelStart = 0 And .SelLength = 0 Then
Else
Rslt = Rslt & Right(Temp, .TextLength - (.SelStart + .SelLength))
End If
.Text = Rslt
End With
End Sub
The code in the standard module:
Option Explicit
Sub showKeypad()
UF1.Show
End Sub
The code in the class module named clsLabel
Option Explicit
Dim WithEvents aLbl As MSForms.Label
Property Get aLabel() As MSForms.Label
Set aLabel = aLbl
End Property
Property Set aLabel(uLabel As MSForms.Label)
Set aLbl = uLabel
End Property
Private Sub aLbl_Click()
Dim vbNewLineLoc As Integer
vbNewLineLoc = InStr(1, aLabel.Caption, vbNewLine)
If vbNewLineLoc < 1 Then
UF1.insertText aLabel.Caption
Else
UF1.insertText _
Mid(aLabel.Caption, vbNewLineLoc + Len(vbNewLine), 1)
End If
End Sub
and finally, the code in the class module named clsTranslator
Option Explicit
Dim Letters() As String, Numbers() As String
Function Translate(aVal As String)
On Error Resume Next
Dim Rslt
With Application.WorksheetFunction
Rslt = .Match(aVal, Letters, 0)
If Err.Number <> 0 Then
Translate = aVal
Else
Translate = .Index(Numbers, Rslt)
End If
End With
End Function
Private Sub Class_Initialize()
Letters = Split("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z", ",")
Numbers = Split("2,2,2,3,3,3,4,4,4,5,5,5,6,6,6,7,7,7,7,8,8,8,9,9,9,9", ",")
End Sub
|
