View Full Version : Unique ID for new userform's entries
VanillaSky
09-09-2017, 12:51 PM
Hello everyone.
I want my userform to insert unique id for every new entry starting from 1, wchich should look like this: [SOLVED]
https://i.gyazo.com/a2098645846d79f5a8596cb47f8097f7.png
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 :)
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
09-09-2017, 04:07 PM
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?
EDIT: Also, is it possible to make userform output work in the table?
I don't understand
VanillaSky
09-09-2017, 04:42 PM
Current userform does not work when the table is created
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
09-10-2017, 09:26 AM
Basically this is how the actual sheet looks like:
https://i.gyazo.com/a254523cf7bd04b833244dd670286b55.png
User clicks
https://i.gyazo.com/1523dd00bcb6e11da5c566c93302c944.png which opens this userform for him:
https://i.gyazo.com/9a4d45deaacc82c9ef5a415fdbb4989c.png
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.
You made the code for this one, so you know exactly what it does.:funnyashe:funnyashe 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
09-10-2017, 01:42 PM
:funnyashe:funnyashe 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 :D
Anyways, suprisingly the last one doesn't work. Userform shows that data has been added, however nothing appears in the table.
https://i.gyazo.com/13c81e10affa1aa33477343b4a830984.png
@ anybody,
I'm Stuck. Anybody else wanna chime in
NoSparks
09-10-2017, 05:07 PM
What sheet is pictured in post #9 ?
This question appears related to the OP's original question (http://www.vbaexpress.com/forum/showthread.php?60675-Userform-output-range) 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 ?
Sheet1 could also be called OriginationSheet, and Sheet2 could also be called DestinationSheet.
VanillaSky
09-10-2017, 11:28 PM
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
09-11-2017, 06:39 AM
Well, still I'm pretty sure you are able to easily imagine how the code is going to work :D
Guess at some point in time you'll be attaching a workbook 'cause the above statement just ain't so.
VanillaSky
09-12-2017, 01:37 AM
Any ideas so far? :dunno
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
09-12-2017, 02:51 AM
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=0BxPL2nvm5F5BSFB1UURRWkJmWDg
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.
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
09-12-2017, 03:22 AM
Start following a course in VBA first.
This is exactly what I'm doing after my work hours :)
It's a Template file, Save it as an .xlsm file, and upload that one here.
VanillaSky
09-12-2017, 08:13 AM
It's a Template file, Save it as an .xlsm file, and upload that one here.
File is too large
If you remove all sheets that bear no relevance to your question (leave only the sheet you posted as a picture) I can hardly imagine the file should be too large.
VanillaSky
09-12-2017, 08:59 AM
If you remove all sheets that bear no relevance to your question (leave only the sheet you posted as a picture) I can hardly imagine the file should be too large.
It's still too large, even after deleting all unnecessary objects, conditional formating etc.
Is it possible that the code itself is so heavy?
NoSparks
09-12-2017, 09:19 AM
@ VanillaSky
When I add a new sheet to your workbook.
Copy just the first 11 rows of the "List" sheet to this new sheet.
Delete all sheets except the new sheet.
Rename the new sheet "List"
Save the workbook with a different name.
It's down from over 2.7 meg to about 21 KB .
VanillaSky
09-12-2017, 09:54 AM
@ VanillaSky
When I add a new sheet to your workbook.
Copy just the first 11 rows of the "List" sheet to this new sheet.
Delete all sheets except the new sheet.
Rename the new sheet "List"
Save the workbook with a different name.
It's down from over 2.7 meg to about 21 KB .
Weird, but actually works... :dunno
NoSparks
09-12-2017, 04:50 PM
Regarding your data input and the picture in post #9.....
unhide the rows of that sheet and you'll see what happened.
VanillaSky
09-18-2017, 12:09 AM
Regarding your data input and the picture in post #9.....
unhide the rows of that sheet and you'll see what happened.
Good point :rofl:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.