Task of assigning random numbers to the squares, and selecting the winning square
Task of assigning random numbers to the squares, and selecting the winning square. Whenever the user clicks the “Go” button, your VBA program should assign new random numbers to the cells in the worksheet and also randomly select the winning cell and change its background color to red (or some other color). No formulas should be placed in any cells on the worksheet. Requirements & Hints: 1) You may use formulas to create the row and column headings in the table. However, there should not be any other formulas in the worksheet. All the random numbers should be generated in VBA and placed in the cells as numeric constants (not cell formulas). 2) In VBA, numbers between 1 and 500 can be randomly generated via: 1 + Int(Rnd * 500)) 3) You will need to figure out how to make VBA loop through the 500 cells in the range containing the numbers. While the lectures in Unit 5 describe the Cells( ) collection of a range object as requiring two arguments to specify a row and column (like Cells(r, c)), you can also use a single argument (like Cells(n)) to refer to the nth cell in a range. 4) There are a variety of ways to approach this problem. One way is to first fill the range sequentially with the integers from 1 to 500. Then, you could go to each cell (one at a time), generate a random cell number between 1 and 500 and then swap the value in the current cell with the value that is in whatever cell number you generate randomly. (See the next page for a detailed walk-through of this procedure using a simplified example.). 5) Make sure the macro you create is Public (not Private). Putting the “Go” button in your application Once you have your VBA code working, you need to add a “Go” button to your spreadsheet that the user can click to execute your code. I strongly recommend that you get your code working first and then follow the instructions below to create a Go button and connect your code to it. To create the “Go” button first make sure you have the Developer tab available on the Ribbon in Excel. If you don’t have a Developer tab do one of the following: In Office 2007 click the Office button, Excel Options, Popular, and click the check box for “Show Developer tab in the Ribbon.” In Office 2010 & 2013 click File, Options, Customize Ribbon, and check the box next to Developer in the right-hand pane. Now, click Developer, Insert, and click the “Button” icon beneath Form Controls, and then click and drag to draw a button control on your spreadsheet. (Note that there is another button icon under ActiveX Controls, but I strongly recommend using the one under Form Controls for this exercise.) A window should appear allowing you to assign a (Public) macro to this button. Select your macro and click OK. If you click your button, it should now run the macro you assigned to it. If you right-click your button you can edit the text that appears on the button (among other things). Simplified Example Suppose I have a range filled with numbers from 1 to 15… 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
"96% of our customers have reported a 90% and above score. You might want to place an order with us."
