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

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

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 @hotmail.com>" <adam_kroger<nospam> wrote in message
news:[email protected]...
> 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
>

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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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 @hotmail.com>" <adam_kroger<nospam> wrote in message
> news:[email protected]...
>> 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
>>
>
>

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.

--
Regards,
Tom Ogilvy




"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:[email protected]...
> 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
>
>

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?


"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:[email protected]...
> 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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> 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 @hotmail.com>" <adam_kroger<nospam> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>
>

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

=YourNameHere



"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?
>
> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
> news:[email protected]...
> > 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
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:[email protected]...
> >> 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 @hotmail.com>" <adam_kroger<nospam> wrote in message
> >> news:[email protected]...
> >>> 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
> >>>
> >>
> >>
> >
> >

--

Dave Peterson