Make Letter Buttons for B-Z from A

Example

Call ActiveForm_MakeLetterButtons("LetterClick")

Only btn_A is on the form. The form is in design view and is active. Run ActiveForm_MakeLetterButtons to make the rest of the letter buttons!

form with a command button for the letter A

The only button that existed before was for "A". The rest were made by code, patterned after btn_A.

A-Z buttons, properties of command button

Goto Top

Properties

for new buttons, these Properties are set:

Other

Name
Name will be "btn_C" WHERE "C" is whatever letter or number character
StatusBarText
Uses what is defined for btn_A, but replaces the letter on the end
For instance, change
Go to first phrase starting with A
to
Go to first phrase starting with B

Format

Caption
Caption will be set to "&C" WHERE & indicates that character "C" is the hot key so the keyboard shortcut will be Alt-C where "C" is whatever letter or number is on the button.
Width
Height
Top
Top could be changed, but it isn't.
Left
Left is calculated each time through the loop according to where it was, button width, and gap.
BackColor
BorderColor
HoverColor
HoverForeColor
ForeColor
PressedColor is set to black
PressedForeColor is set to white

Event

Click
If there is a function name specified in the sClickEventName parameter, a click event will be created for each button that sends the button character as a parameter. For instance,

=LetterClick("B")

If sClickEventName is not specified, the buttons will take space, and not be very useful because they won't do anything except maybe change color.

Code

'module name: _mod_ActiveForm_MakeLetterButtons
'*************** Code Start *****************************************************
' Purpose  : Make Letter Buttons for B-Z from A on a form. Optionally, also 0-9.
' Author   : crystal (strive4peace)
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'-------------------------------------------------------------------------------
'                              ActiveForm_MakeLetterButtons'-------------------------------------------------------------------------------
' 
Sub ActiveForm_MakeLetterButtons( _
    Optional sClickEventName As String = "" _
   , Optional pbNumbersToo As Boolean = False _
   , Optional pbGapQuarterMax As Boolean = False _
   , Optional piGap As Integer = -1 _   
   )
