• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

How to Create Textboxes at Runtime in a Userform with VBA|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / How to Create Textboxes at Runtime in a Userform with VBA|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHow to Create Textboxes at Runtime …
sp_PrintTopic sp_TopicIcon
How to Create Textboxes at Runtime in a Userform with VBA
Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
1
June 25, 2020 - 12:12 am
sp_Permalink sp_Print

Hi everyone, my name is Maurizio and my problem is this:
In a userform I inserted a Textbox and a CommandButton
Now my question is this:
Would it be possible to create more (Textbox) (Dynamics) just by pressing the (Button) even just five or six times obtaining the same dimensions as the first one?
I managed to create the first textbox (Dynamic), it would be enough for me to be able to create all the others at each (Press) of the button.
Thanks to everyone who wants to help me out.
Greetings from Maurizio

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
2
June 25, 2020 - 7:30 am
sp_Permalink sp_Print sp_EditHistory

Hello,

An example that you can use and adjust to your needs

 

Private ctrlx As Collection

Private Sub AddNewControls_Textbox()

Dim i As Long
Dim add_new_ctrl As Control

On Error Resume Next

For i = 1 To 6 ' to add 6 new textbox
     Set add_new_ctrl = Me.Controls.Add("Forms.TextBox.1", "NewTextbox" & i) 'you can change the name "NewTexbox"

' with the same size as the existing textbox
          add_new_ctrl.Top = Me.TextBox1.Top + i * 20
          add_new_ctrl.Left = Me.TextBox1.Left
          add_new_ctrl.Width = Me.TextBox1.Width
          add_new_ctrl.Height = Me.TextBox1.Height
          add_new_ctrl.Text = "Hello World..."
          ctrlx.Add add_new_ctrl, add_new_ctrl.Name

Next

If Not add_new_ctrl Is Nothing Then Set add_new_ctrl = Nothing

End Sub

Private Sub UserForm_Initialize()

Set ctrlx = New Collection

End Sub

 

Miguel

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1549
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
3
June 25, 2020 - 10:10 am
sp_Permalink sp_Print

Thanks Miguel,

Just a note.  If you turn off error handling

On Error Resume Next

you should turn it back on after the expected error has occurred (or not), or write your own error handler.

Regards

Phil

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
4
June 25, 2020 - 3:00 pm
sp_Permalink sp_Print

Hi Miguel Santos and Hi also to Philip Treacy:

Haora: Miguel I like your listing even though to tell the truth I had already done something like this a few years ago for another database project.

But perhaps Google has not translated my request well since I asked that the Text (Dynamics) not be created all at once; But on pressing the key

Surely in this case you should do a (For x 1 to 5 - Next) only that I don't know how to write the whole thing
And this is where the difficulty lies?

Thanks anyway for your interest

Do I fully support Philip's question?
Hello everyone and good luck
By A.Maurizio

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 688
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
5
June 25, 2020 - 6:12 pm
sp_Permalink sp_Print

So you want one new textbox per click of the button? Where should it be placed?

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
6
June 25, 2020 - 6:46 pm
sp_Permalink sp_Print

Hi Velouria
Yes the intent was this:
But I have reached a point in my project; Also following your advice and help
That would also be fine.
The only thing that I would still like to obtain would be to have a progressive number from 1 to 6 that would gradually increase by just pressing the button
Example :
Now I have to put the number by hand in the Text box2
While I wish it was automatic
Take a look at my latest job.
And let me know if it's feasible; And what do you think?
Thank you

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
7
June 25, 2020 - 9:25 pm
sp_Permalink sp_Print

Hello, yes Philip,

A.Maurizio, I'll take a look at your workbook

You want something like that?

 

Option Explicit

Private ctrlx As Collection

Private Sub AddNewControls_Textbox()

Dim add_new_ctrl As Control
Dim ctrl As MSForms.Control
Dim x As Integer
x = 0

For Each ctrl In Me.Controls
     If TypeOf ctrl Is MSForms.TextBox Then
          x = x + 1
     End If
Next ctrl

Debug.Print x

If x >= 6 Then
     MsgBox "Last textbox has already been added!"
     GoTo ExitSub:
End If

Set add_new_ctrl = Me.Controls.Add("Forms.TextBox.1", "NewTextbox" & x + 1) 'you can change the name "NewTexbox"

' with the same size as the existing textbox
add_new_ctrl.Top = Me.TextBox1.Top + x * 20
add_new_ctrl.Left = Me.TextBox1.Left
add_new_ctrl.Width = Me.TextBox1.Width
add_new_ctrl.Height = Me.TextBox1.Height
add_new_ctrl.Text = "Hello World..."

If Control_Exists("NewTextbox" & x, Me) = True Then
     'Cancel is true
Else
     ctrlx.Add add_new_ctrl, add_new_ctrl.Name
End If

ExitSub:
     If Not add_new_ctrl Is Nothing Then Set add_new_ctrl = Nothing
Exit Sub

End Sub

Public Function Control_Exists(ctl_Name As String, ByRef MyForm As UserForm) As Boolean

Dim ctrl As Control

On Error GoTo ErrHandler

For Each ctrl In MyForm.Controls
     If ctrl.Name = ctl_Name Then
          Control_Exists = True
          Exit For
     End If
Next ctrl

ExitFunction:
     Exit Function
ErrHandler:
     Debug.Print Err, Err.Description
     Resume ExitFunction
End Function

Private Sub UserForm_Initialize()

Set ctrlx = New Collection

End Sub

Private Sub CommandButton1_Click()

AddNewControls_Textbox

End Sub

 

Miguel

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
8
June 26, 2020 - 2:52 am
sp_Permalink sp_Print

Hi Miguel
You are uin Grande
what you did was just what I wanted to do from the beginning
Thanks Infinite
And thanks also to everyone else
Now I just have to try to give it a better coordinate to all the textboxes and then the game is done.

Now I want to reveal the secret of my request.
You know the Fatitic Bar of the operating system objects (Windows-Vista) the one where all the (Gadgets) were
Among these was one that has always intrigued me: And it was BlookNotes
Where you had the option of Writing in multiple sheets and all had their place in the memory of (Windows)
Well I want to create this
All the rest I had already created in other programs including dynamic (TextBox).
But never done this way here!
(P.S) I had also made that classic Analog Clock with six melts of different dials; Including the Seven-Sided Digital one and the Weather.
But all this and all not another story.
Again Thanks to all of you and especially to you who kindly offered me this wonderful Project.

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
9
June 26, 2020 - 8:00 pm
sp_Permalink sp_Print

Hello A.Maurizio,

 

I just did what is always done in this wonderful and powerful Forum

Good luck with your project, it's a good idea 😀

 

Regards

Miguel

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
10
June 27, 2020 - 12:22 am
sp_Permalink sp_Print sp_EditHistory

Thanks Infinite Miguel
You are a friend
Greetings and Happy Weekend

