Input Dialogue Box

[amit]

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

 

[OssieMac]

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 don’t 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. It’s 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.

 

[OssieMac]

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.

 

[amit]

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.

 

[amit]

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]

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.

 

[amit]

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]

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

 

[amit]

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