update excel form macro

[sam]

I have an excel form with existing macros that I need to update.

The idea is to auto populate one field based on values selected in two other
fields.

Lets say there are 3 fields A, B, C with specific values listed as a drop
down menu.

A: Hello, What, Going, There
B: Did, Jolly, Cool, Tomorrow
C: Discrepancy

so lets say for eg, If Hello and Did are selected, C will remain blank

If Hello, Jolly are selected, C will populate Discrepancy
(assume Hello has lower rating then Jolly)

If What and Did are selected, C will remain blank.

The Idea is, values in A and B column are text values and they have ratings.

If value in A has lower rating then value in B then populate 'discrepancy'
in C
If value in A has higher rating then value in B then no value is populated
in C
If A and B have the same values then no value is populated in C

And so on.. I hope it is clear.

Thanks in Advance

 

[Patrick Molloy]

got it. C is blank unless A's rating is less than B's rating

are these ratings in a table?

so i have a table on Sheet1 range named "Ratings" with three columns - the
first is the word and the second column is the rating and the thirs the
which of the three combos it belongs to

Hello, 1, A
What, 3, A
Going, 5, A
There. 7, A
Did, 2, B
Jolly, 4, B
Cool, 6, B
Tomorrow, 8, B

---my values based on random sort

add two combo boxes and a text box to a userform. leave default names

copy/paste this code
Option Explicit
Private Sub UserForm_Initialize()
Dim cell As Range
ComboBox1.ColumnCount = 2
ComboBox1.BoundColumn = 2
ComboBox2.ColumnCount = 2
ComboBox2.BoundColumn = 2
For Each cell In Range("Ratings").Columns(1).Cells
Select Case cell.Offset(, 2)
Case "A"
With ComboBox1
.AddItem cell.Value
.List(.ListCount - 1, 1) = cell.Offset(, 1)
End With
Case "B"
With ComboBox2
.AddItem cell.Value
.List(.ListCount - 1, 1) = cell.Offset(, 1)
End With
End Select
Next

End Sub
Private Sub ComboBox1_Change()
checkValues
End Sub

Private Sub ComboBox2_Change()
checkValues
End Sub
Sub checkValues()
TextBox1.Text = ""
If ComboBox1 < ComboBox2 Then
TextBox1.Text = "Discrepency"
End If
End Sub

 

[sam]

Hi Patrick

Thanks a LOT for the help. There are no such ratings given to the values, is
there any way to compare without any ratings to the text strings? Also the
text values in A and B are same.. some thing like this..

A is a dropdown with following values
compile, edit, audit

B is a dropdown and has the same values as A
compile, edit, audit

C is a text box that auto populates with 'Descripency' depending on what is
selected for A and B.

What I am thinking should be done is:
Compare the text strings in A and B using StrComp() function,
It will return a value -1,0,1
Then write an If statement to populate the value in C.

But, How does excel compare the text values in A and B?
For eg, I want Discrepancy to be populated in C if audit is selected in B
and edit is selected in A. How will excel know this? I cannot manualy write
down what text value preeceds what as there are a LOT of values.

I hope I made it clear.

Thanks in Advance

 

[keiji kounoike]

If the field A is the Combobox named as ComboBox1, the field B named as
ComboBox2 and the field C named as ComboBox3.
Something like this is what you are looking for?

Private Sub ComboBox3_Enter()
If ComboBox1.Value <> ComboBox2.Value Then
Me.ComboBox3.List = Array("Discrepancy")
Else
Me.ComboBox3.Clear
End If
End Sub

As you said, you could use StrComp Function to compare two texts. In
that case, your code would be like

If StrComp(ComboBox1.Value, ComboBox2.Value) <> 0 Then

instead of

If ComboBox1.Value <> ComboBox2.Value Then

Keiji