Unique ID for new userform's entries

[VanillaSky]

Hello everyone.

I want my userform to insert unique id for every new entry starting from 1, wchich should look like this: [SOLVED]



Could you please take a look at the code below and advise how to edit it to add this feature? [SOLVED]

EDIT: Also, is it possible to make userform output work in the table?

Private Sub OutPutData()
    Dim NextRow As Range
   
     Set NextRow = Worksheets("List").Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(1, 6)
    With Me
    
        NextRow.Cells(1) = .TextBox_PI_Case
        NextRow.Cells(2) = .TextBox_Company_Name
        NextRow.Cells(3) = "NEW"
        NextRow.Cells(4) = .TextBox_RoR
        NextRow.Cells(5) = .TextBox_Comments
        NextRow.Cells(6) = Date
        
    End With
End Sub


Private Sub ClearData() 'Better = "ClearDataControls," or "Clear_ioCtrls."
    With Me
        .TextBox_PI_Case = ""
        .TextBox_Company_Name = ""
        .TextBox_RoR = ""
        .TextBox_Comments = ""
        .TextBox_PI_Case.SetFocus
    End With
End Sub


 
Sub CommandButton_Submit_Click()
     
     'check for a Name number
    If Trim(Me.TextBox_Company_Name.Value) = "" Then
        Me.TextBox_Company_Name.SetFocus
        MsgBox "Please complete the form"
        Exit Sub
    End If
     
     'copy the data to the database
    OutPutData
     
    MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
     
     'clear the data
    ClearData
     
End Sub


Private Sub CommandButton_Cancel_Click()
Unload Me
End Sub

Credits for code above goes to SamT who made it actually work up to this point, thanks again

[SamT]

I'm in a hurry, but try this

Private Sub OutPutData()
    Dim NextRow As Range
     
    Set NextRow = Worksheets("List").Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(1, 6)
    With Me
        NextRow.Cells(1).Offset(, -1) = GetNewID(NextRow.Cells(1))
        NextRow.Cells(1) = .TextBox_PI_Case
        NextRow.Cells(2) = .TextBox_Company_Name
        NextRow.Cells(3) = "NEW"
        NextRow.Cells(4) = .TextBox_RoR
        NextRow.Cells(5) = .TextBox_Comments
        NextRow.Cells(6) = Date
         
    End With
End Sub

Private Function GetNewID(Cel As Range) As Long 'Or As String. Depends on requirements
Dim Tmp
Tmp = Cel.Offset(-1, -1).Value2

If UCase(Tmp) = "ID" Then 
GetNewID = 1
ElseIf IsNumeric(Tmp) Then 
GetNewID = CLng(Tmp) + 1
End If
End Function

[VanillaSky]

Again, works perfectly. Thank you for your support.

If it's possible, please take a look at my second question:

EDIT: Also, is it possible to make userform output work in the table?

[SamT]

EDIT: Also, is it possible to make userform output work in the table?

I don't understand

[VanillaSky]

Current userform does not work when the table is created

[SamT]

I imagine...

The table should be created before the UserForm is fully coded, because the Table address is hard coded in the UserForm Code.

If the table is created after the UserForm is coded, there must be Code in the UserForm to find the table.

Please tell us all the steps a user must take when he or she wants to use the Excel workbook.

[VanillaSky]

Basically this is how the actual sheet looks like:




User clicks

which opens this userform for him:



You made the code for this one, so you know exactly what it does. After userform's job is done I column formulas count day from creation date to current time and K column creates hyperlink to another sheet where
notes for specific cases are being kept.

Up to this point everything works smoothly.

The problem starts when I want to create table on A1:I101 (there is hidden H column with today() formula) to enable filtering. So basicaly what I want is to keep features above but working in a table.

[SamT]

You made the code for this one, so you know exactly what it does.

That was many Projects ago, and I don't keep copies of all the code I write.

Maybe Something like this

Sub TransferValuesToSheet2()
With Sheets("Sheet1").Range("A1").CurrentRegion
.Resize(.Rows.Count, 9).Copy
End With

With Sheets("Sheet2").Range("A1")
.CurrentRegion.Rows.Delete
DoEvents 'Prevents Timing errors with large Ranges
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
DoEvents
.PasteSpecial Paste:=xlPasteFormats
DoEvents
.PasteSpecial Paste:=xlPasteColumnWidths
End With
DoEvents

Application.CutCopyMode = False
End Sub

[VanillaSky]

That was many Projects ago, and I don't keep copies of all the code I write.

Well, still I'm pretty sure you are able to easily imagine how the code is going to work

Anyways, suprisingly the last one doesn't work. Userform shows that data has been added, however nothing appears in the table.

[SamT]

@ anybody,

I'm Stuck. Anybody else wanna chime in

[NoSparks]

What sheet is pictured in post #9 ?

This question appears related to the OP's original question from yesterday where the posted code was for "Sheet1"
The OP's code posted in this question is for sheet "List".
Sam's suggestion of post #8, copies from "Sheet1" to "Sheet2".

Appears to me there could be three similar sheets.
Is the one in post 9 the one the code pertains to ?

[SamT]

Sheet1 could also be called OriginationSheet, and Sheet2 could also be called DestinationSheet.

[VanillaSky]

What sheet is pictured in post #9 ?

Sheet's title is "List" but I don't think it's the problem as I changed the names in the code accordingly.

[NoSparks]

Well, still I'm pretty sure you are able to easily imagine how the code is going to work

Guess at some point in time you'll be attaching a workbook 'cause the above statement just ain't so.

[VanillaSky]

Any ideas so far?

[snb]

What did you try yourself (any ideas yourself ?) ?

Why don't you post the workbook here in this forum ?

You don't need an ID if the PI-case is unique.

Where can we find RoR in the table ?

[VanillaSky]

What did you try yourself (any ideas yourself ?) ?

I started working with VBA few days ago that's why finding solutions takes me much more time.
Here my current approach, still not working tho:

Private Sub OutPutData()
    Dim oLo As ListObject
    Dim oNewRow As ListRow
    Dim NewID As Long
 
' use first table on sheet ("or specify name")
Set oLo = Worksheets("List").ListObjects(1)
 
' NewID
If oLo.ListRows.Count = 0 Then
    NewID = 1
Else
    NewID = Application.Max(oLo.ListColumns(1).DataBodyRange) + 1
End If
 
' insert table row and data
Set oNewRow = oLo.ListRows.Add(AlwaysInsert:=True)
    With oNewRow
        .Range.Cells(1) = NewID
        .Range.Cells(2) = TextBox_PI_Case
        .Range.Cells(3) = TextBox_Company_Name
        .Range.Cells(4) = "NEW"
        .Range.Cells(5) = TextBox_RoR
        .Range.Cells(6) = TextBox_Comments
        .Range.Cells(7) = Date
    End With
End Sub

Why don't you post the workbook here in this forum ?

Good idea! For some reason it won't upload here, so please find the link below:
https://drive.google.com/open?id=0Bx...FB1UURRWkJmWDg

You don't need an ID if the PI-case is unique.

Still I want to keep the ID.

Where can we find RoR in the table ?

Reason of Rejection - E column.

[snb]

You can upload files in this forum: .xls, xlsx, xlsm, xlsb.
I don't load files form other sources.

Start following a course in VBA first, otherwise we can't even explain something to you.

I can reassure you, VBA is a language like any other (English, Polish, Spanish); it takes the same amount of time/effort to learn to use it properly.

[VanillaSky]

Start following a course in VBA first.

This is exactly what I'm doing after my work hours

[SamT]

It's a Template file, Save it as an .xlsm file, and upload that one here.