Using a Message Box to display ‘Random Quotation’ stored in cells

[robertguy]

Hi

Can any one help me with this one ?


What I’m trying to do is to click on a button which runs a Macro whic
in turn selects a random quotation i.e. “to be or not to be….” whic
is already stored in a cell.

I have about twenty quotes but I need to added to them all the time s
the list would keep on growing


Any help would be greatly appreciated


Many thanks


Ro

 

[Frank Kabel]

Hi Rob
if your quotes are in column A of the active sheet try the following
code (assign this to a button on the active sheet):
Private Sub CommandButton1_Click()
Dim wks As Worksheet
Dim row_index As Long
Dim lastrow As Long
Set wks = ActiveSheet
lastrow = wks.Cells(Rows.count, "A").End(xlUp).row

row_index = Int(lastrow * Rnd + 1)
MsgBox "Quote:" & wks.Cells(row_index, "A").Value
End Sub

 

[copycollie]

Take a look at the attached file. It might be just what you need.

HTH,

copycolli

Attachment filename: quotes.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46711

 

[robertguy]

Franks

once again many thanks !!


Ro

 

[robertguy]

Frank

with regards to your suggestion for "Using a Message Box to displa
‘Random Quotations" below


Private Sub CommandButton1_Click()
Dim wks As Worksheet
Dim row_index As Long
Dim lastrow As Long
Set wks = ActiveSheet
lastrow = wks.Cells(Rows.count, "A").End(xlUp).row

row_index = Int(lastrow * Rnd + 1)
MsgBox "Quote:" & wks.Cells(row_index, "A").Value
End Sub

Which works really well, could you please advise me what I need to d
to have the data (the quoatations) in another worksheet i.e quotation
from the command button ?


Many thanks



Ro

 

[Frank Kabel]

Hi
try
Private Sub CommandButton1_Click()
Dim wks As Worksheet
Dim row_index As Long
Dim lastrow As Long
Set wks = Activeworkbook.Worksheets("quotations") 'change this
line
lastrow = wks.Cells(Rows.count, "A").End(xlUp).row

row_index = Int(lastrow * Rnd + 1)
MsgBox "Quote:" & wks.Cells(row_index, "A").Value
End Sub

 

[robertguy]

Frank


Many thanks for the code you provide me which I have been using it eve
since, however what I have noticed is when I press my button whic
tiggers the macro code it does generate a random "quotation" a
programmed - but when I close and save the workbook and open it up th
next day and press the same button the same quotes are genrated again.

Any thoughts to rectify ?


Many thanks



Ro