CODEWITHSUNDEEP
vbaexcel
OliEshmade
OliEshmade

Reputation: 121

Randomising a number then displaying a corresponding cell on workbook open

Good afternoon. I have tried multiple methods now to achieve this. In essence the code works when I run it, however, it doesn't seem to do it when the Workbook opens as it should. Is there an easier way to achieve this?

Basically in rows 5-28 there are random strings, and I want cell G4 to show one of the random strings every time the workbook is opened.

I think I might be along the right lines, but am I missing anything obvious?

Many thanks

Private Sub Workbook_Open()
    wbOpenEventRun = True    
    Dim MyValue
    MyValue = Int((28 * Rnd) + 5)
    Sheets("Hints & Tips").Range("G4") = Cells(MyValue, 7)
End Sub

Upvotes: 0

Views: 46

Answers (3)

Carmelid
Carmelid

Reputation: 228

Try this:

Private Sub Workbook_Open()
    Randomize    'As Suggested by John Coleman
    wbOpenEventRun = True    
    Dim ws As Worksheet
    Set ws = Sheets("Hints & Tips")
    ws.Range("G4").Value = ws.Range("G" & Int((23 * Rnd()) + 5)).Value
End Sub

Upvotes: 1

nekomatic
nekomatic

Reputation: 6284

You were trying to assign something to the Range object, not to the cell's value. Read the Value property of the source cell and write that to the Value property of the destination:

Sheets("Hints & Tips").Range("G4").Value = Sheets("SourceSheet").Cells(MyValue, 7).Value

It's best to also specify which sheet is the source of the data, or it will depend on which sheet is active at the time the macro runs - unless that's the behaviour you want.

Upvotes: 1

John Coleman
John Coleman

Reputation: 51998

You could use Application.WorksheetFunction.RandBetween():

Private Sub Workbook_Open()
    wbOpenEventRun = True    
    Dim MyValue
    MyValue = Application.WorksheetFunction.RandBetween(5,28)
    Sheets("Hints & Tips").Range("G4") = Cells(MyValue, 7)
End Sub

Upvotes: 1

Related Questions