Link to home
Create AccountLog in
Avatar of maverick0728
maverick0728Flag for United States of America

asked on

Excel textbox to display next value from evaluating worksheet values

Here's what is going on.  I have a bill of material that looks like this is a worksheet.  I have a combo box that is displaying all "ASSY" and "SUB" parts.  Depending on which ASSY or SUB they pick an evaluation needs to occur to display the next available item number.

Here is the example (item : type)
0.0 : ASSY
1.0 : MAKE
2.0 : BUY
3.0 : SUB
3.1 : MAKE
3.2 : MAKE
3.3 : SUB
3.3.1 : MAKE
3.3.2 : BUY
3.3.3 : BUY
3.4 : MAKE
3.5 : BUY
4.0 : MAKE
5.0 : SUB
5.1 : MAKE
5.2 : BUY

The combo box would display:
0.0 : ASSY
3.0 : SUB
3.3 : SUB
5.0 : SUB

I need to return a value to a text box that would be the next available item depending on what they pick in the combo box.
if they pick:
0.0 : ASSY - IF PICK THIS ONE, NEXT VALUE WOULD BE 6.0
3.0 : SUB - IF PICK THIS ONE, NEXT VALUE WOULD BE 3.6
3.3 : SUB - IF PICK THIS ONE, NEXT VALUE WOULD BE 3.3.4
5.0 : SUB - IF PICK THIS ONE, NEXT VALUE WOULD BE 5.3

Please update the example.xlsm file and have the textbox1 on the userform show the next value.
EXAMPLE.xlsm
Avatar of Rgonzo1971
Rgonzo1971

HI,

pls try
Private Sub ComboBox1_change()
TextBox1.Text = Application.VLookup(ComboBox1.Text, Range("a2:C17"), 3)
End Sub

Open in new window

Regards
EE20150629.xlsm
Avatar of maverick0728

ASKER

Rgonzo1971,
Thanks for the comment.  I don't want to display what is in column C.  That is just text saying what the next value is that should be determined and put in textbox1.
Martin Liss - awesome thanks.  It's working well.  Only issue is when there are more levels deep.  I added more examples to the spreadsheet.  Right now it fails on:
sub 3.3.4 -> should produce 3.3.4.1
sub 3.3.5 -> should produce 3.3.5.1
sub 11.2 -> should produce 11.2.1
EXAMPLE-UPDATED.xlsm
Is there a maximum number of levels?
No there isn't a max number of levels.  Typically thought I've never seen our bill of materials go past 13 levels deep.
Martin,
what would it take to go 10 levels deep?  I would be fine with that.
In the new workbook if I selected 0.0, should the textbox show 12.0?
yes in the example-updated.xlsm, if you select 0.0 the next level would be 12.0
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I am getting an error on LowOrdersMatch(

compile error:
Sub or Function not defined
I'm sorry. I forgot to post it. Add it as a new function in the userform's code.

Private Function LowOrdersMatch(C As Variant, S As Variant) As Boolean
Dim lngLevel As Long

For lngLevel = 0 To UBound(C) - 1
    If C(lngLevel) <> S(lngLevel) Then
        Exit Function
    End If
Next
LowOrdersMatch = True

End Function

Open in new window

Martin Liss,
Awesome work.  It's working and will save us alot of time.

Thanks for all your great help in getting this complicated example (at least to me) working.  Your the best I've encountered on EE for Excel.
You're welcome and I'm glad I was able to help. And thanks for the nice compliment!

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015