Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
i need some abstract guidence around using dialogue boxes as an interface to
both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
Am I correct in assuming that you want all these boxes to display
simultaneously? If so, then Text Boxes and Labels might do what you want. You can use Labels to display data and Text Boxes to enter data. When you create a Text Box, it creates an associated Label but if you dont want the associated Label, you can delete it. The Labels and Text Boxes can be inserted directly on worksheets or in Forms created via the VBA editor. To access Labels and Text Boxes:- Xl2007: Click the Insert button in the Controls group on the Developer ribbon. If Developer ribbon not visible, then see Help on how to display it. There are two types of controls, Forms controls and ActiveX controls. Its my opinion that the ActiveX are the preferable ones to use. Pre xl2007 the ActiveX controls are on the Toolbox toolbar. The Forms controls are on the Forms toolbar. If using ActiveX controls, you need to be in Design mode to edit the controls. This automatically turns on when the control is created but you need to turn it off when finished setting the required properties before you can use the control and turn it back on if you need to do more editing. The Design button looks like a Set Square, Ruler and Pencil. Early versions of xl did not have ActiveX controls. Not sure but I think they started in xl2000 and before that you only have Forms controls. Hope this helps and feel free to get back to me. -- Regards, OssieMac "amit" wrote: i need some abstract guidence around using dialogue boxes as an interface to both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
Sorry! I was having a Seniors moment when I said "When you create a Text Box,
it creates an associated Label" that's in Access. -- Regards, OssieMac "OssieMac" wrote: Am I correct in assuming that you want all these boxes to display simultaneously? If so, then Text Boxes and Labels might do what you want. You can use Labels to display data and Text Boxes to enter data. When you create a Text Box, it creates an associated Label but if you dont want the associated Label, you can delete it. The Labels and Text Boxes can be inserted directly on worksheets or in Forms created via the VBA editor. To access Labels and Text Boxes:- Xl2007: Click the Insert button in the Controls group on the Developer ribbon. If Developer ribbon not visible, then see Help on how to display it. There are two types of controls, Forms controls and ActiveX controls. Its my opinion that the ActiveX are the preferable ones to use. Pre xl2007 the ActiveX controls are on the Toolbox toolbar. The Forms controls are on the Forms toolbar. If using ActiveX controls, you need to be in Design mode to edit the controls. This automatically turns on when the control is created but you need to turn it off when finished setting the required properties before you can use the control and turn it back on if you need to do more editing. The Design button looks like a Set Square, Ruler and Pencil. Early versions of xl did not have ActiveX controls. Not sure but I think they started in xl2000 and before that you only have Forms controls. Hope this helps and feel free to get back to me. -- Regards, OssieMac "amit" wrote: i need some abstract guidence around using dialogue boxes as an interface to both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
hi - thanks a lot for your quick response....in the last couple of hrs i've got to some extent with this, i've got text boxes that spit information (whihc i've locked) and also allowed for user entry........etc..... this might be trivial but i have a set of boxes and a final box at the bottom that needs to sum up the above boxes as and when they are updated... i had the below addition line for each of the a,b,c,d,e tex boxes. what i'm intending to do with this is once a text box is modified the sum text box will change.... Private Sub Total_AfterUpdate() Total.Value = A.Value + B.Value + C.Value + D.Value + E.Value End Sub this worked but it pretty much concatenated the text boxes above as anything entered is stored as text. i need help with converting these entries into numbers before adding them. "OssieMac" wrote: Sorry! I was having a Seniors moment when I said "When you create a Text Box, it creates an associated Label" that's in Access. -- Regards, OssieMac "OssieMac" wrote: Am I correct in assuming that you want all these boxes to display simultaneously? If so, then Text Boxes and Labels might do what you want. You can use Labels to display data and Text Boxes to enter data. When you create a Text Box, it creates an associated Label but if you dont want the associated Label, you can delete it. The Labels and Text Boxes can be inserted directly on worksheets or in Forms created via the VBA editor. To access Labels and Text Boxes:- Xl2007: Click the Insert button in the Controls group on the Developer ribbon. If Developer ribbon not visible, then see Help on how to display it. There are two types of controls, Forms controls and ActiveX controls. Its my opinion that the ActiveX are the preferable ones to use. Pre xl2007 the ActiveX controls are on the Toolbox toolbar. The Forms controls are on the Forms toolbar. If using ActiveX controls, you need to be in Design mode to edit the controls. This automatically turns on when the control is created but you need to turn it off when finished setting the required properties before you can use the control and turn it back on if you need to do more editing. The Design button looks like a Set Square, Ruler and Pencil. Early versions of xl did not have ActiveX controls. Not sure but I think they started in xl2000 and before that you only have Forms controls. Hope this helps and feel free to get back to me. -- Regards, OssieMac "amit" wrote: i need some abstract guidence around using dialogue boxes as an interface to both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
Also Another quick question - why am i not able to use the form i've created
again in the same file? i will need to be able to use the same form a few time but with different criteria or pointing to different rows in a table? "OssieMac" wrote: Sorry! I was having a Seniors moment when I said "When you create a Text Box, it creates an associated Label" that's in Access. -- Regards, OssieMac "OssieMac" wrote: Am I correct in assuming that you want all these boxes to display simultaneously? If so, then Text Boxes and Labels might do what you want. You can use Labels to display data and Text Boxes to enter data. When you create a Text Box, it creates an associated Label but if you dont want the associated Label, you can delete it. The Labels and Text Boxes can be inserted directly on worksheets or in Forms created via the VBA editor. To access Labels and Text Boxes:- Xl2007: Click the Insert button in the Controls group on the Developer ribbon. If Developer ribbon not visible, then see Help on how to display it. There are two types of controls, Forms controls and ActiveX controls. Its my opinion that the ActiveX are the preferable ones to use. Pre xl2007 the ActiveX controls are on the Toolbox toolbar. The Forms controls are on the Forms toolbar. If using ActiveX controls, you need to be in Design mode to edit the controls. This automatically turns on when the control is created but you need to turn it off when finished setting the required properties before you can use the control and turn it back on if you need to do more editing. The Design button looks like a Set Square, Ruler and Pencil. Early versions of xl did not have ActiveX controls. Not sure but I think they started in xl2000 and before that you only have Forms controls. Hope this helps and feel free to get back to me. -- Regards, OssieMac "amit" wrote: i need some abstract guidence around using dialogue boxes as an interface to both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
You use the Val function to convert the text to numeric values. However, the
event you have would only run if you make a change to the Total text box. If you place an AfterUpdate event on each text box and call a sub to sum the values, it will update each time you make a change in any of the other text boxes. Place the following code in the Forms code module:- (Do not use a sub name which is used for events.) Private Sub Sum_Values() Total= Val(TextBox1) + Val(TextBox2) + Val(TextBox3) + Val(TextBox4) End Sub Then the following events in the Forms code calls the above sub each time a text box is updated:- Private Sub TextBox1_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox2_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox3_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox4_AfterUpdate() Call Sum_Values End Sub You could place the called sub in a module but you then need to use the Userform name with each Text box like the following:- Sub Sum_Values() UserForm1!Total.Value = Val(UserForm1!TextBox1) + _ Val(UserForm1!TextBox2) + Val(UserForm1!TextBox3) + _ Val(UserForm1!TextBox4) End Sub Not sure what you mean by "why am i not able to use the form i've created again in the same file?" If you close the form and Alt/F11 to open the VBA editor and then right click on the Userform name in the Project Explorer you can either select View Object or View Code. If this is not what you mean then pls get back to me with more explanation of what it is that you can't do. -- Regards, OssieMac "amit" wrote: Also Another quick question - why am i not able to use the form i've created again in the same file? i will need to be able to use the same form a few time but with different criteria or pointing to different rows in a table? "OssieMac" wrote: Sorry! I was having a Seniors moment when I said "When you create a Text Box, it creates an associated Label" that's in Access. -- Regards, OssieMac "OssieMac" wrote: Am I correct in assuming that you want all these boxes to display simultaneously? If so, then Text Boxes and Labels might do what you want. You can use Labels to display data and Text Boxes to enter data. When you create a Text Box, it creates an associated Label but if you dont want the associated Label, you can delete it. The Labels and Text Boxes can be inserted directly on worksheets or in Forms created via the VBA editor. To access Labels and Text Boxes:- Xl2007: Click the Insert button in the Controls group on the Developer ribbon. If Developer ribbon not visible, then see Help on how to display it. There are two types of controls, Forms controls and ActiveX controls. Its my opinion that the ActiveX are the preferable ones to use. Pre xl2007 the ActiveX controls are on the Toolbox toolbar. The Forms controls are on the Forms toolbar. If using ActiveX controls, you need to be in Design mode to edit the controls. This automatically turns on when the control is created but you need to turn it off when finished setting the required properties before you can use the control and turn it back on if you need to do more editing. The Design button looks like a Set Square, Ruler and Pencil. Early versions of xl did not have ActiveX controls. Not sure but I think they started in xl2000 and before that you only have Forms controls. Hope this helps and feel free to get back to me. -- Regards, OssieMac "amit" wrote: i need some abstract guidence around using dialogue boxes as an interface to both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
hi - thanks a tonne for your response. the sum value worked perfectly.
regarding the second question...here's some background information... i'm building a planning model that have a set of grwth drivers for each customer....my intension is for the user to click on the grwth lever for that customer and a dialogue box pops up with the customer name at the top and then a lot of info n input fields to take the info back.... Customer Name G1 G2 ABC Corp 12 15 MSN 14 19 so although the userform i created for say "G2" for ABC Corp, need to be able to reuse that userform for "G2" for Ninemsn...... my userform refers to the cell number and the customer name decides (thru some if else vba in my userform initiate sub) the relevant info for the customer to be put into the userform. i figured the only way to solve this would be able to copy the whole user form realting to "G2" to each of the customer line....... the other way to solve this problem would be to be able to assign the userform show sub to a cell rather than a "box",,,,,,,,,that way assigning the userform to each cell would pick up the relevant customer name from the left... may be i'm asking for too much here ............but thx for your feedback for my earliar questions... kind regards, Amit. "OssieMac" wrote: You use the Val function to convert the text to numeric values. However, the event you have would only run if you make a change to the Total text box. If you place an AfterUpdate event on each text box and call a sub to sum the values, it will update each time you make a change in any of the other text boxes. Place the following code in the Forms code module:- (Do not use a sub name which is used for events.) Private Sub Sum_Values() Total= Val(TextBox1) + Val(TextBox2) + Val(TextBox3) + Val(TextBox4) End Sub Then the following events in the Forms code calls the above sub each time a text box is updated:- Private Sub TextBox1_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox2_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox3_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox4_AfterUpdate() Call Sum_Values End Sub You could place the called sub in a module but you then need to use the Userform name with each Text box like the following:- Sub Sum_Values() UserForm1!Total.Value = Val(UserForm1!TextBox1) + _ Val(UserForm1!TextBox2) + Val(UserForm1!TextBox3) + _ Val(UserForm1!TextBox4) End Sub Not sure what you mean by "why am i not able to use the form i've created again in the same file?" If you close the form and Alt/F11 to open the VBA editor and then right click on the Userform name in the Project Explorer you can either select View Object or View Code. If this is not what you mean then pls get back to me with more explanation of what it is that you can't do. -- Regards, OssieMac "amit" wrote: Also Another quick question - why am i not able to use the form i've created again in the same file? i will need to be able to use the same form a few time but with different criteria or pointing to different rows in a table? "OssieMac" wrote: Sorry! I was having a Seniors moment when I said "When you create a Text Box, it creates an associated Label" that's in Access. -- Regards, OssieMac "OssieMac" wrote: Am I correct in assuming that you want all these boxes to display simultaneously? If so, then Text Boxes and Labels might do what you want. You can use Labels to display data and Text Boxes to enter data. When you create a Text Box, it creates an associated Label but if you dont want the associated Label, you can delete it. The Labels and Text Boxes can be inserted directly on worksheets or in Forms created via the VBA editor. To access Labels and Text Boxes:- Xl2007: Click the Insert button in the Controls group on the Developer ribbon. If Developer ribbon not visible, then see Help on how to display it. There are two types of controls, Forms controls and ActiveX controls. Its my opinion that the ActiveX are the preferable ones to use. Pre xl2007 the ActiveX controls are on the Toolbox toolbar. The Forms controls are on the Forms toolbar. If using ActiveX controls, you need to be in Design mode to edit the controls. This automatically turns on when the control is created but you need to turn it off when finished setting the required properties before you can use the control and turn it back on if you need to do more editing. The Design button looks like a Set Square, Ruler and Pencil. Early versions of xl did not have ActiveX controls. Not sure but I think they started in xl2000 and before that you only have Forms controls. Hope this helps and feel free to get back to me. -- Regards, OssieMac "amit" wrote: i need some abstract guidence around using dialogue boxes as an interface to both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
You could create a Combo Box (DropDown Box) on your form and use the change
event to re-populate the TextBoxes on the Form. Set the RowSource of the ComboBox to your list of Businesses. Syntax in the ComboBox Properties to set the RowSource:- Sheet1!A2:A5 The following is sample data on Sheet1. Col 1, Col 2 are simply the column headers that I used. Col 1 Col 2 Col 3 Col 4 Col 5 ABC Corp 18 11 10 17 MSN Corp 20 16 14 19 XYZ Corp 15 20 12 10 UVW Corp 13 17 14 12 Following are the macros for the Form:- Private Sub ComboBox1_Change() Dim rowNumb As Long Select Case ComboBox1.Value Case "ABC Corp" rowNumb = 2 Case "MSN Corp" rowNumb = 3 Case "XYZ Corp" rowNumb = 4 Case "UVW Corp" rowNumb = 5 Case Else MsgBox "Invalid selection" Exit Sub End Select TextBox1 = Sheets("Sheet1").Cells(rowNumb, "B") TextBox2 = Sheets("Sheet1").Cells(rowNumb, "C") TextBox3 = Sheets("Sheet1").Cells(rowNumb, "D") TextBox4 = Sheets("Sheet1").Cells(rowNumb, "E") Call Sum_TextBoxes End Sub Private Sub Sum_TextBoxes() Total = Val(TextBox1) + Val(TextBox2) + Val(TextBox3) + Val(TextBox4) End Sub -- Regards, OssieMac "amit" wrote: hi - thanks a tonne for your response. the sum value worked perfectly. regarding the second question...here's some background information... i'm building a planning model that have a set of grwth drivers for each customer....my intension is for the user to click on the grwth lever for that customer and a dialogue box pops up with the customer name at the top and then a lot of info n input fields to take the info back.... Customer Name G1 G2 ABC Corp 12 15 MSN 14 19 so although the userform i created for say "G2" for ABC Corp, need to be able to reuse that userform for "G2" for Ninemsn...... my userform refers to the cell number and the customer name decides (thru some if else vba in my userform initiate sub) the relevant info for the customer to be put into the userform. i figured the only way to solve this would be able to copy the whole user form realting to "G2" to each of the customer line....... the other way to solve this problem would be to be able to assign the userform show sub to a cell rather than a "box",,,,,,,,,that way assigning the userform to each cell would pick up the relevant customer name from the left... may be i'm asking for too much here ............but thx for your feedback for my earliar questions... kind regards, Amit. "OssieMac" wrote: You use the Val function to convert the text to numeric values. However, the event you have would only run if you make a change to the Total text box. If you place an AfterUpdate event on each text box and call a sub to sum the values, it will update each time you make a change in any of the other text boxes. Place the following code in the Forms code module:- (Do not use a sub name which is used for events.) Private Sub Sum_Values() Total= Val(TextBox1) + Val(TextBox2) + Val(TextBox3) + Val(TextBox4) End Sub Then the following events in the Forms code calls the above sub each time a text box is updated:- Private Sub TextBox1_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox2_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox3_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox4_AfterUpdate() Call Sum_Values End Sub You could place the called sub in a module but you then need to use the Userform name with each Text box like the following:- Sub Sum_Values() UserForm1!Total.Value = Val(UserForm1!TextBox1) + _ Val(UserForm1!TextBox2) + Val(UserForm1!TextBox3) + _ Val(UserForm1!TextBox4) End Sub Not sure what you mean by "why am i not able to use the form i've created again in the same file?" If you close the form and Alt/F11 to open the VBA editor and then right click on the Userform name in the Project Explorer you can either select View Object or View Code. If this is not what you mean then pls get back to me with more explanation of what it is that you can't do. -- Regards, OssieMac "amit" wrote: Also Another quick question - why am i not able to use the form i've created again in the same file? i will need to be able to use the same form a few time but with different criteria or pointing to different rows in a table? "OssieMac" wrote: Sorry! I was having a Seniors moment when I said "When you create a Text Box, it creates an associated Label" that's in Access. -- Regards, OssieMac "OssieMac" wrote: Am I correct in assuming that you want all these boxes to display simultaneously? If so, then Text Boxes and Labels might do what you want. You can use Labels to display data and Text Boxes to enter data. When you create a Text Box, it creates an associated Label but if you dont want the associated Label, you can delete it. The Labels and Text Boxes can be inserted directly on worksheets or in Forms created via the VBA editor. To access Labels and Text Boxes:- Xl2007: Click the Insert button in the Controls group on the Developer ribbon. If Developer ribbon not visible, then see Help on how to display it. There are two types of controls, Forms controls and ActiveX controls. Its my opinion that the ActiveX are the preferable ones to use. Pre xl2007 the ActiveX controls are on the Toolbox toolbar. The Forms controls are on the Forms toolbar. If using ActiveX controls, you need to be in Design mode to edit the controls. This automatically turns on when the control is created but you need to turn it off when finished setting the required properties before you can use the control and turn it back on if you need to do more editing. The Design button looks like a Set Square, Ruler and Pencil. Early versions of xl did not have ActiveX controls. Not sure but I think they started in xl2000 and before that you only have Forms controls. Hope this helps and feel free to get back to me. -- Regards, OssieMac "amit" wrote: i need some abstract guidence around using dialogue boxes as an interface to both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Dialogue Box
hi - thx for the response, i'm aware of combo box functionality but i'm not
looking to use this as a combo box functionality. essentially i want one userform for col3 for ABC corp and then the same userform again for col3 for MSN corp. the userfomr will be the same but the data in it will depend on what customer name is there in the corresponding cell from the left. the reason for this is to allow the user complete each of the grwth levers for each account before going to the next account. i activate the userform using the showuserform sub assgined to an invisible box placed over the corresponding cell (Col3, News COrp). i want to be able to resuse the from when i place the same box over the corresponding cell (Col3, MSN corp) and when i place it there it should pick up the name of the customer and populate the userform. apologise for being so detailed...i totally understand this forum is not for detailed help...and also due to the limit of text oonly entry i'm probably not being clear...... "OssieMac" wrote: You could create a Combo Box (DropDown Box) on your form and use the change event to re-populate the TextBoxes on the Form. Set the RowSource of the ComboBox to your list of Businesses. Syntax in the ComboBox Properties to set the RowSource:- Sheet1!A2:A5 The following is sample data on Sheet1. Col 1, Col 2 are simply the column headers that I used. Col 1 Col 2 Col 3 Col 4 Col 5 ABC Corp 18 11 10 17 MSN Corp 20 16 14 19 XYZ Corp 15 20 12 10 UVW Corp 13 17 14 12 Following are the macros for the Form:- Private Sub ComboBox1_Change() Dim rowNumb As Long Select Case ComboBox1.Value Case "ABC Corp" rowNumb = 2 Case "MSN Corp" rowNumb = 3 Case "XYZ Corp" rowNumb = 4 Case "UVW Corp" rowNumb = 5 Case Else MsgBox "Invalid selection" Exit Sub End Select TextBox1 = Sheets("Sheet1").Cells(rowNumb, "B") TextBox2 = Sheets("Sheet1").Cells(rowNumb, "C") TextBox3 = Sheets("Sheet1").Cells(rowNumb, "D") TextBox4 = Sheets("Sheet1").Cells(rowNumb, "E") Call Sum_TextBoxes End Sub Private Sub Sum_TextBoxes() Total = Val(TextBox1) + Val(TextBox2) + Val(TextBox3) + Val(TextBox4) End Sub -- Regards, OssieMac "amit" wrote: hi - thanks a tonne for your response. the sum value worked perfectly. regarding the second question...here's some background information... i'm building a planning model that have a set of grwth drivers for each customer....my intension is for the user to click on the grwth lever for that customer and a dialogue box pops up with the customer name at the top and then a lot of info n input fields to take the info back.... Customer Name G1 G2 ABC Corp 12 15 MSN 14 19 so although the userform i created for say "G2" for ABC Corp, need to be able to reuse that userform for "G2" for Ninemsn...... my userform refers to the cell number and the customer name decides (thru some if else vba in my userform initiate sub) the relevant info for the customer to be put into the userform. i figured the only way to solve this would be able to copy the whole user form realting to "G2" to each of the customer line....... the other way to solve this problem would be to be able to assign the userform show sub to a cell rather than a "box",,,,,,,,,that way assigning the userform to each cell would pick up the relevant customer name from the left... may be i'm asking for too much here ............but thx for your feedback for my earliar questions... kind regards, Amit. "OssieMac" wrote: You use the Val function to convert the text to numeric values. However, the event you have would only run if you make a change to the Total text box. If you place an AfterUpdate event on each text box and call a sub to sum the values, it will update each time you make a change in any of the other text boxes. Place the following code in the Forms code module:- (Do not use a sub name which is used for events.) Private Sub Sum_Values() Total= Val(TextBox1) + Val(TextBox2) + Val(TextBox3) + Val(TextBox4) End Sub Then the following events in the Forms code calls the above sub each time a text box is updated:- Private Sub TextBox1_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox2_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox3_AfterUpdate() Call Sum_Values End Sub Private Sub TextBox4_AfterUpdate() Call Sum_Values End Sub You could place the called sub in a module but you then need to use the Userform name with each Text box like the following:- Sub Sum_Values() UserForm1!Total.Value = Val(UserForm1!TextBox1) + _ Val(UserForm1!TextBox2) + Val(UserForm1!TextBox3) + _ Val(UserForm1!TextBox4) End Sub Not sure what you mean by "why am i not able to use the form i've created again in the same file?" If you close the form and Alt/F11 to open the VBA editor and then right click on the Userform name in the Project Explorer you can either select View Object or View Code. If this is not what you mean then pls get back to me with more explanation of what it is that you can't do. -- Regards, OssieMac "amit" wrote: Also Another quick question - why am i not able to use the form i've created again in the same file? i will need to be able to use the same form a few time but with different criteria or pointing to different rows in a table? "OssieMac" wrote: Sorry! I was having a Seniors moment when I said "When you create a Text Box, it creates an associated Label" that's in Access. -- Regards, OssieMac "OssieMac" wrote: Am I correct in assuming that you want all these boxes to display simultaneously? If so, then Text Boxes and Labels might do what you want. You can use Labels to display data and Text Boxes to enter data. When you create a Text Box, it creates an associated Label but if you dont want the associated Label, you can delete it. The Labels and Text Boxes can be inserted directly on worksheets or in Forms created via the VBA editor. To access Labels and Text Boxes:- Xl2007: Click the Insert button in the Controls group on the Developer ribbon. If Developer ribbon not visible, then see Help on how to display it. There are two types of controls, Forms controls and ActiveX controls. Its my opinion that the ActiveX are the preferable ones to use. Pre xl2007 the ActiveX controls are on the Toolbox toolbar. The Forms controls are on the Forms toolbar. If using ActiveX controls, you need to be in Design mode to edit the controls. This automatically turns on when the control is created but you need to turn it off when finished setting the required properties before you can use the control and turn it back on if you need to do more editing. The Design button looks like a Set Square, Ruler and Pencil. Early versions of xl did not have ActiveX controls. Not sure but I think they started in xl2000 and before that you only have Forms controls. Hope this helps and feel free to get back to me. -- Regards, OssieMac "amit" wrote: i need some abstract guidence around using dialogue boxes as an interface to both display information and then get information back from the user in the same dialogue box. potential i need to be able to display say ten boxes of informative data and then 5 boxes where the user enters some $ values. i dont need the actuall code but any directional input as to how this can be achiaved will be helpful |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
speak input box dialogue | Excel Discussion (Misc queries) | |||
Dialogue Box | Excel Discussion (Misc queries) | |||
dialogue pop up | Excel Discussion (Misc queries) | |||
pop up dialogue box | New Users to Excel | |||
Dialogue Box | Excel Programming |