<

Simulating VB Controls in VBA Code

Published on
9,597 Points
3,397 Views
2 Endorsements
Last Modified:
Approved

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
Comment
Author:aikimark
0 Comments

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month