I added the code below given by Tom 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.




"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

If the data in Range("A1:A5") does not change, you will get the same result
every time.

You can change the color of the MsgBox in Control PanelAppearance and
ThemesDisplayappearanceAdvanced. When you change the color of the message
box, you also change the color of your window frames, i.e. Title bar and
Status bar.

"Cajeto 63" wrote:

I added the code below given by Tom 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.




"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

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
...
I added the code below given by Tom 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.




"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

Tom,
thank you for the randomize tip, it works very well now.

About the msgbox color, do you know if there is a way to have it change thru
VBA code and then set it back to normal after the msgbox has been clicked OK?

Or is JLGWhiz solution the only solution?

Thanks for your help.

"Tom Ogilvy" wrote:

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
...
I added the code below given by Tom 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.




"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

I don't know of an easy way. Use a Userform instead and you can do it.

--
Regards,
Tom Ogilvy




"Cajeto 63" wrote:

Tom,
thank you for the randomize tip, it works very well now.

About the msgbox color, do you know if there is a way to have it change thru
VBA code and then set it back to normal after the msgbox has been clicked OK?

Or is JLGWhiz solution the only solution?

Thanks for your help.

"Tom Ogilvy" wrote:

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
...
I added the code below given by Tom 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.




"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