VBA Code Help

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
748
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi,

I am writing a macro involves a user form. I am adding the controls like command button,textbox in run time. Now my problem is how to write the procedure for the events of that added controls.

I know how to write event procedure for the control's added in design time.

Thanks

GNaga
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Probably I might have not explained clearly.

In my VBA project I have a userform. I have declared a variable to hold textbox control in the declaration seciton of that userform

Like this
Dim txtprod(1 To 5) As Object

Then I have added the text box into the form using the fol code

For I = 1 To 5
Set txtprod(I) = Controls.Add("Forms.TextBox.1", "txtProd" & CStr(I))
If I = 1 Then
txtprod(I).Left = 175
txtprod(I).Top = 50
txtprod(I).Width = 50
txtprod(I).Height = 18
Else
txtprod(I).Left = 175
txtprod(I).Top = ((txtprod(I - 1).Top + 40))
txtprod(I).Width = 50
txtprod(I).Height = 18
End If
txtprod(I).TextAlign = 1
txtprod(I).Font.Name = "Tahoma"
txtprod(I).FontSize = 8
txtprod(I).FontBold = True
txtprod(I).ForeColor = &H80000012
Next I


Now I want to write the code for one of these textbox's change event. I do not know how to start

If I use like this it is not working

Private Sub txtProd(1)_Change()

End Sub


Any Help Please

Thanks

GNaga
 
Upvote 0
Thanks Andrew. It is not working. I mean the control is not sensing the event so the code inside the Private Sub txtProd1_Change() procedure is not executing. Simply I tried like this

Private Sub txtProd1_Change()

Msgbox "You Got It"

End Sub

GNaga
 
Upvote 0
Yes. In my userform module I have only two procedures one is userform_Initialize event and the next one is this

Private Sub txtProd1_Change()

Msgbox "You Got It"

End Sub

Thanks

GNaga
 
Upvote 0
I used your code and the event didn't fire for me either. The name is right. It's as if the event procedure doesn't compile unless the control is present. So I tried adding the event procedure in code using the VBIDE and Excel crashed on me!

Why don't you just add the textboxes at design time? You can set their visible and enabled properties at run time.
 
Upvote 0
Unfortunately VBA does not allow you to create object arrays at design time.

Did anybody find a solution to this problem yet? I've tried and been unable to get the code for the change event to run.

Dave
 
Upvote 0
Thanks for you all. I did'nt get any solution yet. But the link given by Ekim seems to be the one I wanted but I do not know how to get it worked. The link is talking about a function in that the following code is the one will fulfill my requirement

'Add event-hander subs for the TextBox

With UserForm1.CodeModule
X = .CountOfLines
.InsertLines X + 1, "Sub txtProd1_Change()"
.InsertLines X + 2, " Msgbox "You Got It""
.InsertLines X + 3, "End Sub"
End With

In which procedure I have write this kind of code to handle the controls event?

GNaga
This message was edited by gnaga on 2002-09-27 22:46
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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