Please HELP Save my Marriage!!!

Posted by Judy on December 14, 2001 7:52 PM

My husband & I are constantly bickering over what to eat. Neither one of us can make up our minds. Can someone PLEASE help us with a macro that will popup a message box that has a "FAST FOOD" button and a "RESTAURANT" button. Once we click one of those two, I need the macro to go through out list of places to eat and randomly select on for us.
Can this be done? I WOULD REALLY, REALLY APPRECIATE ANY HELP WITH THIS. It just might save our marriage!!!!!!!!!!!!!

THANKS SO MUCH!!!

Posted by Jacob on December 14, 2001 8:58 PM

Try this

In Column A type Rand()
Fill down for several rows for each place you want to eat
In column B put the Places to eat

Then do this macro

Dim EndRow
EndRow = Range("B65536").End(xlUp).Address
Range("A1:" & EndRow).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
MsgBox (Range("B1").Value)
End Sub

Hope this helps

Jacob

Posted by Judy on December 14, 2001 9:08 PM

Thanks!! When I run the macro it always returns the restaurant in cell B1.

Thanks!! When I run the macro it always returns the restaurant in cell B1. Did I do something wrong? How can it randomly pick a restaurant?

Posted by Tom Dickinson on December 14, 2001 10:05 PM

Judy:
Try this:

Sub Eatery(Colm)
Randomize Timer
Range("A1") = Range(Colm & Int((Range(Colm &
200).End(xlUp).Row - 2) * Rnd) + 2)
End Sub
Sub Restaurant()
Call Eatery("A")
End Sub
Sub FastFood()
Call Eatery("B")
End Sub

Additional notes: The 2 lines that start Range("A1")... are to be on the same line of code. Put the restaurants in column A starting on row 2. Put the Fast Food places in column B starting on row 2. The formula allows you to list about 200 of each. If you need more, increase the number 200 in the code to whatever you need. Create 2 macro buttons on the sheet, lable 1 as Restaurant and assign the Restaurant macro to it. The other button is for fast food. Put the buttons to the right of cell A1 as that is where the selection will be shown.

Hope this helps. Call me for the 25th anniversary.
Tom

Posted by Bariloche on December 14, 2001 10:51 PM

Judy,

While not exactly meeting your specifications, the following will be a little simpler to implement (no Userform required):

Sub WhereToEat()
Dim LastRow As Long
Dim RestaurantRow As Long
Dim EatingPlace As String

MsgBox "Would you like to find out where to have dinner?", vbOKOnly, "Marriage Saving Restuarant Selector"

LastRow = Cells(65536, 1).End(xlUp).Row

Randomize

RestaurantRow = Int((LastRow - 1 + 1) * Rnd + 1)

EatingPlace = Cells(RestaurantRow, 1).Value

MsgBox "I've decided you should eat at " & Chr(13) & Chr(9) & EatingPlace

End Sub

All that's required is that you list your choices in column A (starting at A1). You can then put a button on the buttonbar to call the macro, or put the guts of it in the workbook open event and opening the "Restaurant" workbook will start the macro. Whichever you like.

The simplification is that you don't get to chose the type of restaurant. Of course if your listing is heavily weight toward one type, then that type would be expected to come up more often.

enjoy

Posted by Jacob on December 15, 2001 1:56 PM

Re: Thanks!! When I run the macro it always returns the restaurant in cell B1.

It is Random, everytime there will be a different restaurant in B1.

Jacob

Posted by Stephen Giles on December 17, 2001 8:03 AM

While not exactly meeting your specifications, the following will be a little simpler to implement (no Userform required):

No contest here - just find the nearest Argentine restaurant and wallow in Argentine beef and wine!!

Posted by Bariloche on December 17, 2001 5:25 PM

At least someone is paying attention 'round here! LOL

Stephen,

Not many folks would pick up on that. I haven't been yet, but it is my intention to one day spend some time, right around this time of the year, casting a dry fly by day and partaking of the local fare by night.

take care

No contest here - just find the nearest Argentine restaurant and wallow in Argentine beef and wine!!