Access VBA - Changing TextBox value from within a function (ByRef)

Question

I'm trying to write a sub that will get two parameters - a textbox in a form and a text. My intention is that the function will append the text into any textbox.

Sub AppendTextBox([any textbox in my form], text As String)

[code that appends the text parameter to the textbox]

End Sub

Please note that I'm not trying to append text to a specific textbox, but to create a function that can receive any textbox in the form and append it with any text.

Thanks for your help.

textbox.Value = AppendText(textbox.Value, text) text1 gets textbox.value and text2 gets the parameter text when AppendTextBox is used. — Zephram, Jun 04 '13 at 22:15

score 5 · Accepted Answer · answered Jun 11 '13 at 13:46

5

I've found the answer and it's much simpler than I though it is:

Private Sub AAA(A)

A.Value = "Desired text"

End Sub

Or if you want to append:

Private Sub AAA(A)

A.Value = A.Value & vbnewline & "Desired text"

End Sub

answered Jun 11 '13 at 13:46

Zephram

499
3
7
16

I get this error trying to change the value: you cant assign a value to this object. what can the problem be? – dieKoderin Aug 10 '16 at 12:58
Somehow, this helped me with a error that I had with record-sets and collections. I was looping through a dao recordset and kept a field of my table inside a collection. I had the weird error of "No Current Record Found" when looping over the collection later. Using the `.Value` property instead of the result("property") syntax itself, I got it to work. Thank you, I guess :) – Mafii Feb 13 '18 at 15:35

Pedro · Answer 2 · 2017-06-13T16:40:24.683

Hi Zephram have you managed to find the solution for this? I have one but is a bit heavy because it uses loops. If you have one better let me know.

Private Function change_TextBox2(altera As String, textbox As ctl, valor As Variant)
Dim ctl As Control
If altera = "Popula" Then
    For Each ctl In Me.Controls
        With ctl
         If (InStr(.Name, textbox)) > 0 Then
             .Value = valor
         End If
       End With
    Next ctl
ElseIf altera = "hide" Then
    For Each ctl In Me.Controls
        With ctl
         If (InStr(.Name, textbox)) > 0 Then
             .Visible = False
         End If
       End With
    Next ctl
End If
End Function

score 0 · Answer 3 · answered Jun 07 '19 at 07:34

0

can be accomplished by:

dim appendTxt as String:  appendTxt = "appended text"
dim ws as Worksheet:  for each ws in ActiveWorkbook.Worksheets
    dim shape as Shape:  for each shape in ws.Shapes
        if shape.Type = msoTextBox then
            'you can move this code parameterized to a separate function then as req by OP:
            with shape.TextEffect:  .Text = .Text & appendTxt
        end if
    next shape
next ws

answered Jun 07 '19 at 07:34

Andreas Covidiot

4,286
5
51
96

This is VBA code for Excel, it will not work on MS Access, sorry Andreas, try again. – Reverus Oct 03 '19 at 21:00
Haha :) I did not recognize because it seemed a verb to me ... "how to access vba textbox" :) ... anyways it helped me with Excel so it may help others. I do not use Access VBA, sorry :-/ – Andreas Covidiot Oct 05 '19 at 12:51

Access VBA - Changing TextBox value from within a function (ByRef)

3 Answers3