I need assistance on Rand() and Combo box in '97

[Adam Kroger]

To begin with I apologize for the cross posting, but I am new to these
groups, and an not sure of the protocols. I am attempting to do several
things with a spreadsheet. For background info, the purpose of the
spreadsheet is to assist in the running of a PBeM version of a wargame
called Battletech. Here are the two things I need assistance for:

Drop-down Listbox within the spreadsheet.
There are 3 different arrays of data that I would like to be able to
access as a dropdown list to fill a cell. I have figured out how to do
this, on an individual basis. My question is, can the same combo box be use
multiple times, without hving to manually edit its details for each cell I
want to be filed by the same information choices (S,M, or L)? I wish to use
the same information choices 84 on different cells, across 8 worksheets,
within the same workbook, with another 178 cells having a Y(es)/N(o) option
set, and 120 cells having the choice between (W?R/J/M). Nedless to say,
manually editing almost 400 listboxes is not somehting I am going to do for
a hobby. Is there a way to copy a combo box around the worksheet and have
its cell referevce change to match the new location?

Random number generation
I was able to get a cell to generate a random number between 2 and 12
simulating the results of 2 six sided dice.using the function
ROUNDUP(RAND()*6+1)+ROUNDUP(RAND()*6+1) . Unfortunatly it creates a new
random number each time the worksheet recalculates. Ideally I would like to
be able to select a cell (or range of cells), activate a button, and have
the cells be filled with the randomly generated numbers, that are specified
in another cell's location. The filled cells would then be non-volitile. I
assume that it would be possible using VBA, but what I know about VBA would
fill a thimble, with room left over. I hope that makes sense. I am trying
to simulate dice rolls. so being able to specify # of dice, and sides of
dice being rolled is needed (probability distribution needs to be
maintained).

ANY assistance would be greatly appreciated as I am at a dead end on both of
these issues. If anyolne who can help would be willing to do so via
straight email it would be best as these groups are extremely busy and
monitoring them for responses will be hard, but done. If you need
additional information, I can always email a copy of the spreadsheet(s), or
a version is available on kazaa under the filename
BattleTech - GM - Combat Tracker v1.0.xls

Thank You

 

[Bernie Deitrick]

Adam,

For the combo-box, try using Data Validation. You can allow any of a number
of values from a list, set the input message, the error message, etc.

For the Rand() problem, you could use two named cells "Sides" and "Dies",
where you enter the number of sides of each die, and the number of dies.
Then use a macro like this, assigned to a button, simulate the toss, which
will fill the selected cells with the values calculated:

Sub DiceToss()
Dim myCell As Range
Dim Sides As Integer
Dim Dies As Integer
Dim i As Integer
Dim myTemp As Integer

Sides = Range("Sides").Value
Dies = Range("Dies").Value
For Each myCell In Selection
Randomize
myTemp = 0
For i = 1 To Dies
myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
Next i
myCell.Value = myTemp
Next myCell
End Sub

Note that you don't want to add 1 to the result of the rand *6, since the
roundup means that it could actually be seven.

HTH,
Bernie
MS Excel MVP

 

[Adam Kroger]

Thank you, Thank you, Thank you

The VBA routine work great. Is it possible to attach it to a toolbar
button? I have been rethinking my strategy, and it seems like it would be
most efficient to have the execution button on a toolbar instead of attached
to a location inside the sheet.

I will have to explore the Data Validation, I am not familiar with it's
features, but it sounds like it might fill the bill for me.

Adam

 

[Tom Ogilvy]

Select 178 cells and do

Data=>Validation
Select the list option

type in

Yes,No

similar for you other choices.

to make your roll non volatile, you would have to go to
Tools=>Options=>Calculation, select Iteration and make max iterations equal
to 1

then assume your formula is in A2

change your formula to
=IF(A1=1,A2,ROUNDUP(RAND()*6+1,0)+ROUNDUP(RAND()*6+1,0))
if you have a 1 in A1, the cell will not recalculate, If you clear A1 or
put other than a 1 there then the cell will calculate. You can then
immediately put in a 1. this will be how you roll the die.

This is called an intentional circular reference. If you unselect iteration
in the Calculation tab, you will get a circular reference error.

 

[Adam Kroger]

I figured out how to attach it to a button and it is working great, I am
having unexpected difficulty with the Data Validation. I have the list of
acceptable entries on a worksheet called "DATA". The validation dialog will
not let me change to the sheet to select it. The list is a named range.
Any suggestions?

 

[Dave Peterson]

Just type in that name in the data|validation dialog.

=YourNameHere



"Adam Kroger