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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.