Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
update excel form macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
update excel form macro
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
update excel form macro
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 "Patrick Molloy" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
update excel form macro
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 sam wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update a word form from Excel - tips? | Excel Programming | |||
thru form update data store in excel | Excel Worksheet Functions | |||
Auto Update a Database from an Excel Form. | Excel Programming | |||
Update Adobe form from Excel | Excel Programming | |||
Update custom outlook form from within Excel | Excel Programming |