variable help....

L

ltyson

I have two sub routines, the first one does exactly what I want. If th
checkbox on the user form is checked, then the corresponding rectangl
is filled in on the worksheet. If I uncheck the check box, then th
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 th
rectangle entry does, but when I change the code it always acts upo
only the true portion whether I have the check box checked or not. S
even if the check box is unchecked or false so to speak it sees it act
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 i
never sees the check box as false if I unselect it like it does in th
first sub.

Thanks,
Larr
 
B

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)
 
L

ltyson

Bob thank you. The one word 'controls' was the missing peice. This ne
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 mor
learning to do. :)

Thanks for your help,

Larr
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top