Code/Messages for User Control

  • Thread starter Thread starter Phil Hageman
  • Start date Start date
P

Phil Hageman

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