PDA

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 :)

SamT
09-09-2017, 03:43 PM
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?

SamT
09-09-2017, 04:28 PM
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

SamT
09-09-2017, 05:29 PM
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.

SamT
09-10-2017, 10:29 AM
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

SamT
09-10-2017, 04:14 PM
@ 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 ?

SamT
09-10-2017, 08:00 PM
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

snb
09-12-2017, 02:26 AM
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.

snb
09-12-2017, 03:17 AM
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 :)

SamT
09-12-2017, 07:48 AM
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

snb
09-12-2017, 08:25 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.

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?

snb
09-12-2017, 09:06 AM
How many sheets ?

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: