What would be the best way to do this?

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,025
I am making a spreadsheet that allows you to enter an activity, press a button, then it stores the activity. A list of activities are compiled over time and there is another button that selects a random activity from the list. You can't use variables for this as they only last until the spreadsheet shuts.

What would be the best way to do this, maybe a list box?
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,435
Hi dpaton05,

Let me know how this goes:

Code:
Option Explicit
Sub Macro2()

    Dim ws As Worksheet
    Dim strSourceCol As String
    Dim lngLowerBound As Long
    Dim lngUpperBound As Long
    Dim lngRandRow As Long
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Sheet name containing the activity list. Change to suit if necessary.
    strSourceCol = "A" 'Column letter contianing the activity list. Change to suit if necessary.
    lngLowerBound = 2 'Starting row number for the activity list. Change to suit if necessary.
    lngUpperBound = ws.Cells(Rows.Count, strSourceCol).End(xlUp).Row
    
    'The following was adpted from here: _
    https://www.techonthenet.com/excel/formulas/rnd.php
    lngRandRow = CLng((lngUpperBound - lngLowerBound + 1) * Rnd + lngLowerBound)
    
    Application.ScreenUpdating = True
    
    MsgBox ws.Range(strSourceCol & lngRandRow) 'Display the result
   
End Sub
Regards,

Robert
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,025
Thanks for the reply Robert, I wanted to make it into an app so instead of randomly selecting an activity from a range, I was thinking of a stand alone way to store the list of activities. What do you think would be the best way to do this?

Dave
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,435
I am making a spreadsheet that allows you to enter an activity, press a button, then it stores the activity.
So my code randomly selects a row from the list (once you change the variables to suit) and displays it in a message box. Isn't that what you were after?
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,435
You're welcome. I'm glad we were able to provide you with a solution ;)
 

Forum statistics

Threads
1,077,827
Messages
5,336,612
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top