| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing text boxes but do't include disabled ones??
Hi again,
I have a command button (cmdsubtotal) which adds 8 text boxes. I got the following code from a post a few weeks ago and it works perfectly - thank you! Private Sub cmdsubtotal_Click() Dim MyTotal As Double MyTotal = 0 If IsNumeric(Trim(Me.txtprice1.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice1.Value) End If If IsNumeric(Trim(Me.txtprice2.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice2.Value) End If If IsNumeric(Trim(Me.txtprice3.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice3.Value) End If If IsNumeric(Trim(Me.txtprice4.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice4.Value) End If If IsNumeric(Trim(Me.txtprice5.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice5.Value) End If If IsNumeric(Trim(Me.txtprice6.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice6.Value) End If If IsNumeric(Trim(Me.txtprice7.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice7.Value) End If If IsNumeric(Trim(Me.txtprice8.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice8.Value) End If Me.txtSubTotal.Value = MyTotal End Sub No I have added some check boxes which disable the corresponding text box when ticked. How can I adjust the code above to NOT include the text box value if it has been disabled? Thanks in advance :) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
summing text boxes but do't include disabled ones??
hi
i think all you would have to do is add another if to test if the check box is checked. Private Sub cmdsubtotal_Click() Dim MyTotal As Double MyTotal = 0 If Me.checkbox1.value = true then ' it's checked If IsNumeric(Trim(Me.txtprice1.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice1.Value) End If End If 'etc. etc. 'work your way down the text/check boxes. end sub untested but if the check box value is false, it should skip the rest of the clause. try it. post back if it don't work. regards FSt1 "Rachel" wrote: Hi again, I have a command button (cmdsubtotal) which adds 8 text boxes. I got the following code from a post a few weeks ago and it works perfectly - thank you! Private Sub cmdsubtotal_Click() Dim MyTotal As Double MyTotal = 0 If IsNumeric(Trim(Me.txtprice1.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice1.Value) End If If IsNumeric(Trim(Me.txtprice2.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice2.Value) End If If IsNumeric(Trim(Me.txtprice3.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice3.Value) End If If IsNumeric(Trim(Me.txtprice4.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice4.Value) End If If IsNumeric(Trim(Me.txtprice5.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice5.Value) End If If IsNumeric(Trim(Me.txtprice6.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice6.Value) End If If IsNumeric(Trim(Me.txtprice7.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice7.Value) End If If IsNumeric(Trim(Me.txtprice8.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice8.Value) End If Me.txtSubTotal.Value = MyTotal End Sub No I have added some check boxes which disable the corresponding text box when ticked. How can I adjust the code above to NOT include the text box value if it has been disabled? Thanks in advance :) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
summing text boxes but do't include disabled ones??
Rachel,
see if this does what you want: Private Sub cmdsubtotal_Click() Dim Ctl As Control Dim i As Integer Dim MyTotal As Double MyTotal = 0 For i = 1 To 8 Set Ctl = Me.Controls("txtprice" & i) If IsNumeric(Trim(Ctl.Value)) Then With Ctl If .Enabled = True Then MyTotal = _ MyTotal + Trim(Me.Controls("txtprice" & i).Value) Else Me.Controls("txtprice" & i).Text = 0 End If End With Else Me.Controls("txtprice" & i).Text = "" End If Next i Me.txtSubTotal.Value = MyTotal End Sub -- jb "Rachel" wrote: Hi again, I have a command button (cmdsubtotal) which adds 8 text boxes. I got the following code from a post a few weeks ago and it works perfectly - thank you! Private Sub cmdsubtotal_Click() Dim MyTotal As Double MyTotal = 0 If IsNumeric(Trim(Me.txtprice1.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice1.Value) End If If IsNumeric(Trim(Me.txtprice2.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice2.Value) End If If IsNumeric(Trim(Me.txtprice3.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice3.Value) End If If IsNumeric(Trim(Me.txtprice4.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice4.Value) End If If IsNumeric(Trim(Me.txtprice5.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice5.Value) End If If IsNumeric(Trim(Me.txtprice6.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice6.Value) End If If IsNumeric(Trim(Me.txtprice7.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice7.Value) End If If IsNumeric(Trim(Me.txtprice8.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice8.Value) End If Me.txtSubTotal.Value = MyTotal End Sub No I have added some check boxes which disable the corresponding text box when ticked. How can I adjust the code above to NOT include the text box value if it has been disabled? Thanks in advance :) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
summing text boxes but do't include disabled ones??
John - spot on as always!! thank you.
I really appreciate you taking the time to answer all my questions. I never would be able to work these things out myself!! "john" wrote: Rachel, see if this does what you want: Private Sub cmdsubtotal_Click() Dim Ctl As Control Dim i As Integer Dim MyTotal As Double MyTotal = 0 For i = 1 To 8 Set Ctl = Me.Controls("txtprice" & i) If IsNumeric(Trim(Ctl.Value)) Then With Ctl If .Enabled = True Then MyTotal = _ MyTotal + Trim(Me.Controls("txtprice" & i).Value) Else Me.Controls("txtprice" & i).Text = 0 End If End With Else Me.Controls("txtprice" & i).Text = "" End If Next i Me.txtSubTotal.Value = MyTotal End Sub -- jb "Rachel" wrote: Hi again, I have a command button (cmdsubtotal) which adds 8 text boxes. I got the following code from a post a few weeks ago and it works perfectly - thank you! Private Sub cmdsubtotal_Click() Dim MyTotal As Double MyTotal = 0 If IsNumeric(Trim(Me.txtprice1.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice1.Value) End If If IsNumeric(Trim(Me.txtprice2.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice2.Value) End If If IsNumeric(Trim(Me.txtprice3.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice3.Value) End If If IsNumeric(Trim(Me.txtprice4.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice4.Value) End If If IsNumeric(Trim(Me.txtprice5.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice5.Value) End If If IsNumeric(Trim(Me.txtprice6.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice6.Value) End If If IsNumeric(Trim(Me.txtprice7.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice7.Value) End If If IsNumeric(Trim(Me.txtprice8.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice8.Value) End If Me.txtSubTotal.Value = MyTotal End Sub No I have added some check boxes which disable the corresponding text box when ticked. How can I adjust the code above to NOT include the text box value if it has been disabled? Thanks in advance :) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
summing text boxes but do't include disabled ones??
Hi Rachel,
you are most welcome & thanks for your feedback. Just in case you have not seen it, I updated your lookup function post. -- jb "Rachel" wrote: John - spot on as always!! thank you. I really appreciate you taking the time to answer all my questions. I never would be able to work these things out myself!! "john" wrote: Rachel, see if this does what you want: Private Sub cmdsubtotal_Click() Dim Ctl As Control Dim i As Integer Dim MyTotal As Double MyTotal = 0 For i = 1 To 8 Set Ctl = Me.Controls("txtprice" & i) If IsNumeric(Trim(Ctl.Value)) Then With Ctl If .Enabled = True Then MyTotal = _ MyTotal + Trim(Me.Controls("txtprice" & i).Value) Else Me.Controls("txtprice" & i).Text = 0 End If End With Else Me.Controls("txtprice" & i).Text = "" End If Next i Me.txtSubTotal.Value = MyTotal End Sub -- jb "Rachel" wrote: Hi again, I have a command button (cmdsubtotal) which adds 8 text boxes. I got the following code from a post a few weeks ago and it works perfectly - thank you! Private Sub cmdsubtotal_Click() Dim MyTotal As Double MyTotal = 0 If IsNumeric(Trim(Me.txtprice1.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice1.Value) End If If IsNumeric(Trim(Me.txtprice2.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice2.Value) End If If IsNumeric(Trim(Me.txtprice3.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice3.Value) End If If IsNumeric(Trim(Me.txtprice4.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice4.Value) End If If IsNumeric(Trim(Me.txtprice5.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice5.Value) End If If IsNumeric(Trim(Me.txtprice6.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice6.Value) End If If IsNumeric(Trim(Me.txtprice7.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice7.Value) End If If IsNumeric(Trim(Me.txtprice8.Value)) Then MyTotal = MyTotal + Trim(Me.txtprice8.Value) End If Me.txtSubTotal.Value = MyTotal End Sub No I have added some check boxes which disable the corresponding text box when ticked. How can I adjust the code above to NOT include the text box value if it has been disabled? Thanks in advance :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 30 CHECK BOXES to be disabled | Excel Programming | |||
| Macro - To Include Summing All Similar Entries | Excel Discussion (Misc queries) | |||
| Summing two text boxes on a form - HELP! | Excel Programming | |||
| Summing the values in text boxes | Excel Programming | |||
| disabled check boxes | Excel Discussion (Misc queries) | |||
Linear Mode
