Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multipage Query
Just alter this to address all the textboxes you want included:
TextBox8.Text = CLng("0" & TextBox2.Text) + _ CLng("0" & TextBox3.Text) + _ CLng("0" & TextBox4.Text) + _ CLng("0" & TextBox5.Text) + _ CLng("0" & TextBox6.Text) + _ CLng("0" & TextBox7.Text) Perhaps calculate a subtotal for each page and then add the subtotals together to simplify. or you could do this: Private Sub CommandButton1_Click() Dim pg As Page For Each pg In Me.MultiPage1.Pages If pg.Index < 0 Then For Each ctrl In pg.Controls If TypeOf ctrl Is MSforms.TextBox Then Sum = Sum + CLng("0" & ctrl.Value) End If Next End If Next Me.TextBox4.Value = Sum End Sub In my test setup, Textbox4 was the textbox on the first page of the multipage to hold the result. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "MDL2004" wrote in message ... Hi I'm trying to use a multipage to help with input data quickly into a main spreadsheet. Design wise everything is fine, it's just i'm trying to calculate the total of each multipage(8 in all) to give a subtotal as the user tabs through each page. I've used some code that i picked up on from the discussion group but it only adds up page 1 and I need the same code to calculate pages 2 onwards. I've attached the code in case anyone can put me straight as I am unsure what I'm doing wrong!! Private Sub textbox2_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox3_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox4_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox5_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox6_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox7_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub Calctotal() TextBox8.Text = CLng("0" & TextBox2.Text) + _ CLng("0" & TextBox3.Text) + _ CLng("0" & TextBox4.Text) + _ CLng("0" & TextBox5.Text) + _ CLng("0" & TextBox6.Text) + _ CLng("0" & TextBox7.Text) End Sub Private Sub userform_click() End Sub Look forward any comments |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multipage Query
Cheers Tom
Tried what you suggested and it has done the trick, just what i was trying to do!! Thanks again!! "Tom Ogilvy" wrote: Just alter this to address all the textboxes you want included: TextBox8.Text = CLng("0" & TextBox2.Text) + _ CLng("0" & TextBox3.Text) + _ CLng("0" & TextBox4.Text) + _ CLng("0" & TextBox5.Text) + _ CLng("0" & TextBox6.Text) + _ CLng("0" & TextBox7.Text) Perhaps calculate a subtotal for each page and then add the subtotals together to simplify. or you could do this: Private Sub CommandButton1_Click() Dim pg As Page For Each pg In Me.MultiPage1.Pages If pg.Index < 0 Then For Each ctrl In pg.Controls If TypeOf ctrl Is MSforms.TextBox Then Sum = Sum + CLng("0" & ctrl.Value) End If Next End If Next Me.TextBox4.Value = Sum End Sub In my test setup, Textbox4 was the textbox on the first page of the multipage to hold the result. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "MDL2004" wrote in message ... Hi I'm trying to use a multipage to help with input data quickly into a main spreadsheet. Design wise everything is fine, it's just i'm trying to calculate the total of each multipage(8 in all) to give a subtotal as the user tabs through each page. I've used some code that i picked up on from the discussion group but it only adds up page 1 and I need the same code to calculate pages 2 onwards. I've attached the code in case anyone can put me straight as I am unsure what I'm doing wrong!! Private Sub textbox2_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox3_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox4_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox5_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox6_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub textbox7_keypress(ByVal keyascii As _ MSforms.ReturnInteger) If keyascii < Asc("0") Or keyascii Asc("9") Then Interaction.Beep keyascii = 0 End If End Sub Private Sub Calctotal() TextBox8.Text = CLng("0" & TextBox2.Text) + _ CLng("0" & TextBox3.Text) + _ CLng("0" & TextBox4.Text) + _ CLng("0" & TextBox5.Text) + _ CLng("0" & TextBox6.Text) + _ CLng("0" & TextBox7.Text) End Sub Private Sub userform_click() End Sub Look forward any comments |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MultiPage tab? | Excel Discussion (Misc queries) | |||
MultiPage | Excel Discussion (Misc queries) | |||
How do I use multipage | Excel Discussion (Misc queries) | |||
vba - using multipage | Excel Programming | |||
Multipage | Excel Programming |