CODEWITHSUNDEEP
ms-accessvba
R. Amaro
R. Amaro

Reputation: 41

Auto-Generate Textboxes with code in Access VBA

I tried using Auto Subs, but I can't understand that well..

I have Multiple (In this case 59) Textboxes/Buttons in a Form, everything with a Click Event. But in VBA I don't want to make every single one with code because I want to make the Form easy to control and use it to someone who doesn't code, and having 59 Click Subs would be quite ugly and if I wanted to add more I would need to create in the VBA Code another Sub.

The real question is: Is there some way to do Auto Code (Sub) for every Text/ Button with different variables?

E.G.:

Text1_Click + Code -> Variable: 1
Text2_Click + Code -> Variable: 2
Text3_Click + Code -> Variable: 3   --> Text(?)_Click + Code -> Variable: ?
Text4_Click + Code -> Variable: 4
Text5_Click + Code -> Variable: 5

Upvotes: 2

Views: 1562

Answers (3)

HansUp
HansUp

Reputation: 97101

Create a VBA function and use it for each text box's On Click property: =MyFunction()

In your example, the text boxes are named Text1 through Text5 and it seems you want Variable to contain the number from the text box name. It's easy to extract that number with Mid().

Public Function MyFunction()
    Dim strControl As String
    Dim Variable As Variant

    strControl = Application.Screen.ActiveControl.Name
    Variable = Null
    If strControl Like "Text*" Then
        Variable = Val(Mid(strControl, 5))
    End If
    Debug.Print strControl & "_Click -> Variable: " & Nz(Variable, "Null")
End Function

If you want to extend that approach to numbered command buttons, include this ...

    If strControl Like "Command*" Then
        Variable = Val(Mid(strControl, 8))
    End If

If you need to assign the On Click property programmatically, loop through the controls and set each .OnClick = "=MyFunction()"

Upvotes: 2

Nathan_Sav
Nathan_Sav

Reputation: 8531

In your form have the buttons, and the following code

Option Explicit

Public colCustomControls As Collection

Private Sub Form_Open(Cancel As Integer)

Dim ctl As Control
Dim clsCustom As clsCustomButton

    Set colCustomControls = New Collection

    For Each ctl In Me.Controls
        Set clsCustom = New clsCustomButton
        clsCustom.INITIALISE ctl
        colCustomControls.Add clsCustom
    Next ctl

End Sub

then a class called clsCustomButton

Option Explicit

Private WithEvents cbCustom As CommandButton

Public Sub INITIALISE(cb As CommandButton)
    Set cbCustom = cb
    cb.OnClick = "[Event Procedure]"
End Sub

Private Sub cbCustom_Click()
    Select Case cbCustom.Name
        Case "Command0": MsgBox "Button1 clicked"
        Case "Command1": MsgBox "Button2 clicked"
    End Select
End Sub

What is happening is the collection is mimicking, the me.Controls collection in the form, but we are making all our buttons be the class, so thus, using the class _click event. You could modify the initialise to also take a proc name event, for the sub call, then use application.Run to call the code.

Like so

Option Explicit

Private WithEvents cbCustom As CommandButton
Private strProcCall As String

Public Sub INITIALISE(cb As CommandButton, strProc As String)
    Set cbCustom = cb
    strProcCall = strProc
    cb.OnClick = "[Event Procedure]"
End Sub

Private Sub cbCustom_Click()
    Application.Run strProcCall
End Sub

and

Option Explicit

Public colCustomControls As Collection

Private Sub Form_Open(Cancel As Integer)

Dim ctl As Control
Dim clsCustom As clsCustomButton

    Set colCustomControls = New Collection

        Set clsCustom = New clsCustomButton
        clsCustom.INITIALISE Me.Controls("Command0"), "MACRO_1"
        colCustomControls.Add clsCustom

        Set clsCustom = New clsCustomButton
        clsCustom.INITIALISE Me.Controls("Command1"), "MACRO_2"
        colCustomControls.Add clsCustom

End Sub

Upvotes: 1

Gustav
Gustav

Reputation: 55806

Yes, you can refer to a control with:

n = 5
Set ctl = Me("Text" & CStr(n))

then ctl will refer to Me!Text5

But look into WithEvents: How to write generic code ...

Upvotes: 0

Related Questions