I would like to create a macro to simulate a dice roll. I want it to check
the status of a check box and if the checkbox value is False then roll the
dice (random number generator from 1 through 6). If checkbox value is true
then skip rolling the dice.

Something like this :-

Code:
--------------------

Dim MyNum As Integer
If ActiveSheet.Range("A1").Value=0 Then Exit Sub
MyNum = Int(Rnd * 6) + 1
MsgBox (MyNum)

--------------------


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=388248

Then of course to expand on your gaming pleasure, I once built a 7 segment
LED (Excel based of course) display to use as a scoring mechanism in an excel
based puzzle (I used 3-7 segment digits). Similarly, with conditional
formatting you could use a block of 9 cells to represent a single die. Set
the font to 18pt webdings, which makes for decent dice dots when the cell
contains a lower case L. ( I have occasionally used these as "warning lamps"
in other projects) Set the row height and column width to 30 pixels each to
make it square. Pick a background color you can live with and set the block
that color. In the example, my block (die) occupies cells B2:D4. To make all
six possibilities, we only need to use 7 of the cells in the block.

Add the following conditional format to one of the cells in the block:

OR($C$2<1,$C$2=1,$C$2=2,$C$2=3,$C$2=4,$C$2=5,$C$2= 6,$C$2=6)

After pressing Ok, pull it back up and make sure it stuck. For some reason,
Excel likes to add quotes marks that you really don't want, although I
believe it only happens when there is string data in the formula.

Type a lower case "L" in the cell you just put the CF in.

With every possible condition, nothing lights up that way; don't worry. Then
copy that cell and paste it over the whole B2:D4 block.

I didn't use C2 (or C4), so I hid the current roll value there (C2)
formatted with matching foreground and background. If you want to get rid of
excess baggage, go back and delete the CF from C2 and C4, and delete the
contents of C4 as well. Otherwise, they are harmless if you leave them there.

Then go into each CF and remove the specific arguments from each cell to
cause it to properly react to a number stored in C2. I find it much easier to
delete what I don't need rather than to retype it over and over. Not sure why
MS won't let us copy and paste in there. Sometimes more IS better.

B2 and D4 - remove tests for 3, 4, 5 and 6
B3 and D3 - remove test for 6
B4 and D2 - remove tests for 2, 4, 5 and 6
C3 - remove tests for 1, 2, 4 and 6

Add a border around the B2:D4 block, drop the message box and instead write
the value (MyNum) to C2:

A number less than 1 or more than 6 will blank the die. Anything else will
reflect the number in C2.

You can build the die in B2:D4 and paste it where you need it, or adjust the
above to your situation and change the B2:D4 references accordingly.

Roy

Dim MyNum As Integer
If ActiveSheet.Range("A1").Value=0 Then Exit Sub
MyNum = Int(Rnd * 6) + 1
' MsgBox (MyNum) 'REMOVE THIS
range("C2") = MyNum ' AND ADD THIS

I used a command button to generate test rolls. You might want to leave the
messagebox in place, since you might not notice the automatic roll in your
situation, particularly when the same number is rolled.

The above uses reverse logic, in other words, the dots are always on and the
formatting suppresses them. The benefit is that it is much easier to change
font colors directly on the sheet as opposed to re-editing each CF cell. In
my own project, I had 3-7 segment displays that would always be black
background, however I wanted to experiment with the foreground and this made
it much easier.

Roy
"BrianB" wrote:


Something like this :-

Code:
--------------------

Dim MyNum As Integer
If ActiveSheet.Range("A1").Value=0 Then Exit Sub
MyNum = Int(Rnd * 6) + 1
MsgBox (MyNum)

--------------------


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=388248

Thanks Brian. You gave me some excellent information and even more food for
thought.

"BrianB" wrote:


Something like this :-

Code:
--------------------

Dim MyNum As Integer
If ActiveSheet.Range("A1").Value=0 Then Exit Sub
MyNum = Int(Rnd * 6) + 1
MsgBox (MyNum)

--------------------


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=388248

Roy, Thanks for all the really neat stuff. My effort pales compared to your
ideas. I haven't tried them yet but I'm going to.

"Roy" wrote:

Then of course to expand on your gaming pleasure, I once built a 7 segment
LED (Excel based of course) display to use as a scoring mechanism in an excel
based puzzle (I used 3-7 segment digits). Similarly, with conditional
formatting you could use a block of 9 cells to represent a single die. Set
the font to 18pt webdings, which makes for decent dice dots when the cell
contains a lower case L. ( I have occasionally used these as "warning lamps"
in other projects) Set the row height and column width to 30 pixels each to
make it square. Pick a background color you can live with and set the block
that color. In the example, my block (die) occupies cells B2:D4. To make all
six possibilities, we only need to use 7 of the cells in the block.

Add the following conditional format to one of the cells in the block:

OR($C$2<1,$C$2=1,$C$2=2,$C$2=3,$C$2=4,$C$2=5,$C$2= 6,$C$2=6)

After pressing Ok, pull it back up and make sure it stuck. For some reason,
Excel likes to add quotes marks that you really don't want, although I
believe it only happens when there is string data in the formula.

Type a lower case "L" in the cell you just put the CF in.

With every possible condition, nothing lights up that way; don't worry. Then
copy that cell and paste it over the whole B2:D4 block.

I didn't use C2 (or C4), so I hid the current roll value there (C2)
formatted with matching foreground and background. If you want to get rid of
excess baggage, go back and delete the CF from C2 and C4, and delete the
contents of C4 as well. Otherwise, they are harmless if you leave them there.

