Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 28

Thread: Unique ID for new userform's entries

  1. #1

    Unique ID for new userform's entries

    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
    Last edited by VanillaSky; 09-09-2017 at 04:03 PM.

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,189
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    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?

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,189
    Location
    EDIT: Also, is it possible to make userform output work in the table?
    I don't understand
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Current userform does not work when the table is created

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,189
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    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.

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,189
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    Quote Originally Posted by SamT View Post
    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.


  10. #10
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,189
    Location
    @ anybody,

    I'm Stuck. Anybody else wanna chime in
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #11
    VBAX Regular
    Joined
    Feb 2013
    Posts
    51
    Location
    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 ?

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,189
    Location
    Sheet1 could also be called OriginationSheet, and Sheet2 could also be called DestinationSheet.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  13. #13
    Quote Originally Posted by NoSparks View Post
    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.

  14. #14
    VBAX Regular
    Joined
    Feb 2013
    Posts
    51
    Location
    Quote Originally Posted by VanillaSky View Post
    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.

  15. #15
    Any ideas so far?

  16. #16
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,857
    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 ?

  17. #17
    Quote Originally Posted by snb View Post
    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.

  18. #18
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,857
    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.

  19. #19
    Start following a course in VBA first.
    This is exactly what I'm doing after my work hours

  20. #20
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,189
    Location
    It's a Template file, Save it as an .xlsm file, and upload that one here.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •