how increment number int textboxes based on filling comboboxes on userfrom

[Abdo]

Hi

I want way to increment number for column 1 on userform under lable(ITEM) based on filling comboboxes for column 2 under lable(BATCH)

the textboxes in column ITEM(textbox1:textbox12) but comboboxes for column 2 under lable(BATCH) their numbers is not consecutively

will be increment up to numbers 4 when move next row (combobox1,5,9,13.....)

first

result

any suggestion guys?

 

[ouvay]

Try this formula in the ITEM column (assuming that the instance is in cell A2)

=IF(B2="", "", ROW()-1)

 

[ouvay]

If you are looking for a VBA solution, try this.

This will work as long as there is no break between your BATCH entries - It auto refreshes and clears the cell if data is erased so I think it might prove to be useful

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = -1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Rng.Count() + Rng.Offset(-1, xOffsetColumn).Value
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

If you are looking for a solution which allows for breaks in data entries please specify

 

[Abdo]

thanks for your effort , but you misunderstood my question , your code deals with the sheet . in my case should deal with the userform directly without depends on sheet .