I have a problem with a CheckBox on a UserForm that I can't figure out how to
fix or work around, and I was hoping somebody could help me with it. I have a
UserForm with several different controls that I use to make decisions on what
variables to accept and what not to (including ListBoxes, TextBoxes,
ComboBoxes, etc). I have 4 CheckBoxes that by clicking them changes the
values in the worksheet. Two of these are no problem, and they work fine
since all they do is put a "P" in a cell or take it away. The other two are
the issue in that they cause a value from one cell to be moved to another. I
can't just make it "=" because it makes a circular reference. Here's the
coding I have for one month and one CheckBox.

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

I used a Paste Special to avoid the circular reference. Cell O67 is where the
data is that, if I click CheckBox4, is copied into cell T55 (this formula
will go through 72 versions in all, but if this can be made to work I can
adjust the others).

Here's the problem. When I click the CheckBox it does what it's supposed to,
copying and pasting the value from cell O67 into T55. If I go on to the next
sheet then go back however the amount in T55 is erased, although the CheckBox
on the UserForm is still checked. I have this coding to make it look to see
if it was checked (probably a better way to do that but I don't know it):


If ActiveSheet.Range("T55") < 0 Then
Me.CheckBox4.Value = True

I hope this is enough info. Any help at all would be appreciated.

--
Message posted via http://www.officekb.com

Joe, the code you have posted will not, in and of itself, cause T55 tp be
cleared. However, if you have some worksheet event code that triggers when
you go from one sheet to another, it could be causing it. You also have to
be very careful when you use Select and Selection in your code, because you
could issue a command for a Selection with the wrong cell or range selected.
If you know how to walk through the code line by line, you might want to do
that while watching cell T55 and see exactly where it dissapears. To execute
the code and watch the worksheet at the same time, just click the minimize
button for the VBE and you can use your mouse to grab the edges and size it
so that you can see the code and the worksheet at the same time. Then use F8
or the step button on the debug toolbar to step through the code. To shorten
the process, you can put a breakpoint on the line where you paste to T55 and
then start the line by line stepping from there. To set the breakpoint,
click on the panel between the code window and the project/properties window
of the VBE. When you find the actual line of code that does the delete, post
back if you need help fixing it.

"Joe_Hunt via OfficeKB.com" wrote:

I have a problem with a CheckBox on a UserForm that I can't figure out how to
fix or work around, and I was hoping somebody could help me with it. I have a
UserForm with several different controls that I use to make decisions on what
variables to accept and what not to (including ListBoxes, TextBoxes,
ComboBoxes, etc). I have 4 CheckBoxes that by clicking them changes the
values in the worksheet. Two of these are no problem, and they work fine
since all they do is put a "P" in a cell or take it away. The other two are
the issue in that they cause a value from one cell to be moved to another. I
can't just make it "=" because it makes a circular reference. Here's the
coding I have for one month and one CheckBox.

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

I used a Paste Special to avoid the circular reference. Cell O67 is where the
data is that, if I click CheckBox4, is copied into cell T55 (this formula
will go through 72 versions in all, but if this can be made to work I can
adjust the others).

Here's the problem. When I click the CheckBox it does what it's supposed to,
copying and pasting the value from cell O67 into T55. If I go on to the next
sheet then go back however the amount in T55 is erased, although the CheckBox
on the UserForm is still checked. I have this coding to make it look to see
if it was checked (probably a better way to do that but I don't know it):


If ActiveSheet.Range("T55") < 0 Then
Me.CheckBox4.Value = True

I hope this is enough info. Any help at all would be appreciated.

--
Message posted via http://www.officekb.com

Thank you. I'll give it a try.

JLGWhiz wrote:
Joe, the code you have posted will not, in and of itself, cause T55 tp be
cleared. However, if you have some worksheet event code that triggers when
you go from one sheet to another, it could be causing it. You also have to
be very careful when you use Select and Selection in your code, because you
could issue a command for a Selection with the wrong cell or range selected.
If you know how to walk through the code line by line, you might want to do
that while watching cell T55 and see exactly where it dissapears. To execute
the code and watch the worksheet at the same time, just click the minimize
button for the VBE and you can use your mouse to grab the edges and size it
so that you can see the code and the worksheet at the same time. Then use F8
or the step button on the debug toolbar to step through the code. To shorten
the process, you can put a breakpoint on the line where you paste to T55 and
then start the line by line stepping from there. To set the breakpoint,
click on the panel between the code window and the project/properties window
of the VBE. When you find the actual line of code that does the delete, post
back if you need help fixing it.

I have a problem with a CheckBox on a UserForm that I can't figure out how to
fix or work around, and I was hoping somebody could help me with it. I have a
[quoted text clipped - 39 lines]

I hope this is enough info. Any help at all would be appreciated.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200810/1