(p.S) Listen to Miguel
I would like to ask you this again:
If you had to follow your project
How do I go about Identifying the (First Textbox) Rather than the (Second and so on?

I'll explain:
When I did a database program with dynamic Label and textbox many years ago
Nor did I declare their quantity and value through a series of cells selected on sheet 1
Then I gave their Voices once the form opened and the database started from there; With all the data that was saved in a Document (Txt)
Thereforep when I recalled everything from (Txt) to (Textbox) the data already knew where to go as there were precise coordinates.

Therefore in your Case: How do I identify one of these 5 (Textbox) with your method?

Thanks right now for all the tips you want to give me about it Hello

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
11
June 27, 2020 - 7:56 pm
sp_Permalink sp_Print

Hello, 

A.Maurizio, I don't know if this is what you asked me, but I leave here 3 examples

 

First example: Update to add new textbox

 

Private Sub AddNewControls_Textbox()

Dim add_new_ctrl As Control
Dim ctrl As MSForms.Control
Dim x As Integer
x = 0

For Each ctrl In Me.Controls
     If TypeOf ctrl Is MSForms.TextBox Then
          x = x + 1
     End If
Next ctrl

Debug.Print x

If x = 0 Then
     MsgBox "Error: Textbox1 does not exist!"
     Exit Sub
End If

If x >= 6 Then
     MsgBox "Last textbox has already been added!"
     Exit Sub
End If

If Control_Exists("NewTextbox" & x, Me) = True Then
     MsgBox "Error: Textbox Already exists!"
     Exit Sub
End If

Set add_new_ctrl = Me.Controls.Add("Forms.TextBox.1", "NewTextbox" & x) 'you can change the name "NewTexbox"

' with the same size as the existing textbox
add_new_ctrl.Top = Me.TextBox1.Top + x * 20 ' 20 is textbox.height(in this example is 18) = (18+2)
add_new_ctrl.Left = Me.TextBox1.Left
add_new_ctrl.Width = Me.TextBox1.Width
add_new_ctrl.Height = Me.TextBox1.Height

Select Case x
     Case 1
          add_new_ctrl.ForeColor = vbRed
          add_new_ctrl.Font.Size = 10
          add_new_ctrl.Text = "This Textbox name: " & add_new_ctrl.Name
          '--------------------------------
          'you can add more cod here if you want
          '--------------------------------
     Case 2
          add_new_ctrl.ForeColor = vbBlack
          add_new_ctrl.Font.Size = 12
          add_new_ctrl.Text = "This Textbox name: " & add_new_ctrl.Name
          '--------------------------------
          'you can add more cod here if you want
          '--------------------------------
     Case 3
          add_new_ctrl.ForeColor = vbGreen
          add_new_ctrl.Font.Size = 14
          add_new_ctrl.Text = "This Textbox name: " & add_new_ctrl.Name
          '--------------------------------
          'you can add more cod here if you want
          '--------------------------------
     Case 4
          add_new_ctrl.ForeColor = vbBlue
          add_new_ctrl.Font.Size = 8
          add_new_ctrl.Text = "This Textbox name: " & add_new_ctrl.Name
          '--------------------------------
          'you can add more cod here if you want
          '--------------------------------
     Case 5
          add_new_ctrl.ForeColor = vbYellow
          add_new_ctrl.Font.Size = 10
          add_new_ctrl.Text = "This Textbox name: " & add_new_ctrl.Name
          '--------------------------------
          'you can add more cod here if you want
          '--------------------------------
     Case Else
          '--------------------------------
          'add some cod here if you want
          '--------------------------------
End Select

ctrlx.Add add_new_ctrl, add_new_ctrl.Name

If Not add_new_ctrl Is Nothing Then Set add_new_ctrl = Nothing

End Sub

 

Second example: assuming you want to save the data for each textbox on the excel sheet

with the function to determine if control(textbox) exists

 

Private Sub CommandButton2_Click()

Dim ctrl As MSForms.TextBox
Dim i As Integer

With Application.ThisWorkbook.Worksheets("Folha1")
     .Select
     .Range("A1").Value = Me.TextBox1.Text
     For i = 1 To 5 ' only for the 5 new textbox
          On Error Resume Next
          Set ctrl = Me.Controls("NewTextbox" & i)
          On Error GoTo 0
          If Control_Exists("NewTextbox" & 1, Me) = True Then
               If ctrl.Name = "NewTextbox1" Then
                    .Range("A2").Value = ctrl.Text
               End If
          Else
               .Range("A2").Value = "No data!"
          End If
          If Control_Exists("NewTextbox" & 2, Me) = True Then
               If ctrl.Name = "NewTextbox2" Then
                    .Range("A3").Value = ctrl.Text
               End If
          Else
               .Range("A3").Value = "No data!"
          End If
          If Control_Exists("NewTextbox" & 3, Me) = True Then
               If ctrl.Name = "NewTextbox3" Then
                    .Range("A4").Value = ctrl.Text
               End If
          Else
               .Range("A4").Value = "No data!"
          End If
          If Control_Exists("NewTextbox" & 4, Me) = True Then
               If ctrl.Name = "NewTextbox4" Then
                    .Range("A5").Value = ctrl.Text
               End If
          Else
               .Range("A5").Value = "No data!"
          End If
          If Control_Exists("NewTextbox" & 5, Me) = True Then
              If ctrl.Name = "NewTextbox5" Then
                   .Range("A6").Value = ctrl.Text
              End If
          Else
               .Range("A6").Value = "No data!"
          End If
     Next i
End With

If Not ctrl Is Nothing Then Set ctrl = Nothing

End Sub

 

third example: to get the control index number

you can use the index number of the control to use in macros

 

Private Sub CommandButton3_Click()

Dim ctrl As MSForms.Control
Dim x As String

For Each ctrl In Me.Controls
     If TypeOf ctrl Is MSForms.TextBox Then
          x = x & ctrl.Name & " - " & ctrl.TabIndex & vbCrLf
     End If
Next ctrl

MsgBox x

End Sub

 

Miguel

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
12
June 28, 2020 - 12:16 am
sp_Permalink sp_Print

Thanks Infinite Miguel
And more than I asked for!

I must say that you are an inexhaustible Baggage of Computer Science

Nor will I surely make good use of your help Thanks Infinite
Happy weekend Sincere greetings from A.Maurizi2

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Terry Hedditch, Tracy English, Mohamed Touahria
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
David von Kleek
Ronald White
Ginette Guevremont
Taryn Ambrosi
Mark Davenport
Christy Nichols
Harald Endres
Ashley Hughes
Herbie Key
Trevor Pindling
Forum Stats:
Groups: 3
Forums: 24
Topics: 6528
Posts: 28594

 

Member Stats:
Guest Posters: 49
Members: 32817
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.