Simulating VB Controls in VBA Code

aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Published:

Introduction

While answering a recent question in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code on my first attempt.  This is a quick tip about simulating VB classic controls in the VBA code environment.

First Attempt Does Not Work

To answer the (textbox keypress event) question, I needed to access the text and selstart properties of a textbox control.  I added a couple of variables in the General Declarations section of a module for the properties.  I set the variables in the Immediate window and got the following code to return the correct answer.
Option Explicit
                      
                      Public SelStart As Long
                      Public Text18 As String
                      
                      
                      Private Sub Text18_KeyPress(KeyAscii As Integer)
                          Dim strParsed() As String
                          Dim strPostKeyValue As String
                          If SelStart = 0 Then
                              strPostKeyValue = Chr(KeyAscii) & Text18
                          Else
                              strPostKeyValue = Left$(Text18, SelStart) & Chr(KeyAscii) & Mid$(Text18, SelStart + 1)
                          End If
                          strParsed = Split(strPostKeyValue, "x", , vbTextCompare)
                          If (UBound(strParsed) >= 0) And (UBound(strParsed) < 2) Then
                              If IsNumeric(strParsed(LBound(strParsed))) And (IsNumeric(strParsed(UBound(strParsed))) Or Len(strParsed(UBound(strParsed))) = 0) Then
                              Else
                                  KeyAscii = 0
                              End If
                          Else
                              If IsNumeric(Chr(KeyAscii)) Then
                              Else
                                  KeyAscii = 0
                              End If
                          End If
                      
                      End Sub
                      

Open in new window

Since the default property of a textbox is its Text property, I thought this would be simple enough to replace the variable references with Text18 qualified references.  Unfortunately, I missed a couple and could not use the compiler to double-check my syntax.

Note: The feedback I received was "It doesn't work."  Unfortunately, the questioner didn't have an Option Explicit statement or my mistakes would have been caught immediately.

Simulating the Textbox Properties

The solution to this code-only simulation is to create a structure for the textbox properties.  This is done with a Type‚ĶEnd Type statement as shown below.

Option Explicit
                      
                      Type TextBoxProperties_struc
                          Text As String
                          SelStart As Long
                          SelLength As Long
                      End Type
                      
                      Public Text18 As TextBoxProperties_struc
                      
                      
                      Public Sub Text18_KeyPress(KeyAscii As Integer)
                          Dim strParsed() As String
                          Dim strPostKeyValue As String
                          If Text18.SelStart = 0 Then
                              strPostKeyValue = Chr(KeyAscii) & Text18.Text
                          Else
                              strPostKeyValue = Left$(Text18.Text, Text18.SelStart) & Chr(KeyAscii) & Mid$(Text18.Text, Text18.SelStart + 1)
                          End If
                          strParsed = Split(strPostKeyValue, "x", , vbTextCompare)
                          If (UBound(strParsed) >= 0) And (UBound(strParsed) < 2) Then
                              If IsNumeric(strParsed(LBound(strParsed))) And (IsNumeric(strParsed(UBound(strParsed))) Or Len(strParsed(UBound(strParsed))) = 0) Then
                              Else
                                  KeyAscii = 0
                              End If
                          Else
                              If IsNumeric(Chr(KeyAscii)) Then
                              Else
                                  KeyAscii = 0
                              End If
                          End If
                      
                      End Sub
                      

Open in new window


Now, the compiler catches all the syntax errors.  It does prevent me from using the default property, but that is a small price to pay for the convenience of answering such VB classic form/control related questions in the VBA environment.

For more complicated control properties, you might need to create a class or create a user form with the particular type of control you need.  
Note: User form controls aren't the same as VB classic controls.  Sometimes they have different properties and methods.
 
=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=
If you liked this article and want to see more from this author,   please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=
2
4,378 Views
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.