User Forms
 

I have a user form with a series of text boxes txtA txtB to txtZ
I have a series of routines to interchange data betwwen the text boxes
and an array,
e.g
vaData(1, 7) = txtA
vaData(1, 8) = txtB
vaData(1, 9) = txtC
vaData(1, 10) = txtD
vaData(1, 11) = txtE
vaData(1, 12) = txtF
vaData(1, 13) = txtG
how can I creat a loop to cycle through the text boxes?

nev



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

User Forms
 

Nev,

Something like this. I haven't tested it live but it should give you the
idea.

Dim nCounter as integer

With MyForm
for nCounter = 65 to 90
vaData(1,nCounter-58) = .Controls("txt" & Chr(nCounter)).Text
next nCounter
End With

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in


"Nev" wrote in message
...
I have a user form with a series of text boxes txtA txtB to txtZ
I have a series of routines to interchange data betwwen the text boxes
and an array,
e.g
vaData(1, 7) = txtA
vaData(1, 8) = txtB
vaData(1, 9) = txtC
vaData(1, 10) = txtD
vaData(1, 11) = txtE
vaData(1, 12) = txtF
vaData(1, 13) = txtG
how can I creat a loop to cycle through the text boxes?

nev



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

User Forms
 

Try this:

For intVar = 1 to 26
vaData(1, intVar) = UserForm1.Controls("txt" & chr(64 + intVar))
Next intVar

Let me know if that doesn't work.

Mark
---
Mark Bigelow
mjbigelow at hotmail dot com
http://hm.imperialoiltx.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

User Forms
 

Thanks Mark works fine, have three other similar situations -

If txtA.Value = "" Then
txtA.Value = 0
Else
txtValue = CDbl(txtA)
End If

If txtB.Value = "" Then
txtB.Value = 0
txtB.Value = CDbl(txtB)
End If
__________________________________________________ __
If txtA = 0 Then
vaData(1, 7) = Null
Else
vaData(1, 7) = txtA.Value
End If

If txtB = 0 Then
vaData(1, 8) = Null
Else
vaData(1, 8) = txtB.Value
End If

__________________________________________________ _

Private Sub txtA_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ReCalc
End Sub
Private Sub txtB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ReCalc
End Sub

Private Sub txtC_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ReCalc
End Sub

all cycle through a to z

any help appreciared



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

User Forms
 

See revised code below. Sorry for the delay! Let me know if you have
any questions or it doesn't work.

Mark

Nev wrote:
*Thanks Mark works fine, have three other similar situations -

For x = 1 to 26
If UserForm1.Controls("txt" & CHR(64 + x)).Value = "" Then
UserForm1.Controls("txt" & CHR(64 + x)).Value = 0
Else
txtValue = CDbl(UserForm1.Controls("txt" & CHR(64 + x)).Value)
End If
Next x
__________________________________________________ __
For x = 1 to 26
If UserForm1.Controls("txt" & CHR(64 + x)).Value = 0 Then
vaData(1, 7) = Null
Else
vaData(1, 7) = UserForm1.Controls("txt" & CHR(64 + x)).Value
End If
Next x
__________________________________________________ _

Private Sub txtA_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ReCalc
End Sub
Private Sub txtB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ReCalc
End Sub

Private Sub txtC_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ReCalc
End Sub

all cycle through a to z

any help appreciared *



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/