Label with randomly selected text from cells

C

Cajeto 63

Hello everybody,

I was given the following code yesterday by Tom Ogilvy to select a text
randomly from a list of cells in a message box.

Sub Text()

Set rng = Sheets("sheet2").Range("A1:A5")
i = Int(Rnd() * rng.Count + 1)
MsgBox rng(i)

End Sub


The question is how can I do the same for the text of a label in a userform?
Thank you for your help.
 
T

Tom Ogilvy

Private Sub Userform_Initialize()

Dim rng as Range, i as Long
Set rng = Sheets("sheet2").Range("A1:A5")
i = Int(Rnd() * rng.Count + 1)
me.Label1.Caption = rng(i).Value

End Sub


this should be in the userform module.
 
C

Cajeto 63

Tom,
I added this code in my folder but it doesn't really work.
The strange thing is that when I run the macro several times in a row with
the file open it works perfectly. But if I Close/Open Close/Open Close/Open,
each time I open it gives the same message. Any idea on how to change this?

By the way, is it possible to change the color of a msgbox?

Thank you.
 
T

Tom Ogilvy

Private Sub Userform_Initialize()

Dim rng as Range, i as Long
randomize
Set rng = Sheets("sheet2").Range("A1:A5")
i = Int(Rnd() * rng.Count + 1)
me.Label1.Caption = rng(i).Value

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top