# Generating random numbers / Sum with a command button

#### Blanchetdb

##### Board Regular
Hi,

I need to create a code that will generate random numbers at the click of a command button "start" until the person click another command button "stop"..... the numbers will appear on the form and once stopped, it will provide the overall sum of all numbers generated

can someone help me with that

thanks

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### CSmith

##### Well-known Member
Integers? Decimals? Random # range? specific formatting options? user form or excel sheet? can you provide more specifics?

#### Blanchetdb

##### Board Regular
thank you for responding....

Integers, numbers between 1-100, not sure what you mean by formatting, in a user form

thanks

#### Yongle

##### Well-known Member
Before complicating things with a userform try this to see if it provides what you want in a simple message box Instead of trying to stop the procedure in mid-flow (which can cause other issues), the code asks the user to supply the number of random numbers that should be generated
- which does the same thing in a simpler way
- you could use a timer within the code to vary the amount of time random numbers are generated if preferred - open a NEW workbook and place the code in a module and run it from list of macros

- explain how the numbers should appear in the user form etc

VBA Code:
``````Sub RandomNumbers()
Application.ScreenUpdating = False
Dim hum As Long, num As Long, sum As Long, x As String
x = "@"
Range("A:B").ClearContents
For hum = 1 To InputBox("How many numbers", "", 70)
num = WorksheetFunction.RoundUp(Rnd * 100, 0)
x = x & ", " & num
sum = sum + num
Next hum
MsgBox "SUM" & vbTab & sum & Replace(x, "@, ", vbCr & vbCr), , ""
End Sub``````

#### Yongle

##### Well-known Member
Here is a variation that generates a random number of random numbers each time and which keeps giving new results if user clicks on OK in the message box
VBA Code:
``````Sub RandomNumbers2()
Application.ScreenUpdating = False
Dim hum As Long, num As Long, sum As Long, x As String, c As Long
x = "@"
Range("A:B").ClearContents
c = WorksheetFunction.RoundUp(Rnd * 1000, 0)
For hum = 1 To c
num = WorksheetFunction.RoundUp(Rnd * 100, 0)
x = x & ", " & num
sum = sum + num
Next hum
If MsgBox("SUM" & vbTab & sum & vbTab & vbTab & "How many ?" & vbTab & c & Replace(x, "@, ", vbCr & vbCr), vbOKCancel, "") = vbOK Then Call RandomNumbers2
End Sub``````

Last edited:

#### Yongle

##### Well-known Member
Better presented in message box VBA Code:
``````Sub RandomNumbers3()
Application.ScreenUpdating = False
Dim hum As Long, num As Long, sum As Long, x As String, c As Long
x = "@"
Range("A:B").ClearContents
c = WorksheetFunction.RoundUp(Rnd * 1000, 0)
For hum = 1 To c
num = WorksheetFunction.RoundUp(Rnd * 100, 0)
If hum Mod 7 = 0 Then x = x & vbCr
x = num & vbTab & x
sum = sum + num
Next hum
x = vbCr & vbCr & x
If MsgBox("SUM" & vbTab & sum & vbTab & vbTab & "How many ?" & vbTab & c & Replace(x, "@", vbCr & vbCr), vbOKCancel, "") = vbOK Then Call RandomNumbers3
End Sub``````

#### Yongle

##### Well-known Member
Edit I think I screwed up the cumulative string x in the above macro, but it still gives you an idea of what can be achieved

#### CSmith

##### Well-known Member
If you would like a simple sum result you can watch while it works do this:
Create userform named: frmRandSum
Create START BUTTON named: btnStart
Create STOP BUTTON named: btnStop
Create TextBox named: txtOutput
Create CheckBox (hidden/disabled) named: sFlag
View Code and paste the following below...

VBA Code:
``````Option Explicit

Public Sub rndNumbers()
Const rndLB = 1
Const rndUB = 100
Randomize
sFlag = True
txtOutput.Text = 0
Do While sFlag
txtOutput.Text = txtOutput.Text + fRnd(rndLB, rndUB)
DoEvents  ' This is SUPER IMPORTANT or you will get frozen form/Excel!
Loop
End Sub

Public Function fRnd(lBnd, uBnd As Integer) As Integer
fRnd = Int((uBnd - lBnd + 1) * Rnd + lBnd)
End Function

Private Sub btnStop_Click()
sFlag = False
End Sub

Private Sub btnStart_Click()
Call rndNumbers
End Sub``````

• Blanchetdb

#### Blanchetdb

##### Board Regular
thank you it works great.....

a few questions

is it possible for me to see the random numbers to show in a grid within the Userform?
is the number, that shows in the text box, the Sum of the random numbers?

#### CSmith

##### Well-known Member
Add another textbox (multiline) field called: txtNumbers
Alter code to this:

VBA Code:
``````Option Explicit

Public Sub rndNumbers()
Const rndLB = 1
Const rndUB = 100
Dim n As Integer, ppc As String

Randomize
ppc = "= "
sFlag = True
txtOutput.Text = 0
Do While sFlag
n = fRnd(rndLB, rndUB)
txtOutput.Text = txtOutput.Text + n
txtNumbers.Text = txtNumbers.Text & ppc & n
DoEvents  ' This is SUPER IMPORTANT or you will get frozen form/Excel!
ppc = " + "
Loop
End Sub

Public Function fRnd(lBnd, uBnd As Integer) As Integer
fRnd = Int((uBnd - lBnd + 1) * Rnd + lBnd)
End Function

Private Sub btnStop_Click()
sFlag = False
End Sub

Private Sub btnStart_Click()
Call rndNumbers
End Sub``````

• Blanchetdb