Then go into each CF and remove the specific arguments from each cell to
cause it to properly react to a number stored in C2. I find it much easier to
delete what I don't need rather than to retype it over and over. Not sure why
MS won't let us copy and paste in there. Sometimes more IS better.

B2 and D4 - remove tests for 3, 4, 5 and 6
B3 and D3 - remove test for 6
B4 and D2 - remove tests for 2, 4, 5 and 6
C3 - remove tests for 1, 2, 4 and 6

Add a border around the B2:D4 block, drop the message box and instead write
the value (MyNum) to C2:

A number less than 1 or more than 6 will blank the die. Anything else will
reflect the number in C2.

You can build the die in B2:D4 and paste it where you need it, or adjust the
above to your situation and change the B2:D4 references accordingly.

Roy

Dim MyNum As Integer
If ActiveSheet.Range("A1").Value=0 Then Exit Sub
MyNum = Int(Rnd * 6) + 1
' MsgBox (MyNum) 'REMOVE THIS
range("C2") = MyNum ' AND ADD THIS

I used a command button to generate test rolls. You might want to leave the
messagebox in place, since you might not notice the automatic roll in your
situation, particularly when the same number is rolled.

The above uses reverse logic, in other words, the dots are always on and the
formatting suppresses them. The benefit is that it is much easier to change
font colors directly on the sheet as opposed to re-editing each CF cell. In
my own project, I had 3-7 segment displays that would always be black
background, however I wanted to experiment with the foreground and this made
it much easier.

Roy
"BrianB" wrote:


Something like this :-

Code:
--------------------

Dim MyNum As Integer
If ActiveSheet.Range("A1").Value=0 Then Exit Sub
MyNum = Int(Rnd * 6) + 1
MsgBox (MyNum)

--------------------


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=388248

No problem Carl. I've only been hanging around here for a week or two now,
all because I was having trouble doing something I was pretty sure could be
done. I have learned so many new tricks just by surfing through everyone
else's posts that I can't as I sit here remember why I came in the first
place. I have been playing with new Excel gadgetry from the first day. I
can't imagine a better resource. It's addicting.

Roy

"Carl" wrote:

Roy, Thanks for all the really neat stuff. My effort pales compared to your
ideas. I haven't tried them yet but I'm going to.

"Roy" wrote:

Then of course to expand on your gaming pleasure, I once built a 7 segment
LED (Excel based of course) display to use as a scoring mechanism in an excel
based puzzle (I used 3-7 segment digits). Similarly, with conditional
formatting you could use a block of 9 cells to represent a single die. Set
the font to 18pt webdings, which makes for decent dice dots when the cell
contains a lower case L. ( I have occasionally used these as "warning lamps"
in other projects) Set the row height and column width to 30 pixels each to
make it square. Pick a background color you can live with and set the block
that color. In the example, my block (die) occupies cells B2:D4. To make all
six possibilities, we only need to use 7 of the cells in the block.

Add the following conditional format to one of the cells in the block:

OR($C$2<1,$C$2=1,$C$2=2,$C$2=3,$C$2=4,$C$2=5,$C$2= 6,$C$2=6)

After pressing Ok, pull it back up and make sure it stuck. For some reason,
Excel likes to add quotes marks that you really don't want, although I
believe it only happens when there is string data in the formula.

Type a lower case "L" in the cell you just put the CF in.

With every possible condition, nothing lights up that way; don't worry. Then
copy that cell and paste it over the whole B2:D4 block.

I didn't use C2 (or C4), so I hid the current roll value there (C2)
formatted with matching foreground and background. If you want to get rid of
excess baggage, go back and delete the CF from C2 and C4, and delete the
contents of C4 as well. Otherwise, they are harmless if you leave them there.

Then go into each CF and remove the specific arguments from each cell to
cause it to properly react to a number stored in C2. I find it much easier to
delete what I don't need rather than to retype it over and over. Not sure why
MS won't let us copy and paste in there. Sometimes more IS better.

B2 and D4 - remove tests for 3, 4, 5 and 6
B3 and D3 - remove test for 6
B4 and D2 - remove tests for 2, 4, 5 and 6
C3 - remove tests for 1, 2, 4 and 6

Add a border around the B2:D4 block, drop the message box and instead write
the value (MyNum) to C2:

A number less than 1 or more than 6 will blank the die. Anything else will
reflect the number in C2.

You can build the die in B2:D4 and paste it where you need it, or adjust the
above to your situation and change the B2:D4 references accordingly.

Roy

Dim MyNum As Integer
If ActiveSheet.Range("A1").Value=0 Then Exit Sub
MyNum = Int(Rnd * 6) + 1
' MsgBox (MyNum) 'REMOVE THIS
range("C2") = MyNum ' AND ADD THIS

I used a command button to generate test rolls. You might want to leave the
messagebox in place, since you might not notice the automatic roll in your
situation, particularly when the same number is rolled.

The above uses reverse logic, in other words, the dots are always on and the
formatting suppresses them. The benefit is that it is much easier to change
font colors directly on the sheet as opposed to re-editing each CF cell. In
my own project, I had 3-7 segment displays that would always be black
background, however I wanted to experiment with the foreground and this made
it much easier.

Roy
"BrianB" wrote:


Something like this :-

Code:
--------------------

Dim MyNum As Integer
If ActiveSheet.Range("A1").Value=0 Then Exit Sub
MyNum = Int(Rnd * 6) + 1
MsgBox (MyNum)

--------------------


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=388248