Multiple textboxes - same code

L

lpvdsteen

Board Regular
Joined
Jan 21, 2003
Messages
161
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have a number of textboxes, or other boxes, using exactly the same code. In visual basic you can assign an index to these boxes and create one code where the index number specifies the box you are working with.

I have tried finding a way to do thing in VBA, but came up against a blank. I realise that this is either not possible or very simple, but right now I am stuck with the 'not possible'. Does anyone know if the 'very simple' is an option. It would greatly decrease the size of my program, make it easier to visualise and not make me change to much each time.
Of course I refer to subs as much as possible making these routines 3 line routines (sub-call-endsub), but still there are a lot of textbox1_click() routines whereas textbox_click(index) would be nicer.

Luke
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
All related textboxes are on the same userform. I may have bundled some of them into frames to be able to differentiate between sets.

I have not worked with class modules (yet) May open up a brand new door to me. Can you expand??

say a code like this

sub datebox1_afterupdate()
if isdate(datebox1.text) then
msgbox "correct date"
else
msgbox "this is not a date"
endif
end sub

where this goes for say 5 dateboxes
Luke
 
Upvote 0
Here is some sample code:

Code: 
'   Class module named TBClass

Public WithEvents TBGroup As MSForms.TextBox

Private Sub TBGroup_Change()
    MsgBox TBGroup.Name & " changed"
End Sub

'   UserForm module

Dim TBs() As New TBClass

Private Sub UserForm_Initialize()
    Dim TBCount As Integer
    Dim Ctrl As Control
    TBCount = 0
    For Each Ctrl In UserForm1.Controls
        If TypeName(Ctrl) = "TextBox" Then
            TBCount = TBCount + 1
            ReDim Preserve TBs(1 To TBCount)
            Set TBs(TBCount).TBGroup = Ctrl
        End If
    Next Ctrl
End Sub

That uses the Change event, to which you can add code as required. In the Class module click TBGroup in the left hand dropdown and you will get a list of events in the right hand dropdown. Not all the expected events are available, particularly AfterUpdate, BeforeUpdate, Enter and Exit.
 
Upvote 0
in the meantime started expirimenting with class modules. Works great. Thanks for the tip and for the sample code
 
Upvote 0
Hi Andrew Poulsom,

I copied the entire code and pasted in the userform. I do not have any other code on the form.
I guess I am missing some code. Where do I write code for textbox.

Please revert.

Thanks.
 
Upvote 0
The first part of the code goes in a Class mdule named TBClass:

Code: 
'   Class module named TBClass

Public WithEvents TBGroup As MSForms.TextBox

Private Sub TBGroup_Change()
    MsgBox TBGroup.Name & " changed"
End Sub
 
Upvote 0
Hi Andrew Poulsom,

I'm sorry, I did not get you.

I have 4 textboxes - textbox1, textbox2, textbox3, textbox4 and a Command button - commandbutton1.

The user enters value on textbox1 and textbo3 as 5 and 7 respectively.
On clicking the Command Button, the output should be produced as 25 and 35 in textbox2 and textbox4 respectively assuming output = input * 5.

Please let me know the code for above requirement so that I can code accordingly.

Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Userform edit multiple textboxes at the same time
Replies
4
Views
96
Formula11
F
A
  • Question Question
Decimal display in textboxes.
Replies
3
Views
140
DanteAmor
DanteAmor
P
  • Question Question
Multiple textbox calculation.
Replies
2
Views
176
Fluff
Fluff
P
  • Question Question
If and ElseIf confusion !
Replies
5
Views
248
Micron
M
I
  • Solved
Check each Textbox has a value before running code
Replies
3
Views
372
ipbr21054
I

Forum statistics

Threads
1,223,975
Messages
6,175,749
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top