J
Joe_Hunt via OfficeKB.com
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.
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.