CheckBox Help

[Joe_Hunt via OfficeKB.com]

I have an issue with CheckBoxes on a UserForm not doing what I want them to,
and I hope somebody can help me with it. The workbook includes over 100
vehicles, and each month we go through the process of uploading new values
for these vehicles into our “system.†The workbook consists of the previous
month’s values compared to two outside sources. After loading these values
into the workbook I or my boss activates the UserForm and then goes from
vehicle to vehicle making a decision on which value to accept (one of the
outside sources is from an average of auction values from around the country).
On the UserForm are lots of different controls, including command buttons,
ComboBoxes, etc, that give the information necessary to make this decision.
There are 4 CheckBoxes also, and therein lies my issue. When I click a
CheckBox two of them are supposed to copy and paste special the percentage of
difference between the current market value of the vehicle (the auction site)
and what’s in the system now into a cell that several dozen formulas link to
that changes the value in the system to the CMV and projects it out over the
next 36 months. The other two are simply to place a “P†in a specified cell.
These work fine unless you back up (I have one command button that goes to
the next sheet and another one that goes to the previous one). When I do it
erases what I put in the cell.

For example if I have this after entering the three sources of data:
$18,844 -$3,147.68 11.5%

When I click the CheckBox it should move the 11.5% to either T8 or T55
depending on which vehicle I’m looking at, and it does. The formula in the
cell where the 11.5% was originally takes that value to 0% as it should. If I
go the next vehicle and then come back, sometimes it erases the value in T8
and/or T55.

Does anybody have a suggestion on how to fix this? Here’s the coding as it
stands now, and below that is what I think is going on:

Under the UserForm activate:
Private Sub UserForm_Activate()
If ActiveSheet.Range("S8") = "P" Then
Me.CheckBox2.Value = True
End If
If ActiveSheet.Range("S55") = "P" Then
Me.CheckBox1.Value = True
End If
If ActiveSheet.Range("T8") <> 0 Then
Me.CheckBox5.Value = True
End If
If ActiveSheet.Range("T55") <> 0 Then
Me.CheckBox4.Value = True
End If

End Sub

On the previous button:
Private Sub CommandButton4_Click()
If ActiveSheet.Range("B5") = "2008 Chrysler 300 Cycle 1" Then
Unload UserForm4
Else
ActiveSheet.Previous.Select
Unload UserForm4
UserForm4.Show vbModeless
End If
End Sub

On the next button:
Private Sub CommandButton3_Click()
If ActiveSheet.Range("B5") = "2008 Jeep Wrangler 4WD Cycle 2" Then
Unload UserForm4
Else
ActiveSheet.Next.Select
Unload UserForm4
UserForm4.Show vbModeless
End If
End Sub

CheckBox5:
Private Sub CheckBox5_Click()

If TextBox5 = "10/31/2008" Then
If Me.CheckBox5.Value = True Then
ActiveSheet.Range("O20").Select
Selection.Copy
Range("T8").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Else
ActiveSheet.Range("T8").Value = ""
End If

ElseIf TextBox5 = "11/30/2008" Then
If Me.CheckBox5.Value = True Then
ActiveSheet.Range("O21").Select
Selection.Copy
Range("T8").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Else
ActiveSheet.Range("T8").Value = ""
End If

CheckBox4:
Private Sub CheckBox4_Click()

If TextBox5 = "10/31/2008" Then
If Me.CheckBox4.Value = True Then
ActiveSheet.Range("O55").Select
Selection.Copy
Range("T55").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Else
ActiveSheet.Range("T55").Value = ""
End If

ElseIf TextBox5 = "11/30/2008" Then
If Me.CheckBox4.Value = True Then
ActiveSheet.Range("O56").Select
Selection.Copy
Range("T55").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Else
ActiveSheet.Range("T55").Value = ""
End If


It is registering whether or not there’s already a check mark in the
appropriate boxes like it’s supposed to. It appears to me that when I click
the previous or next button it’s reading the CheckBox coding first and
blanking out the cell.

 

[Joel]

In two locations (previous and next button) try reversing the following two
lines

from
ActiveSheet.Next.Select
Unload UserForm4
to
Unload UserForm4
ActiveSheet.Next.Select

 

[Joe_Hunt via OfficeKB.com]

That didn't fix it. Thanks though.

In two locations (previous and next button) try reversing the following two
lines

from
ActiveSheet.Next.Select
Unload UserForm4
to
Unload UserForm4
ActiveSheet.Next.Select

I have an issue with CheckBoxes on a UserForm not doing what I want them to,
and I hope somebody can help me with it. The workbook includes over 100

[quoted text clipped - 129 lines]

the previous or next button it’s reading the CheckBox coding first and
blanking out the cell.

 

[Joe_Hunt via OfficeKB.com]

As an update I've found that refreshing a graph I have on the UserForm fixes
this (as a work around anyway). Is there a way to refresh without closing and
opening the form? For some reason that doesn't do it.

That didn't fix it. Thanks though.

In two locations (previous and next button) try reversing the following two
lines

[quoted text clipped - 11 lines]

 

[JLGWhiz]

It has to be related to the Unload and reloading of the UserForm. Are those
cells linked to the CheckBoxes? If you walk through the code using F8 line
by line, you cand see which line causes the cell values to change. I suspect
it is the Unload UserForm1 line that does it. But I cannot see how the cells
are connected to the form from what you have posted.

That didn't fix it. Thanks though.

In two locations (previous and next button) try reversing the following two
lines

from
ActiveSheet.Next.Select
Unload UserForm4
to
Unload UserForm4
ActiveSheet.Next.Select

I have an issue with CheckBoxes on a UserForm not doing what I want them to,
and I hope somebody can help me with it. The workbook includes over 100

[quoted text clipped - 129 lines]

the previous or next button it’s reading the CheckBox coding first and
blanking out the cell.

 

[JLGWhiz]

there is a Refresh method for charts, I have never used it and am not sure
how iit would be applied in your case.

As an update I've found that refreshing a graph I have on the UserForm fixes
this (as a work around anyway). Is there a way to refresh without closing and
opening the form? For some reason that doesn't do it.

That didn't fix it. Thanks though.

In two locations (previous and next button) try reversing the following two
lines

[quoted text clipped - 11 lines]

the previous or next button it’s reading the CheckBox coding first and
blanking out the cell.

 

[Joe_Hunt via OfficeKB.com]

I think you're right on one level, but the problem is that to get the OWC
Chart on the UserForm to refresh I have to refresh the UserForm. I've done
that by unloading and reloading the UserForm. Is there another way to do that?

It has to be related to the Unload and reloading of the UserForm. Are those
cells linked to the CheckBoxes? If you walk through the code using F8 line
by line, you cand see which line causes the cell values to change. I suspect
it is the Unload UserForm1 line that does it. But I cannot see how the cells
are connected to the form from what you have posted.

That didn't fix it. Thanks though.

[quoted text clipped - 13 lines]