PDA

View Full Version : Solved: For loop and textboxes



danbrix
09-30-2010, 12:45 PM
Hi guys,

I am new to this forum and new to macro vba as well.

I am trying to generate random numbers and insert them into a userform textbox. I can do that individually, like this

randomNum = int(10*Rnd)
TextBox1.Text = randomNum

The problem is I have got like 40 textboxes and I want to use a for loop but I just couldn't get the hung of it. Any help is very much appreciated. Thank you

JKwan
09-30-2010, 01:02 PM
Try this out:

Private Sub UserForm_Initialize()
Dim ctl As Control
Dim RandomNum As Double

RandomNum = Int(10 * Rnd)
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Value = RandomNum
End Select
Next

End Sub

danbrix
09-30-2010, 01:08 PM
By the way this is what I tried

Public NumValue, randomNum As Integer
Public RandText As String
For NumValue = 1 To 40 Step 1
RandText = ""
randomNum = Int(10*Rnd)
RandText = RandText & "num" & NumValue & "." & "Text"
'num(NumValue).Text = randomNum
RandText = randomNum
Next NumValue

danbrix
09-30-2010, 01:26 PM
Thank you JKwan for the quick reply.

Just tried the code and run into a problem again.

1) It gives out the same random number on all textboxes. But I would have liked different random number on all textboxes. I am sorry I should have made it clear in my OP.
2) I have other text boxes in the form that I don't want random numbers to be generated into.

Thank you again

Bob Phillips
09-30-2010, 01:29 PM
You have to be careful about creating duplicates using Rand. What values can they span, just 1-40 or more?

Bob Phillips
09-30-2010, 01:30 PM
Seeing your latest post, how would the target textboxes be identified?

danbrix
09-30-2010, 01:34 PM
Seeing your latest post, how would the target textboxes be identified?That is exactly my problem.

JKwan
09-30-2010, 01:38 PM
None of those restrictions were mentioned....
try this

Private Sub UserForm_Initialize()
Dim ctl As Control
Dim randomNum As Long

For Each ctl In Me.Controls
randomNum = Int(10 * Rnd)
Select Case TypeName(ctl)
Case "TextBox"
If ctl.Tag = "yes" Then
ctl.Value = randomNum
End If
End Select
Next
End Sub


Before you run it. Select all the Textboxes that you want random number to be assigned (control click), now, find the .Tag property and type in "yes", this is the indentifier. Now, run the form.

Bob Phillips
09-30-2010, 01:44 PM
Seeing your latest post, how would the target textboxes be identified?

Well we have no idea, only you can say.

Bob Phillips
09-30-2010, 01:45 PM
This code will keep them unique



Public Sub RandText()
Dim vecRands(1 To 40) As Long
Dim NumValue As Long
Dim randNum As Long
Dim i As Long

Randomize
Do

randNum = Int(Rnd() * 40 + 1)
If IsError(Application.Match(randNum, vecRands, 0)) Then

NumValue = NumValue + 1
vecRands(NumValue) = randNum
End If
Loop Until NumValue >= 40

For i = 1 To 40

Me.Controls("TexBox" & i).Text = "num " & vecRands(i) & " text"
Next i
End Sub

danbrix
09-30-2010, 01:46 PM
This is amazing ... it works like a wonder.

Thank you very much JKwan.

danbrix
09-30-2010, 02:04 PM
Thank you very much xld ... your code works fine as well except for the last bit which I changed as
'Me.Controls("TextBox" & i).Text = "num " & vecRands(i) & " text"
Me.Controls("TextBox" & i).Text = Int(Rnd * 100)
Thank you very much guys

Bob Phillips
09-30-2010, 02:33 PM
No! You are ignoring the uniquely generated list that way, and creating duplicates.

danbrix
09-30-2010, 03:29 PM
No! You are ignoring the uniquely generated list that way, and creating duplicates. xld, I am sorry if I am taking up your time. This really is all new to me and still learning in the process but when I run your code it generates strings ... Am I missing anything here?