'190723, 27 strive4peace
'makes command buttons on a form for B-Z, then, optionally, 0-9
'name controls: btn_X where X is the letter or number
'
   'FORM MUST BE ACTIVE and in DESIGN VIEW
   ' command button to pattern after MUST be named btn_A
   '
   'PARAMETERS
   '  sClickEventName = Click event that runs for the new letter/number
   '     ie: =LetterClick("B")
   '     WHERE LetterClick is defined in code behind the form
   '                       or in a general module
   '  pbNumbersToo. if True then buttons for 0-9 will be made after letters
   '  if pbGapQuarterMax is true, then use 1/4 button width for gap
   '     (if calculated gap is greater)
   '     This is done so spacing doesn't look so odd
   '  iGap is the gap between buttons.
   '     -1 or <0 is indicator to calculate based on form width
   '     fyi: 36 = 0.25 inch * 1440 twip/inch. 72 = 1/2 inch.
   '
   'NOTES
   ' If StatusBarText is set for btn_A, ASSUME last letter of what is specified
   '     is the letter you want to replace
   '     'ie: Go to first phrase starting with A
   '           --> Go to first phrase starting with B
   '
   '  Copy properties of pattern button like Size and Color
   '  Make new buttons with B-Z
   '     and then 0-9 if pbNumbersToo = True
   ' If you don't like the form changes, Don't save, and Run again
   '       .. maybe you don't like the spacing or want to change btn_A:
   '           close and don't save the form, open in design view again
   '           change Width of the A button pattern,
   '           and whatever other properties you want
   '           and Save form.  
   '           Then run again (modify parameters too?)
   '           until you like how your form looks   
   

   On Error GoTo Proc_Err

   Dim oForm As Form

   Dim iLeft As Integer _
      , iTop As Integer _
      , iWidth As Integer _
      , iHeight As Integer _
      , iGapQtr As Integer _
      , iSection As Integer _
      , iASCII As Integer _
      , iLoop As Integer _
      , iMaxButtons As Integer _
      , iMaxSets As Integer _
      , iStart As Integer _
      , iEnd As Integer _
      , sCaption As String _
      , sFormName As String _
      , sStatusBarText As String _
      , sMsg As String _
      , nBackColor As Long _
      , nBorderColor As Long _
      , nHoverColor As Long _
      , nHoverForeColor As Long _
      , nForeColor As Long
      
   iASCII = 0 'A -- this will be incremented
   sCaption = "" 'ie: "A" -- this will be changed
   
   'see how many buttons user wants to do
   If pbNumbersToo = True Then
      iMaxButtons = 36
      iMaxSets = 2
   Else
      iMaxButtons = 26
      iMaxSets = 1
   End If

   Set oForm = Screen.ActiveForm
   
   With oForm
      sFormName = .Name
      sMsg = "*** Make Letter/Number command buttons for " & sFormName & "  *** "
      Debug.Print sMsg; Tab(80); ; " * " & Now
         
      With .Controls("btn_A")
         iSection = .Section
         iLeft = .Left
         iTop = .Top
         iWidth = .Width
         iHeight = .Height
         nBackColor = .BackColor
         nBorderColor = .BorderColor
         nHoverColor = .HoverColor
         nHoverForeColor = .HoverForeColor
         nForeColor = .ForeColor
         
         sStatusBarText = Trim(.StatusBarText & "")
         If sStatusBarText <> "" Then
            'strip last letter 
            sStatusBarText = Trim(Left(sStatusBarText, Len(sStatusBarText) - 1))
         End If
      End With
      
      'calculate gap
      If piGap < 0 Then
         'from the form width subtract:
         '  double Left(A) for left margin and space on right after buttons
         '  width(A) * number of buttons.
         '  Then divide all that by (#buttons-1)
         piGap = ((.Width + 1 - iLeft * 2) - (iWidth * iMaxButtons)) \ (iMaxButtons - 1)
         If piGap < 1 Then
            piGap = 0
         End If
         'maybe move this to after If calculate block?
         'if pbGapQuarterMax  was specified, maybe reduce gap         
         If pbGapQuarterMax = True And piGap <> 0 Then
            iGapQtr = (iWidth + 3) \ 4
            If iGapQtr < piGap Then
               piGap = iGapQtr
            End If
         End If
      End If

      For iLoop = 1 To iMaxSets
         If iLoop = 1 Then
            iStart = 66 'B
            iEnd = 90 'Z
         Else
            iStart = 48 '0
            iEnd = 57 '9
         End If
                        
         For iASCII = iStart To iEnd 'B-Z or 0-9
            'create new buttons
            'adjust left -- could have parameter to adjust top instead, or both
            iLeft = iLeft + iWidth + piGap
            With CreateControl(sFormName, acCommandButton, iSection _
                           , , , iLeft, iTop, iWidth, iHeight)
                           
               .Name = "btn_" & sCaption
                        
               .BackColor = nBackColor
               .BorderColor = nBorderColor
               .HoverColor = nHoverColor
               .HoverForeColor = nHoverForeColor
               .ForeColor = nForeColor
               .PressedColor = 0 'black
               .PressedForeColor = 16777215 'white RGB(255, 255, 255)
               
               sCaption = Chr(iASCII)
               .Caption = "&" & sCaption
               If sClickEventName <> "" Then 'ie: LetterClick("B")
                  .OnClick = "=" & sClickEventName & "(""" & sCaption & """)"
               End If
               If sStatusBarText <> "" Then
                  .StatusBarText = sStatusBarText & sCaption
               End If
            End With
         
         Next iASCII
      Next iLoop
      
   End With
   
   Set oForm = Nothing
   
   sMsg = "Done making buttons, save form if you want to"
   Debug.Print sMsg; Tab(80); ; " * " & Now
   MsgBox sMsg, , "Done"
   
Proc_Exit:
   On Error Resume Next
   'release oFormect variables
   Set oForm = Nothing
   Exit Sub
  
Proc_Err:
   'err 2104 name already in use -- fix, or modify code to handle it
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ActiveForm_MakeLetterButtons"

   Resume Proc_Exit
   Resume

End Sub
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

'-------------------------------------------------------------------------------
'                              call_ActiveForm_MakeLetterButtons'-------------------------------------------------------------------------------
' 
Sub call_ActiveForm_MakeLetterButtons()
'190727 s4p
   'run ActiveForm_MakeLetterButtons. Specify click event to be LetterClick
   Call ActiveForm_MakeLetterButtons("LetterClick")
End Sub

Logic

1. Create a form with a command button called btn_A to pattern after.

2. Save the form before you run this procedure.

3. Run ActiveForm_MakeLetterButtons, which is a Sub that has parameters to specify Click event (or not), whether or not to make number buttons after the letter buttons (default=No), specify Gap between buttons (default is less than zero, which means calculate automatically), and put a cap on the maximum amount the gap can be, if desired.

Define error handler to give a message box and then exit. Resume is below the handler so you can break code, set it to be the next statement, and single-step to go to the problem.

Variables are dimensioned

The maximum number of buttons will be 26 for letters A-Z, or 36 if numbers are also being created. This is used to calculate gap.

Since A and 0 (zero) don't follow in ASCII (American Standard Code for Information Interchange), a variable called iMaxSets is defined to be 1 for letters or 2 for letters and numbers.

The oForm object variable is set to the active form on the screen. It must be in design view for this to work.

The form name is stored in sFormName and a message is printed to the debug window that the process is starting.

Properties are read and stored for btn_A, such as its size, color, and more. This MUST be the name of the button to pattern after, unless you change the code.

If the StatusBarText property is set, code ASSUMES the last character is a letter to replace, such as "A"

The gap between buttons is calculated if a number less than zero, such as -1, was passed for piGap, or the parameter wasn't specified.

Now we loop ... first through letters and then through numbers.

Adjust the Left property for the next button according to the Left property of the last button, its width, and the gap.

Create a new command button control and set its properties. It will be in the same section as btn_A.

The Caption will be set to &L WHERE L is the letter, so the hot key will be Alt+Letter.

If sClickEventName was passed, make a Click event that calls it and sends the button caption. For instance, =LetterClick("B")

If StatusBarText is to be set, append a space and then the button caption.

After the loop is done, there is a message that it is done and to save the form if desired.

To finish, the oForm object variable is released.

The form is still open, but not saved. If you like what happened, you can save it. Otherwise, close and don't save.

4. Save the form if you like the changes

Parameters

Optional:

sClickEventName

Click event that runs for each letter or number

pbNumbersToo

True to also make 0-9 buttons. Default is False.

pbGapQuarterMax

True if you want the maximum calculated gap between buttons to be 1/4 width of the pattern button .

piGap

Spacing between buttons will be whatever number (0 or more) that you specify.
-1 = calculate gap (actually, any number less than zero)

VBA > Control > Make Letter Buttons

Quick Jump

Example

Call ActiveForm_MakeLetterButtons("LetterClick")

Properties

Other

Format

Event

=LetterClick("B")

Code

' ActiveForm_MakeLetterButtons

' call_ActiveForm_MakeLetterButtons

Logic

Parameters

Optional:

sClickEventName

pbNumbersToo

pbGapQuarterMax

piGap

Notes

Backstory

Reference

Screen.ActiveForm

Docs / Office VBA Reference / Access / Object Model / Screen object / Properties / ActiveForm

CommandButton object

Docs / Office VBA Reference / Access / Object Model / CommandButton object

CreateControl

Docs / Office VBA Reference / Access / Object Model / CommandButton object

Tab function

Docs / Office VBA Reference / Language reference / Reference / Functions / Tab

Share

Do you have something to say?