<

Go Premium for a chance to win a PS4. Enter to Win

x

Simulating VB Controls in VBA Code

Published on
9,435 Points
3,235 Views
2 Endorsements
Last Modified:

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month