What would be the best way to do this?

[dpaton05]

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]

Hi dpaton05,

Let me know how this goes:

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]

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]

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?

 

[dpaton05]

Thanks Robert

 

[Trebor76]

You're welcome. I'm glad we were able to provide you with a solution