Label with randomly selected text from cells
 

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.

Label with randomly selected text from cells
 

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.

--
Regards,
Tom Ogilvy


"Cajeto 63" wrote in message
...
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.

Label with randomly selected text from cells
 

Thank you very much Tom,
Once again it works perfectly.

Thanks for your help.


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Cajeto 63" wrote in message
...
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.

Label with randomly selected text from cells
 

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.


"Cajeto 63" wrote:

Thank you very much Tom,
Once again it works perfectly.

Thanks for your help.


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Cajeto 63" wrote in message
...
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.

Label with randomly selected text from cells
 

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


--
Regards,
Tom Ogilvy



"Cajeto 63" wrote in message
...
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.


"Cajeto 63" wrote:

Thank you very much Tom,
Once again it works perfectly.

Thanks for your help.


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Cajeto 63" wrote in message
...
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.