Generating random numbers / Sum with a command button

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
65
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
 

Some videos you may like

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
Joined
Jan 13, 2020
Messages
686
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Integers? Decimals? Random # range? specific formatting options? user form or excel sheet? can you provide more specifics?
 

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
65
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
Joined
Mar 11, 2015
Messages
6,007
Office Version
365
Platform
Windows
Before complicating things with a userform try this to see if it provides what you want in a simple message box
ResultMsg.jpg


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
HowMany.jpg


- open a NEW workbook and place the code in a module and run it from list of macros

In order that we can help you further
- 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
Joined
Mar 11, 2015
Messages
6,007
Office Version
365
Platform
Windows
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
Joined
Mar 11, 2015
Messages
6,007
Office Version
365
Platform
Windows
Better presented in message box

Nice.jpg

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
Joined
Mar 11, 2015
Messages
6,007
Office Version
365
Platform
Windows
Edit :mad:
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
Joined
Jan 13, 2020
Messages
686
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
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

Board Regular
Joined
Jul 31, 2018
Messages
65
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?

again....thank you for your help
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,006
Messages
5,465,968
Members
406,457
Latest member
Pinky Rose Jordan

This Week's Hot Topics

Top