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
maverick0728Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
0
maverick0728Author Commented:
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.
0
Martin LissOlder than dirtCommented:
Try this.
EXAMPLE.xlsm
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

maverick0728Author Commented:
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
0
Martin LissOlder than dirtCommented:
Is there a maximum number of levels?
0
maverick0728Author Commented:
No there isn't a max number of levels.  Typically thought I've never seen our bill of materials go past 13 levels deep.
0
maverick0728Author Commented:
Martin,
what would it take to go 10 levels deep?  I would be fine with that.
0
Martin LissOlder than dirtCommented:
In the new workbook if I selected 0.0, should the textbox show 12.0?
0
maverick0728Author Commented:
yes in the example-updated.xlsm, if you select 0.0 the next level would be 12.0
0
Martin LissOlder than dirtCommented:
I think this will do any number of levels. Note that my result for 3.3.4 is 3.3.4.3 and not 3.3.4.1

Private Sub ComboBox1_Change()
Dim strSelected() As String
Dim strChoices() As String
Dim lngRow As Long
Dim intHigh As Integer
Dim lngIndex As Long

strSelected = Split(ComboBox1.Text, ".")
With ActiveSheet
Select Case True
        Case ComboBox1.Text = "0.0"
            ' For item "0.0"
            For lngRow = 2 To .UsedRange.Rows.Count
                strChoices = Split(.Cells(lngRow, 1), ".")
                If UBound(strChoices) = UBound(strSelected) Then
                    If strChoices(0) > intHigh Then
                        intHigh = strChoices(0)
                    End If
                End If
            Next
            TextBox1.Text = intHigh + 1 & ".0"
        Case UBound(strSelected) = 1 And strSelected(UBound(strSelected)) = "0"
            ' For items with two parts ending in "0" with the exception of "0.0"
            For lngRow = 2 To .UsedRange.Rows.Count
                strChoices = Split(.Cells(lngRow, 1), ".")
                If UBound(strChoices) = UBound(strSelected) Then
                    If strChoices(0) = strSelected(0) Then
                        If strChoices(UBound(strChoices)) > intHigh Then
                            intHigh = strChoices(UBound(strChoices))
                        End If
                    End If
                End If
            Next
            TextBox1.Text = strSelected(0) & "." & intHigh + 1
        Case Else
            ' For all other items
            For lngRow = 2 To .UsedRange.Rows.Count
                strChoices = Split(.Cells(lngRow, 1), ".")
                If UBound(strChoices) - 1 = UBound(strSelected) Then
                    If LowOrdersMatch(strChoices, strSelected) Then
                        If strChoices(UBound(strChoices)) > intHigh Then
                            intHigh = strChoices(UBound(strChoices))
                        End If
                    End If
                End If
            Next
            TextBox1.Text = ""
            For lngIndex = 0 To UBound(strSelected)
                TextBox1.Text = TextBox1.Text & strSelected(lngIndex) & "."
            Next
            TextBox1.Text = TextBox1.Text & intHigh + 1
    End Select
End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maverick0728Author Commented:
I am getting an error on LowOrdersMatch(

compile error:
Sub or Function not defined
0
Martin LissOlder than dirtCommented:
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

0
maverick0728Author Commented:
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.
0
Martin LissOlder than dirtCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.