Generating random numbers / Sum with a command button

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Integers? Decimals? Random # range? specific formatting options? user form or excel sheet? can you provide more specifics?
 
Upvote 0
thank you for responding....

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

thanks
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top