Lookup highest number in column and assign the next highest to a field when saving userform

[Channce]

Hello, need some help please!

I have a userform to enter data on a table. I'm in need of having the code search column "H" to find the highest number/value and then place the next highest number/value in the cell "H" of the row that the userform is entering data to. (I hope that makes sense)

I currently don't have any field on the the userform to input that "next highest value"...I was hoping it would do it in the background so the user doesn't have to do an additional step.

I also thought maybe it can perform that lookup when the form is initialized before entering any data? and it can display that number on the user form.... I'm not sure what's easier.

Here is the code for the userform. Any input would be great!!

Private Sub cmdSaveItem_Click()
  Sheets("Item").Unprotect Password:="123456"
    Dim ctrl As Control, LastRow As Long
    LastRow = Sheets("Item").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            If ctrl.Value = "" Then
                MsgBox ("Please enter a value for " & ctrl.Name)
                ctrl.SetFocus
                Exit Sub
            End If
        End If
    Next ctrl
    Sheets("Item").Cells(LastRow + 1, 1).Resize(1, 7) = Array(txt_ItemName.Value, _
        txt_ProdCodeSKU.Value, txt_VendorSelection.Value, txt_Location, txt_MaxStock.Value, txt_ReorderLevel.Value, txt_ItemStocked.Value)
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            ctrl.Value = ""
        End If
    Next ctrl
    txt_ItemStocked = False
    Sheets("Item").Protect Password:="123456"
End Sub

 

[Channce]

I'm sorry, I should clarify...

I need to search the entire column, "H", to find whatever cell has the highest number between all of them. I then need the code to output that number + 1 and place that on the end of the row (column H) in the new data that is stored via the userform.

 

[Akuini]

I'm sorry, I should clarify...

I need to search the entire column, "H", to find whatever cell has the highest number between all of them. I then need the code to output that number + 1 and place that on the end of the row (column H) in the new data that is stored via the userform.

Here's an example:

With Sheets("Sheet1")
n = .Range("H" & .Rows.count).End(xlUp).Row
.Range("H" & n + 1) = WorksheetFunction.Max(.Range("H1:H" & n)) + 1
End With

 

[Channce]

This looks like what I need! Now I need this number to display in the field "txt_ExcelItemCode" but can't figure out the code to show when the form is initiated. This is what I have so far...

Private Sub cmd_CreateNewItem_Click()
frmCreateNewItem.Show
With Sheets("Item")
n = .Range("H" & .Rows.Count).End(xlUp).Row
.Range("H" & n + 1) = WorksheetFunction.Max(.Range("H2:H" & n)) + 1
End With

End Sub

 

[Akuini]

Is "txt_ExcelItemCode" a textboxt located in userform "frmCreateNewItem"?

 

[Channce]

Yes. its a locked cell because I don't want the user to be able to adjust this number.

 

[Akuini]

Yes. its a locked cell because I don't want the user to be able to adjust this number.

Wait, "txt_ExcelItemCode" is a cell not a textbox? And it's a locked cell?

 

[Channce]

I'm sorry... I used the wrong word. "txt_ExcelItemCode" is a text box field in the userform.