variable help....

[ltyson]

I have two sub routines, the first one does exactly what I want. If the checkbox on the user form is checked, then the corresponding rectangle is filled in on the worksheet. If I uncheck the check box, then the corrsponding rectangle is unfilled for lack of a better term.

Sub CheckBox368_Click()
i = 368
If CheckBox368 = True Then
Sheets("Order_Entry").Shapes("Rectangle " & i).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
End If
If CheckBox368 = False Then
Sheets("Order_Entry").Shapes("Rectangle " & i).Select
Selection.ShapeRange.Fill.Visible = msoFalse
End If
End Sub


Problem is I want the checkbox to use the variable ' i ' like the rectangle entry does, but when I change the code it always acts upon only the true portion whether I have the check box checked or not. So even if the check box is unchecked or false so to speak it sees it acts as if it is true in this sub.

Sub CheckBox368_Click()
i = 368
If ("CheckBox" & i) = True Then
Sheets("Order_Entry").Shapes("Rectangle " & i).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
End If
If ("CheckBox" & i) = False Then
Sheets("Order_Entry").Shapes("Rectangle " & i).Select
Selection.ShapeRange.Fill.Visible = msoFalse
End If
End Sub

I am sure it is just a syntex type issue but do not understand why it never sees the check box as false if I unselect it like it does in the first sub.

Thanks,
Larry

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ltyson" <[email protected]> wrote in
message news:[email protected]...
>
> I have two sub routines, the first one does exactly what I want. If the
> checkbox on the user form is checked, then the corresponding rectangle
> is filled in on the worksheet. If I uncheck the check box, then the
> corrsponding rectangle is unfilled for lack of a better term.
>
> Sub CheckBox368_Click()
> i = 368
> If CheckBox368 = True Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
> Selection.ShapeRange.Fill.Visible = msoTrue
> Selection.ShapeRange.Fill.Solid
> End If
> If CheckBox368 = False Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.Visible = msoFalse
> End If
> End Sub
>
>
> Problem is I want the checkbox to use the variable ' i ' like the
> rectangle entry does, but when I change the code it always acts upon
> only the true portion whether I have the check box checked or not. So
> even if the check box is unchecked or false so to speak it sees it acts
> as if it is true in this sub.
>
> Sub CheckBox368_Click()
> i = 368
> If ("CheckBox" & i) = True Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
> Selection.ShapeRange.Fill.Visible = msoTrue
> Selection.ShapeRange.Fill.Solid
> End If
> If ("CheckBox" & i) = False Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.Visible = msoFalse
> End If
> End Sub
>
> I am sure it is just a syntex type issue but do not understand why it
> never sees the check box as false if I unselect it like it does in the
> first sub.
>
> Thanks,
> Larry
>
>
> --
> ltyson
> ------------------------------------------------------------------------
> ltyson's Profile:
http://www.excelforum.com/member.php...fo&userid=2768
> View this thread: http://www.excelforum.com/showthread...hreadid=397581
>

[Bob Phillips]

This should show you the syntax that you need

If Me.Controls("CheckBox" & i) Then
MsgBox "Checked"
Else
MsgBox "Unchecked"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ltyson" <[email protected]> wrote in
message news:[email protected]...
>
> I have two sub routines, the first one does exactly what I want. If the
> checkbox on the user form is checked, then the corresponding rectangle
> is filled in on the worksheet. If I uncheck the check box, then the
> corrsponding rectangle is unfilled for lack of a better term.
>
> Sub CheckBox368_Click()
> i = 368
> If CheckBox368 = True Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
> Selection.ShapeRange.Fill.Visible = msoTrue
> Selection.ShapeRange.Fill.Solid
> End If
> If CheckBox368 = False Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.Visible = msoFalse
> End If
> End Sub
>
>
> Problem is I want the checkbox to use the variable ' i ' like the
> rectangle entry does, but when I change the code it always acts upon
> only the true portion whether I have the check box checked or not. So
> even if the check box is unchecked or false so to speak it sees it acts
> as if it is true in this sub.
>
> Sub CheckBox368_Click()
> i = 368
> If ("CheckBox" & i) = True Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
> Selection.ShapeRange.Fill.Visible = msoTrue
> Selection.ShapeRange.Fill.Solid
> End If
> If ("CheckBox" & i) = False Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.Visible = msoFalse
> End If
> End Sub
>
> I am sure it is just a syntex type issue but do not understand why it
> never sees the check box as false if I unselect it like it does in the
> first sub.
>
> Thanks,
> Larry
>
>
> --
> ltyson
> ------------------------------------------------------------------------
> ltyson's Profile:
http://www.excelforum.com/member.php...fo&userid=2768
> View this thread: http://www.excelforum.com/showthread...hreadid=397581
>

[ltyson]

Bob thank you. The one word 'controls' was the missing peice. This new code pasted below now works perfect:

Sub CheckBox359_Click()
i = 359
If Controls("CheckBox" & i) = True Then
Sheets("Order_Entry").Shapes("Rectangle " & i).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
End If
If Controls("CheckBox" & i) = False Then
Sheets("Order_Entry").Shapes("Rectangle " & i).Select
Selection.ShapeRange.Fill.Visible = msoFalse
End If
End Sub

It is odd to me why i have to use:

If Controls("CheckBox" & i) = True Then

if I use the variable but:

if Checkbox359 = True Then

works fine if I don't use the variable. Looks like I have more learning to do.

Thanks for your help,

Larry

[Bob Phillips]

You are dealing with objects here, it just doesn't wotk that you can refer
to them partially and with variables.

You also missed soomething else I added in the code, albeiet I was not
explicit, so compare this code to yours

Sub CheckBox359_Click()
i = 359
If Controls("CheckBox" & i) Then
Sheets("Order_Entry").Shapes("Rectangle " & i).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Else
Sheets("Order_Entry").Shapes("Rectangle " & i).Select
Selection.ShapeRange.Fill.Visible = msoFalse
End If
End Sub

or even

Sub CheckBox359_Click()
i = 359
If Controls("CheckBox" & i) Then
With Sheets("Order_Entry").Shapes("Rectangle " & i) _
.ShapeRange.Fill.ForeColor.SchemeColor = 8
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.Solid
End With
Else
Sheets("Order_Entry").Shapes("Rectangle " & i) _
.ShapeRange.Fill.Visible = msoFalse
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ltyson" <[email protected]> wrote in
message news:[email protected]...
>
> Bob thank you. The one word 'controls' was the missing peice. This new
> code pasted below now works perfect:
>
> Sub CheckBox359_Click()
> i = 359
> If Controls("CheckBox" & i) = True Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
> Selection.ShapeRange.Fill.Visible = msoTrue
> Selection.ShapeRange.Fill.Solid
> End If
> If Controls("CheckBox" & i) = False Then
> Sheets("Order_Entry").Shapes("Rectangle " & i).Select
> Selection.ShapeRange.Fill.Visible = msoFalse
> End If
> End Sub
>
> It is odd to me why i have to use:
>
> If Controls("CheckBox" & i) = True Then
>
> if I use the variable but:
>
> if Checkbox359 = True Then
>
> works fine if I don't use the variable. Looks like I have more
> learning to do.
>
> Thanks for your help,
>
> Larry
>
>
> --
> ltyson
> ------------------------------------------------------------------------
> ltyson's Profile:
http://www.excelforum.com/member.php...fo&userid=2768
> View this thread: http://www.excelforum.com/showthread...hreadid=397581
>