I created a macro to preform this function below. Once
that function is preformed the user has the option of
keeping all or some of the returned data.

How do I make the macro only look at the valid unkeept
data to change. I put in a "check box form" to let the
user select the data to keep, but how do I tell the macro
to ignor that portion of the spread sheet?

Sub ShipCaptCrew()
Randomize
Dim NextRow As Long
Range("Die1") = Int(Rnd() * 6) + 1
Range("Die2") = Int(Rnd() * 6) + 1
Range("Die3") = Int(Rnd() * 6) + 1
Range("Die4") = Int(Rnd() * 6) + 1
Range("Die5") = Int(Rnd() * 6) + 1

Any help will be great.
Thanks
Pete W.

Pete

Do you have checkboxes next to the ranges? Are those checkboxes linked to
cells?

Let's say you have five checkboxes next to Die1 - Die5 and those checkboxes
are linked to the cells just to the right of Die1-Die5. Now your macro
could look like this

Sub ShipCaptCrew()
Dim i as Long
For i = 1 to 5
With Range("Die " & i)
If Not .Offset(0,1).Value Then
Randomize
.Value = Int(Rnd() * 6) + 1
End If
End With
Next i
End Sub

This will look at the cell to the right of Die x (.Offset(1,0)) and if it's
Not TRUE (Check box wasn't checked), then it will replace it's value with a
new random number.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pete" wrote in message
...
I created a macro to preform this function below. Once
that function is preformed the user has the option of
keeping all or some of the returned data.

How do I make the macro only look at the valid unkeept
data to change. I put in a "check box form" to let the
user select the data to keep, but how do I tell the macro
to ignor that portion of the spread sheet?

Sub ShipCaptCrew()
Randomize
Dim NextRow As Long
Range("Die1") = Int(Rnd() * 6) + 1
Range("Die2") = Int(Rnd() * 6) + 1
Range("Die3") = Int(Rnd() * 6) + 1
Range("Die4") = Int(Rnd() * 6) + 1
Range("Die5") = Int(Rnd() * 6) + 1

Any help will be great.
Thanks
Pete W.

Dick

Thanks for you insight to this question of mine. However I
did what you said and entered this code you posted, only
to incurr a run time error 1004 ~Method Range of
object_Glodal Failed~ @ With Range("Die " & i). Any
thoughts?

If I take out the space between With Range("Die " & i) to
look like this ("~With Range("Die" & i)~"), then the macro
will work but the checkboxes won't preform the hold
function.

Thanks for your help.
Pete

-----Original Message-----
Pete

Do you have checkboxes next to the ranges? Are those
checkboxes linked to
cells?

Let's say you have five checkboxes next to Die1 - Die5
and those checkboxes
are linked to the cells just to the right of Die1-Die5.
Now your macro
could look like this

Sub ShipCaptCrew()
Dim i as Long
For i = 1 to 5
With Range("Die " & i)
If Not .Offset(0,1).Value Then
Randomize
.Value = Int(Rnd() * 6) + 1
End If
End With
Next i
End Sub

This will look at the cell to the right of Die x (.Offset
(1,0)) and if it's
Not TRUE (Check box wasn't checked), then it will replace
it's value with a
new random number.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pete" wrote in
message
...
I created a macro to preform this function below. Once
that function is preformed the user has the option of
keeping all or some of the returned data.

How do I make the macro only look at the valid unkeept
data to change. I put in a "check box form" to let the
user select the data to keep, but how do I tell the
macro
to ignor that portion of the spread sheet?

Sub ShipCaptCrew()
Randomize
Dim NextRow As Long
Range("Die1") = Int(Rnd() * 6) + 1
Range("Die2") = Int(Rnd() * 6) + 1
Range("Die3") = Int(Rnd() * 6) + 1
Range("Die4") = Int(Rnd() * 6) + 1
Range("Die5") = Int(Rnd() * 6) + 1

Any help will be great.
Thanks
Pete W.


.

Pete

It worked for me. We must have something set up differently. Here's my set
up:

I have cells A1-A5 named Die1-Die5. I have 5 checkboxes linked to B1, B2,
B3, B4 and B5, respectively. I'm using checkboxes from the Forms toolbar
and to link them, I right-clicked and chose Format Control and put the range
reference in the Cell Link box. If you are using checkboxes from the
Control Toolbox, you would need to click Properties with the control
selected and fill in LinkedCell property.

When I run it, it puts a random number between 1 and 6 in each of A1:A5. If
I check the box that corresponds with a number and re-run the macro, that
number doesn't change.

I can send you the workbook on which I tested this, or if you like, you can
send me your workbook if you can't figure out what the difference is.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

wrote in message
...
Dick

Thanks for you insight to this question of mine. However I
did what you said and entered this code you posted, only
to incurr a run time error 1004 ~Method Range of
object_Glodal Failed~ @ With Range("Die " & i). Any
thoughts?

If I take out the space between With Range("Die " & i) to
look like this ("~With Range("Die" & i)~"), then the macro
will work but the checkboxes won't preform the hold
function.

Thanks for your help.
Pete

-----Original Message-----
Pete

Do you have checkboxes next to the ranges? Are those
checkboxes linked to
cells?

Let's say you have five checkboxes next to Die1 - Die5
and those checkboxes
are linked to the cells just to the right of Die1-Die5.
Now your macro
could look like this

Sub ShipCaptCrew()
Dim i as Long
For i = 1 to 5
With Range("Die " & i)
If Not .Offset(0,1).Value Then
Randomize
.Value = Int(Rnd() * 6) + 1
End If
End With
Next i
End Sub

This will look at the cell to the right of Die x (.Offset
(1,0)) and if it's
Not TRUE (Check box wasn't checked), then it will replace
it's value with a
new random number.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pete" wrote in
message
...
I created a macro to preform this function below. Once
that function is preformed the user has the option of
keeping all or some of the returned data.

How do I make the macro only look at the valid unkeept
data to change. I put in a "check box form" to let the
user select the data to keep, but how do I tell the
macro
to ignor that portion of the spread sheet?

Sub ShipCaptCrew()
Randomize
Dim NextRow As Long
Range("Die1") = Int(Rnd() * 6) + 1
Range("Die2") = Int(Rnd() * 6) + 1
Range("Die3") = Int(Rnd() * 6) + 1
Range("Die4") = Int(Rnd() * 6) + 1
Range("Die5") = Int(Rnd() * 6) + 1

Any help will be great.
Thanks
Pete W.


.

Dick

I sent you a E-mail directly to your computer @
. If that is not your e-
mail address please e-mail me @ .

Thanks
Pete W

-----Original Message-----
Pete

It worked for me. We must have something set up
differently. Here's my set
up:

I have cells A1-A5 named Die1-Die5. I have 5 checkboxes
linked to B1, B2,
B3, B4 and B5, respectively. I'm using checkboxes from
the Forms toolbar
and to link them, I right-clicked and chose Format
Control and put the range
reference in the Cell Link box. If you are using
checkboxes from the
Control Toolbox, you would need to click Properties with
the control
selected and fill in LinkedCell property.

When I run it, it puts a random number between 1 and 6 in
each of A1:A5. If
I check the box that corresponds with a number and re-run
the macro, that
number doesn't change.

I can send you the workbook on which I tested this, or if
you like, you can
send me your workbook if you can't figure out what the
difference is.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

wrote in message
...
Dick

Thanks for you insight to this question of mine.
However I
did what you said and entered this code you posted, only
to incurr a run time error 1004 ~Method Range of
object_Glodal Failed~ @ With Range("Die " & i). Any
thoughts?

If I take out the space between With Range("Die " & i)
to
look like this ("~With Range("Die" & i)~"), then the
macro
will work but the checkboxes won't preform the hold
function.

Thanks for your help.
Pete

-----Original Message-----
Pete

Do you have checkboxes next to the ranges? Are those
checkboxes linked to
cells?

Let's say you have five checkboxes next to Die1 - Die5
and those checkboxes
are linked to the cells just to the right of Die1-Die5.
Now your macro
could look like this

Sub ShipCaptCrew()
Dim i as Long
For i = 1 to 5
With Range("Die " & i)
If Not .Offset(0,1).Value Then
Randomize
.Value = Int(Rnd() * 6) + 1
End If
End With
Next i
End Sub

This will look at the cell to the right of Die x
(.Offset
(1,0)) and if it's
Not TRUE (Check box wasn't checked), then it will
replace
it's value with a
new random number.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pete" wrote in
message
...
I created a macro to preform this function below.
Once
that function is preformed the user has the option of
keeping all or some of the returned data.

How do I make the macro only look at the valid
unkeept
data to change. I put in a "check box form" to let
the
user select the data to keep, but how do I tell the
macro
to ignor that portion of the spread sheet?

Sub ShipCaptCrew()
Randomize
Dim NextRow As Long
Range("Die1") = Int(Rnd() * 6) + 1
Range("Die2") = Int(Rnd() * 6) + 1
Range("Die3") = Int(Rnd() * 6) + 1
Range("Die4") = Int(Rnd() * 6) + 1
Range("Die5") = Int(Rnd() * 6) + 1

Any help will be great.
Thanks
Pete W.


.



.

Dick

I sent you a E-mail directly to your computer @
. If that is not your e-
mail address please e-mail me @ .

Thanks
Pete W

-----Original Message-----
Pete

It worked for me. We must have something set up
differently. Here's my set
up:

I have cells A1-A5 named Die1-Die5. I have 5 checkboxes
linked to B1, B2,
B3, B4 and B5, respectively. I'm using checkboxes from
the Forms toolbar
and to link them, I right-clicked and chose Format
Control and put the range
reference in the Cell Link box. If you are using
checkboxes from the
Control Toolbox, you would need to click Properties with
the control
selected and fill in LinkedCell property.

When I run it, it puts a random number between 1 and 6 in
each of A1:A5. If
I check the box that corresponds with a number and re-run
the macro, that
number doesn't change.

I can send you the workbook on which I tested this, or if
you like, you can
send me your workbook if you can't figure out what the
difference is.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

wrote in message
...
Dick

Thanks for you insight to this question of mine.
However I
did what you said and entered this code you posted, only
to incurr a run time error 1004 ~Method Range of
object_Glodal Failed~ @ With Range("Die " & i). Any
thoughts?

If I take out the space between With Range("Die " & i)
to
look like this ("~With Range("Die" & i)~"), then the
macro
will work but the checkboxes won't preform the hold
function.

Thanks for your help.
Pete

-----Original Message-----
Pete

Do you have checkboxes next to the ranges? Are those
checkboxes linked to
cells?

Let's say you have five checkboxes next to Die1 - Die5
and those checkboxes
are linked to the cells just to the right of Die1-Die5.
Now your macro
could look like this

Sub ShipCaptCrew()
Dim i as Long
For i = 1 to 5
With Range("Die " & i)
If Not .Offset(0,1).Value Then
Randomize
.Value = Int(Rnd() * 6) + 1
End If
End With
Next i
End Sub

This will look at the cell to the right of Die x
(.Offset
(1,0)) and if it's
Not TRUE (Check box wasn't checked), then it will
replace
it's value with a
new random number.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pete" wrote in
message
...
I created a macro to preform this function below.
Once
that function is preformed the user has the option of
keeping all or some of the returned data.

How do I make the macro only look at the valid
unkeept
data to change. I put in a "check box form" to let
the
user select the data to keep, but how do I tell the
macro
to ignor that portion of the spread sheet?

Sub ShipCaptCrew()
Randomize
Dim NextRow As Long
Range("Die1") = Int(Rnd() * 6) + 1
Range("Die2") = Int(Rnd() * 6) + 1
Range("Die3") = Int(Rnd() * 6) + 1
Range("Die4") = Int(Rnd() * 6) + 1
Range("Die5") = Int(Rnd() * 6) + 1

Any help will be great.
Thanks
Pete W.


.



.

Pete

I got it and here was my reply (also emailed):

--------------------------------------------
If I remove the space in Range("Die " & i) to

Range("Die" & i)

then it works fine for me.

The run time error was because there was no Range by that name and removing
the space fixes it.

In your post you said that you removed the space which got rid of the error,
but that the checkboxes didn't hold the values. When I check the box, the
values hold for me. Are you still experiencing that problem? If so, what
are you experiencing? Are the dice changing even when the checkbox is
checked?

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

BTW, that's a nice looking spreadsheet.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pete" wrote in message
...
Dick

I sent you a E-mail directly to your computer @
. If that is not your e-
mail address please e-mail me @ .

Thanks
Pete W

-----Original Message-----
Pete

It worked for me. We must have something set up
differently. Here's my set
up:

I have cells A1-A5 named Die1-Die5. I have 5 checkboxes
linked to B1, B2,
B3, B4 and B5, respectively. I'm using checkboxes from
the Forms toolbar
and to link them, I right-clicked and chose Format
Control and put the range
reference in the Cell Link box. If you are using
checkboxes from the
Control Toolbox, you would need to click Properties with
the control
selected and fill in LinkedCell property.

When I run it, it puts a random number between 1 and 6 in
each of A1:A5. If
I check the box that corresponds with a number and re-run
the macro, that
number doesn't change.

I can send you the workbook on which I tested this, or if
you like, you can
send me your workbook if you can't figure out what the
difference is.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

wrote in message
...
Dick

Thanks for you insight to this question of mine.
However I
did what you said and entered this code you posted, only
to incurr a run time error 1004 ~Method Range of
object_Glodal Failed~ @ With Range("Die " & i). Any
thoughts?

If I take out the space between With Range("Die " & i)
to
look like this ("~With Range("Die" & i)~"), then the
macro
will work but the checkboxes won't preform the hold
function.

Thanks for your help.
Pete

-----Original Message-----
Pete

Do you have checkboxes next to the ranges? Are those
checkboxes linked to
cells?

Let's say you have five checkboxes next to Die1 - Die5
and those checkboxes
are linked to the cells just to the right of Die1-Die5.
Now your macro
could look like this

Sub ShipCaptCrew()
Dim i as Long
For i = 1 to 5
With Range("Die " & i)
If Not .Offset(0,1).Value Then
Randomize
.Value = Int(Rnd() * 6) + 1
End If
End With
Next i
End Sub

This will look at the cell to the right of Die x
(.Offset
(1,0)) and if it's
Not TRUE (Check box wasn't checked), then it will
replace
it's value with a
new random number.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pete" wrote in
message
...
I created a macro to preform this function below.
Once
that function is preformed the user has the option of
keeping all or some of the returned data.

How do I make the macro only look at the valid
unkeept
data to change. I put in a "check box form" to let
the
user select the data to keep, but how do I tell the
macro
to ignor that portion of the spread sheet?

Sub ShipCaptCrew()
Randomize
Dim NextRow As Long
Range("Die1") = Int(Rnd() * 6) + 1
Range("Die2") = Int(Rnd() * 6) + 1
Range("Die3") = Int(Rnd() * 6) + 1
Range("Die4") = Int(Rnd() * 6) + 1
Range("Die5") = Int(Rnd() * 6) + 1

Any help will be great.
Thanks
Pete W.


.



.