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 |
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 |