control arrays in Excel 2

[tatochka]

I could not figure out how to use the control arrays in VBA for Excel. Can somebody give me a hint how to use a loop instead of having the code I do.

    txtNoUnits1.Text = Format(txtNoUnits1.Text, "##,###,##0")
    txtNoUnits2.Text = Format(txtNoUnits2.Text, "##,###,##0")
    txtNoUnits3.Text = Format(txtNoUnits3.Text, "##,###,##0")
    txtNoUnits4.Text = Format(txtNoUnits4.Text, "##,###,##0")
    txtNoUnits5.Text = Format(txtNoUnits5.Text, "##,###,##0")
    txtNoUnits6.Text = Format(txtNoUnits6.Text, "##,###,##0")
    txtNoUnits7.Text = Format(txtNoUnits7.Text, "##,###,##0")
    txtNoUnits8.Text = Format(txtNoUnits8.Text, "##,###,##0")
    txtNoUnits9.Text = Format(txtNoUnits9.Text, "##,###,##0")
    txtNoUnits10.Text = Format(txtNoUnits10.Text, "##,###,##0")
    txtNoUnits11.Text = Format(txtNoUnits11.Text, "##,###,##0")
    txtNoUnits12.Text = Format(txtNoUnits12.Text, "##,###,##0")
    txtNoUnits13.Text = Format(txtNoUnits13.Text, "##,###,##0")
    txtNoUnits14.Text = Format(txtNoUnits14.Text, "##,###,##0")
    txtNoUnits15.Text = Format(txtNoUnits15.Text, "##,###,##0")

I use soemthing like this a lot in my application and I know it is wrong, but don't know how to loop through controls or cells. I am new in VB. I would appriciate any help. Thanks.

 

[dsi]

Each textbox would have to be part of the control array. The easiest way to start this would be to insert a textbox on your form. For this example, call it txtNo. Then, copy and paste the textbox. You will be asked if you want to create a control array for the control. Answer yes! That will turn your first textbox into the object txtNo(0) and the copied one will be txtNo(1). Then, you can use a loop:

Dim i As Long
For i = 0 to 14
  txtNo(i).Text = Format(txtNo(i).Text, "#,##0")
Next i

 

[DarkSun]

This isn't the best way to do it,BUT it will work.

Dim TempObject As Object
Dim i As Integer
i = 1
For i = 1 To 15
Set TempObject = Sheet1.OLEObjects("TextBox" & i).Object
TempObject.Text = Format(TempObject.Text, "##,###,##0&quot
Next i

You will need to replace Sheet1 with where your objects are located.

Hopefully someone else will know how to evaluate a string to an Object.

eg. ("TextBox" & i).Text

 

[DarkSun]

dsi,
Of course you know that won't work if you are using Office 97?? (VBA)

Or can you?? I maybe wrong. I haven't found a way to do it.

 

[dsi]

Oh yeah! I keep forgetting which forum I am in! Thanks DarkSun.

 

[tatochka]

DarkSun,
My text boxes are located on user form not on the excel sheet, so I can't use OLEObjects. I use excel sheets only to store data that users typed in form. So, I stil don't see the solution to my problem. Does somebody else have some ideas. I write tones of code instead of just one loop.

 

[DarkSun]

tatochka,

Okay use this code then.

Dim TempObject As Object
Dim i As Integer
i = 1
For i = 1 To 15
Set TempObject = UserForm1.Controls.Item("TextBox" & i)
TempObject.Text = Format(TempObject.Text, "##,###,##0&quot
Next i

Make sure you replace "UserForm1" with the name of your form.

You could change the code to this:

Dim i As Integer
i = 1
For i = 1 To 4
UserForm1.Controls.Item("TextBox" & i).Text = Format(UserForm1.Controls.Item("TextBox" & i).Text, "##,###,##0&quot
Next i

It's up to you.

Let me know if you have any problems.

 

[tatochka]

DarkSun,

Thanks a lot. It did work. I got rid of probably have of my not needed code.

 

[tatochka]

DarkSun,

Would not you know, by the chance, how to do the same thing with event procedure.
That is what I have a lot in my application:

Private Sub cboUnitType2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Beep
KeyAscii = 0
End Sub

Private Sub cboUnitType3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Beep
KeyAscii = 0
End Sub

Private Sub cboUnitType4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Beep
KeyAscii = 0
End Sub

Private Sub cboUnitType5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Beep
KeyAscii = 0
End Sub

Private Sub cboUnitType6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Beep
KeyAscii = 0
End Sub

Private Sub cboUnitType7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Beep
KeyAscii = 0
End Sub

 

[DarkSun]

I'm not Sure if it's possible.

You could shorten it a bit by setting up a Sub to do the Beep and KeyAscii = 0.

This would also mean that if you need to change what it does you only need to change it in one place.

Private Sub ComboEvent()
Beep
KeyAscii = 0
End Sub

Then Place "Call ComboEvent" in each event.

This will tidy it up a bit.

 

[Guest_imported]

how to get handle of a VBA control