Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code/Messages for User Control
Cells A1, A2, and A3 are provided for users to enter text
describing an action. A1 is always filled in, and A2 and A3 may, or may not, be blank. In cells B1, B2, and B3 users enter a percentage value associated with the "A" value. Therefore, the values in the "B" cells must sum to 100%. Three possibilities: A B 1 Do this action 100% 1 Do this action 75% 2 Do something different 25% 1 Do this action 65% 2 Do something different 25% 3 Do yet another thing 10% Can someone help me with code and messages to force this rigor? Thanks, Jerry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code/Messages for User Control
Phil,
As in reply my to your "weighting" sub post A form with three text boxes. Hide the first and second boxes so as to force the user to enter the third box first. Check it's value is = 0 and <100 Show the second box and get the user to enter the second value. Check if this is = 0 and =< (1 - third value) Show the first box. It will be 100 - second value - third value. (could be 100 - 0 - 0) There's no need to get the user to input anything in this box as you can calculate it. HTH Henry "Phil Hageman" wrote in message ... Cells A1, A2, and A3 are provided for users to enter text describing an action. A1 is always filled in, and A2 and A3 may, or may not, be blank. In cells B1, B2, and B3 users enter a percentage value associated with the "A" value. Therefore, the values in the "B" cells must sum to 100%. Three possibilities: A B 1 Do this action 100% 1 Do this action 75% 2 Do something different 25% 1 Do this action 65% 2 Do something different 25% 3 Do yet another thing 10% Can someone help me with code and messages to force this rigor? Thanks, Jerry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code/Messages for User Control
Henry,
Thanks for your reply Henry, but don't understand what you are trying to do here. The workbook/worksheets involved here are in place, and I'm trying to modify them, as explained. Can you explain further what "A form...boxes." means? I'm sure you have my solution here, but I need more instruction to implement it. Thanks, Phil -----Original Message----- Phil, As in reply my to your "weighting" sub post A form with three text boxes. Hide the first and second boxes so as to force the user to enter the third box first. Check it's value is = 0 and <100 Show the second box and get the user to enter the second value. Check if this is = 0 and =< (1 - third value) Show the first box. It will be 100 - second value - third value. (could be 100 - 0 - 0) There's no need to get the user to input anything in this box as you can calculate it. HTH Henry "Phil Hageman" wrote in message ... Cells A1, A2, and A3 are provided for users to enter text describing an action. A1 is always filled in, and A2 and A3 may, or may not, be blank. In cells B1, B2, and B3 users enter a percentage value associated with the "A" value. Therefore, the values in the "B" cells must sum to 100%. Three possibilities: A B 1 Do this action 100% 1 Do this action 75% 2 Do something different 25% 1 Do this action 65% 2 Do something different 25% 3 Do yet another thing 10% Can someone help me with code and messages to force this rigor? Thanks, Jerry . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code/Messages for User Control
Phil,
Sorry. I had assumed that you were familiar with VBA, seeing that you asked for code and this is a programming NG. Open your workbook, press Alt + F11 to open the VBA environment.. Insert a userform. Put on the form two text boxes, a command button and a label (all from the toolbox). In the properties for the label, the command button and the second text box, go to Visible property and set it to false. Double click the first textbox and copy and paste this into the code window on the right. Private Sub TextBox1_AfterUpdate() If TextBox1.Value < 0 Or _ TextBox1.Value =100 Then MsgBox ("Entry must be zero or a positive number less than 100") Exit Sub End If Sheets("MySheet").Range("B3").Value = TextBox1.Value TextBox2.Visible = True End Sub Private Sub TextBox2_AfterUpdate() If TextBox2.Value < 0 Or _ TextBox2.Value = (100 - TextBox1.Value) Then MsgBox ("Entry must be zero or a positive number less than " _ & (100 - TextBox1.Value)) Exit Sub End If Sheets("MySheet").Range("B2").Value = TextBox2.Value CommandButton1,Visible = True Label1.Visible = True Label1.Caption = (100 - TextBox1.Value - TextBox2.Value) End Sub Private Sub CommandButton1_Click() Sheets("MySheet").Range("B1").Value = (100 - TextBox1.Value - TextBox2.Value) Unload Me End Sub Change MySheet (in 3 places) to the name (Tab) of Your sheet Under Microsoft Excel Objects in the Project explorer pane, double click the worksheet that the form is to appear in and copy and paste this into the code window on the right. Private Sub Worksheet_Activate() UserForm1.Show End Sub Save & exit. Reopen the workbook and when you access the sheet, the form will appear. The first textbox is only thing on the form that is visible initially. This is the one for the last item (B3). Any entry in there will be checked for range. Any entry of less than 0 or 100 or more will be rejected. A correct entry will result in the second textbox becoming visible. This is the one for the middle item (B2). Again, any entry in there will be checked for range. Any entry of less than 0 or (100 - the value in the first textbox) or more will be rejected. A correct entry will result in the label and command button becoming visible. The label caption will be the value of (100 - the value in the first textbox - the value in the second textbox) I've made this a label (not a text box) so the user cannot change the value in it. Clicking on the command button will put the value of the caption in B1 and close the form. Once you've got it working, you can add labels to your form telling the user what is required in each textbox and you can change the caption of the command button to something like OK or Exit. You can also change the colours on the form, textboxes, labels and command button. In modern parlance "Sex it up" HTH Henry "Phil Hageman" wrote in message ... Henry, Thanks for your reply Henry, but don't understand what you are trying to do here. The workbook/worksheets involved here are in place, and I'm trying to modify them, as explained. Can you explain further what "A form...boxes." means? I'm sure you have my solution here, but I need more instruction to implement it. Thanks, Phil -----Original Message----- Phil, As in reply my to your "weighting" sub post A form with three text boxes. Hide the first and second boxes so as to force the user to enter the third box first. Check it's value is = 0 and <100 Show the second box and get the user to enter the second value. Check if this is = 0 and =< (1 - third value) Show the first box. It will be 100 - second value - third value. (could be 100 - 0 - 0) There's no need to get the user to input anything in this box as you can calculate it. HTH Henry "Phil Hageman" wrote in message ... Cells A1, A2, and A3 are provided for users to enter text describing an action. A1 is always filled in, and A2 and A3 may, or may not, be blank. In cells B1, B2, and B3 users enter a percentage value associated with the "A" value. Therefore, the values in the "B" cells must sum to 100%. Three possibilities: A B 1 Do this action 100% 1 Do this action 75% 2 Do something different 25% 1 Do this action 65% 2 Do something different 25% 3 Do yet another thing 10% Can someone help me with code and messages to force this rigor? Thanks, Jerry . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect User from edit Control Toolbox (Design Mode) | Excel Discussion (Misc queries) | |||
want user to input informartion to control mnacro actions | Excel Discussion (Misc queries) | |||
Giving out code in messages | Excel Discussion (Misc queries) | |||
How do I control to which cells a user adds data? | Excel Worksheet Functions | |||
Control code behavior with userform ? | Excel Programming |