Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Help
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 months 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 whats 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 Im 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? Heres 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 theres already a check mark in the appropriate boxes like its supposed to. It appears to me that when I click the previous or next button its reading the CheckBox coding first and blanking out the cell. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200810/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Help
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" wrote: 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 months 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 whats 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 Im 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? Heres 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 theres already a check mark in the appropriate boxes like its supposed to. It appears to me that when I click the previous or next button its reading the CheckBox coding first and blanking out the cell. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200810/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Help
That didn't fix it. Thanks though.
Joel wrote: 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 its reading the CheckBox coding first and blanking out the cell. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200810/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Help
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. Joe_Hunt wrote: 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 its reading the CheckBox coding first and blanking out the cell. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200810/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Help
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. "Joe_Hunt via OfficeKB.com" wrote: That didn't fix it. Thanks though. Joel wrote: 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 its reading the CheckBox coding first and blanking out the cell. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200810/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Help
there is a Refresh method for charts, I have never used it and am not sure
how iit would be applied in your case. "Joe_Hunt via OfficeKB.com" wrote: 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. Joe_Hunt wrote: 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 its reading the CheckBox coding first and blanking out the cell. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200810/1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Help
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? JLGWhiz wrote: 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] the previous or next button its reading the CheckBox coding first and blanking out the cell. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have Checkbox A uncheck with checked Checkbox B | Excel Discussion (Misc queries) | |||
link a checkbox in a sheet to a checkbox on a userform? | Excel Programming | |||
How do I link one checkbox to update another checkbox? | Excel Programming | |||
CheckBox | Excel Programming | |||
checkbox on form reset from checkbox on sheet | Excel Programming |