Auto Number with VBA

[excelenergy]

Hello,

I have a userform (I provided the code that controls the userform below). What I essintially want to happen, is the userform inputs its data into the excel sheet. Right now there is a field in the userform to type in the ID of the entry. I would like Excel to automatically number these (Ie: Check to see if there is an entry 1, if not, Excel automatically assigns 001 to the entry, then, when the next user makes an entry, excel will automatically number 002 and so on and so fourth.

I tried doing the auto numbering myself, but couldn't get it to work, does anyone know how to do this? Thanksss

Option Explicit
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("View Lessons")
'''find  first empty row in database
''iRow = ws.Cells(Rows.Count, 1) _
''  .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a part number

If Trim(Me.txtPart.Value) = "" Then
  Me.txtPart.SetFocus
  MsgBox "Please enter a part number"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value

ws.Cells(iRow, 5).Value = Me.txtSdate.Value
ws.Cells(iRow, 6).Value = Me.txtldescription.Value
ws.Cells(iRow, 7).Value = Me.txtcauselesson.Value
ws.Cells(iRow, 8).Value = Me.txtlearned.Value

ws.Cells(iRow, 9).Value = Me.txtfirst.Value
ws.Cells(iRow, 10).Value = Me.txtlast.Value
ws.Cells(iRow, 11).Value = Me.txtemail.Value
ws.Cells(iRow, 12).Value = Me.txtphone.Value
ws.Cells(iRow, 13).Value = Me.txtBU.Value
ws.Cells(iRow, 14).Value = Me.txtBCat.Value
ws.Cells(iRow, 15).Value = Me.txtBSub.Value
ws.Cells(iRow, 16).Value = Me.txtlocation.Value
ws.Cells(iRow, 17).Value = Me.txtrisk.Value
ws.Cells(iRow, 18).Value = Me.txtattach.Value
ws.Cells(iRow, 19).Value = Me.txtophase.Value
ws.Cells(iRow, 20).Value = Me.txtphase.Value
ws.Cells(iRow, 21).Value = Me.txtaddition.Value
ws.Cells(iRow, 22).Value = Me.txtlessons.Value
ws.Cells(iRow, 23).Value = Me.txtabc.Value
ws.Cells(iRow, 24).Value = Me.txtkeywords.Value
'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtSdate.Value = ""
Me.txtldescription.Value = ""
Me.txtcauselesson.Value = ""
Me.txtlearned.Value = ""
Me.txtfirst.Value = ""
Me.txtlast.Value = ""
Me.txtemail.Value = ""
Me.txtphone.Value = ""
Me.txtBU.Value = ""
Me.txtBCat.Value = ""
Me.txtBSub.Value = ""
Me.txtlocation.Value = ""
Me.txtrisk.Value = ""
Me.txtattach.Value = ""
Me.txtophase.Value = ""
Me.txtphase.Value = ""
Me.txtaddition.Value = ""
Me.txtlessons.Value = ""
Me.txtabc.Value = ""
Me.txtkeywords.Value = ""
Me.txtPart.SetFocus
End Sub

 

[excelenergy]

Fantastic, that worked great...got everything I needed.

Looking into some active directory options now. haha, this thing is going to be complex when Im done even though I hope it isnt.


Is there a way (and Im seeing what I can find online right now to) to get Excel to return the name of the user logged onto the system? It doesn't matter to me if Excel uses active directory to get the information or whereever. Ive just never done anything like this, so Im trying to see if I can get it to happen. If you have any tips though on this, do let me know...Have you ever done it, or do you (or anyone else) know if thats even possible or am I on a wild goose chase here?

change:
txtSdate.Value = ""
to:
txtSdate.Value = Date

and add the same line to the end of UserForm_Initialize.

 

[excelenergy]

Hey There,

Great news, found this online:
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function

Cant seem to make it work in my script under the me.txtfirst yet

Fantastic, that worked great...got everything I needed.

Looking into some active directory options now. haha, this thing is going to be complex when Im done even though I hope it isnt.


Is there a way (and Im seeing what I can find online right now to) to get Excel to return the name of the user logged onto the system? It doesn't matter to me if Excel uses active directory to get the information or whereever. Ive just never done anything like this, so Im trying to see if I can get it to happen. If you have any tips though on this, do let me know...Have you ever done it, or do you (or anyone else) know if thats even possible or am I on a wild goose chase here?

 

[p45cal]

Hey There,

Great news, found this online:
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function

Cant seem to make it work in my script under the me.txtfirst yet

When you have a function (like above) you must assign what you want the function to return to a variable with the same name as the function, so either:

[FONT=Courier New][SIZE=2][COLOR=#0000ff]Function UserNameWindows() As String
    UserName[B]Windows[/B] = Environ("USERNAME")
[/COLOR][/SIZE][/FONT][SIZE=2][FONT=Courier New][COLOR=#0000ff]End Function[/COLOR][/FONT][/SIZE]

or:

[FONT=Courier New][SIZE=2][COLOR=#0000ff]Function [B]UserName[/B]() As String
    UserName = Environ("USERNAME")
[/COLOR][/SIZE][/FONT][SIZE=2][FONT=Courier New][COLOR=#0000ff]End Function[/COLOR][/FONT][/SIZE]

 

[excelenergy]

Fantastic, it works, I haven't embedded it into the spreadsheet yet but will eventually.

I have another question relating to this topic. Let me just briefly explain what Ive done and draw on your wisdom!
So I added some rows, which are apart of the heading across the top of the sheet, without getting into to much detail, I basically need the sheet to start making the entries at Row 4, instead of row 1. Allow me to explain why, just incase this is useful information, lets say my headigns are these:

ID | Description | Risk |

Now under Risk there are three titles: -----------People--------Process------------Environment
And under each of those titles: -----Severity | Likelihood| Severity | Likelihood | Severity | Liklihood


Basically I have combox boxes that will fill in the severity and liklihood - so thats not an issue. The issue is, just ensuring the form will start entering at row 4, and not Row 1. I tried editing this line of code:

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

Didnt seem to do anything. If you need the full code, I pasted it below:

Option Explicit
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("View Lessons")
'''find  first empty row in database
''iRow = ws.Cells(Rows.Count, 1) .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a part number
If Trim(Me.txtPart.Value) = "" Then
  Me.txtPart.SetFocus
  MsgBox "Please enter an ID Number; one will be suggested…"
  txtPart.Value = Format(Application.Max(ws.Range("A:A")) + 1, "000")
  Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = txtPart.Value
ws.Cells(iRow, 1).NumberFormat = "000"
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value
ws.Cells(iRow, 5).Value = Me.txtSdate.Value
ws.Cells(iRow, 6).Value = Me.txtldescription.Value
ws.Cells(iRow, 7).Value = Me.txtcauselesson.Value
ws.Cells(iRow, 8).Value = Me.txtlearned.Value
ws.Cells(iRow, 9).Value = Me.txtfirst.Value
ws.Cells(iRow, 10).Value = Me.txtlast.Value
ws.Cells(iRow, 11).Value = Me.txtemail.Value
ws.Cells(iRow, 12).Value = Me.txtphone.Value
ws.Cells(iRow, 13).Value = Me.ComboBox2.Value
ws.Cells(iRow, 14).Value = Me.txtBCat.Value
ws.Cells(iRow, 15).Value = Me.txtBSub.Value
ws.Cells(iRow, 16).Value = Me.ComboBox1.Value
ws.Cells(iRow, 17).Value = Me.ComboBox3.Value
ws.Cells(iRow, 18).Value = Me.txtattach.Value
ws.Cells(iRow, 19).Value = Me.txtophase.Value
ws.Cells(iRow, 21).Value = Me.txtaddition.Value
ws.Cells(iRow, 22).Value = Me.txtlessons.Value
ws.Cells(iRow, 23).Value = Me.txtabc.Value
ws.Cells(iRow, 24).Value = Me.txtkeywords.Value
'clear the data
txtPart.Value = Format(Application.Max(ws.Range("A:A")) + 1, "000")
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtSdate.Value = Date
Me.txtldescription.Value = ""
Me.txtcauselesson.Value = ""
Me.txtlearned.Value = ""
Me.txtfirst.Value = ""
Me.txtlast.Value = ""
Me.txtemail.Value = ""
Me.txtphone.Value = ""
Me.ComboBox2.Value = ""
Me.txtBCat.Value = ""
Me.txtBSub.Value = ""
Me.ComboBox1.Value = ""
Me.ComboBox3.Value = ""
Me.txtattach.Value = ""
Me.txtophase.Value = ""
Me.txtaddition.Value = ""
Me.txtlessons.Value = ""
Me.txtabc.Value = ""
Me.txtkeywords.Value = ""
Me.txtLoc.SetFocus
End Sub
Private Sub UserForm_Initialize()
txtPart.Value = Format(Application.Max(Sheets("View Lessons").Range("A:A")) + 1, "000")
txtSdate.Value = Date
End Sub
Private Sub cmdClose_Click()
  Unload Me
End Sub

When you have a function (like above) you must assign what you want the function to return to a variable with the same name as the function, so either:

[FONT=Courier New][SIZE=2][COLOR=#0000ff]Function UserNameWindows() As String
    UserName[B]Windows[/B] = Environ("USERNAME")
[/COLOR][/SIZE][/FONT][SIZE=2][FONT=Courier New][COLOR=#0000ff]End Function[/COLOR][/FONT][/SIZE]

or:

[FONT=Courier New][SIZE=2][COLOR=#0000ff]Function [B]UserName[/B]() As String
    UserName = Environ("USERNAME")
[/COLOR][/SIZE][/FONT][SIZE=2][FONT=Courier New][COLOR=#0000ff]End Function[/COLOR][/FONT][/SIZE]

 

[excelenergy]

Sorry my above post, posted twice, so I just deleted it and this is the result.

 

[excelenergy]

You know,

I was just thinking, I asked the wrong question. In the code, Comboxbox3 is what holds the categories People, Process, Environment. What Im wondering is if Excel can enter the entries on the spreadsheet based on those categories. So hypothetically lets say, on the userform someone choses "Environment" category > then the next thing they have to select is the severity level.

That information needs to be entered under the Environment column > in the "Severity" sub category.

Or lets say someone chooses the "People" category the next item they would enter is the "Severity" sub category - so Excel would have to input the information under the "People" heading, as opposed to the above option which the user selected Environment.

Hopefully that makes sense. Im wondering if there is a way to get Excel to input information into the sheet based on which category the user selects. Combox3 holds the main categories, people, process, environment.

 

[p45cal]

At the moment you're populating a table and it seems that it would provide a good basis for a pivot table which would allow you to summarise the data very easily, very quickly and very flexibly. I would leave it as it is.

In any event, there just isn't enough information on how your sheet is laid out to give you a good answer; a version of your workbook on the interweb somewhere and linked to here would allow a better answer.

Your previous question about starting at row 4 instead of row 1 should need no change to the code, which identifies the last used row on the sheet.

 

[excelenergy]

Hey,

Thanks for your response, you are definitely a big help. Like you mentioned Im using a user form to populate an Excel Sheet. Thing is, the sheet where the form puts the information - your average users dont have access to, only me and a manager.

Previously this was set up under a pivot table, the boss now wants the users to just enter the information now by userform and not to have access to all the entries. So Im in a bind as far as developing something that works. The userform works and currently enters all the information. Only issue now is this risk category.

Let me ask this, do you think if I added a sheet, and hid it, but put the risk data in the hidden sheet in the form of a pivott table - could the VBA call on the Pivott table to know which columns in the main sheet to put the risk data? Ie: From the risk category if a user selected, "People" > VBA would automatically know alright the severity and likelihood data for this entry need to be put in the people section.

If a user selected, "Environment" > VBA would know to enter the severity and likelihood data need to be put under the Environment section.

I think you get the idea of how my sheet looks, and what Im trying to do, but I thought I would attach a screenshot of the sheet just incase you wanted to see how this risk section looks. Youll notice that in the actual screenshot there are more categories than I list in this topic, I just use the categories in this topic as examples. (There are two links to the same image below, not sure which one works)



ImageShack® - Online Photo and Video Hosting




Oh, and dont worry about the NA column, thats going to get removed most likely...I just had it there because I thought it was going to be used intially, but turns out its not.

At the moment you're populating a table and it seems that it would provide a good basis for a pivot table which would allow you to summarise the data very easily, very quickly and very flexibly. I would leave it as it is.

In any event, there just isn't enough information on how your sheet is laid out to give you a good answer; a version of your workbook on the interweb somewhere and linked to here would allow a better answer.

Your previous question about starting at row 4 instead of row 1 should need no change to the code, which identifies the last used row on the sheet.

 

[p45cal]

Previously this was set up under a pivot table,

I don't know what this means!

the boss now wants the users to just enter the information now by userform and not to have access to all the entries.

Both quotes above seem to imply that that you can somehow enter information with a pivot table.

Let me ask this, do you think if I added a sheet, and hid it, but put the risk data in the hidden sheet in the form of a pivott table - could the VBA call on the Pivott table to know which columns in the main sheet to put the risk data?

The whole idea of a pivot table is to summarise data in a way to make it easy for people to see and absorb - not for it to be hidden away on a hidden sheet.
You seem to have some misapprehension as to what pivot tables are for - I'm not going to go into a long spiel about them here - Google is your friend there.

From the risk category if a user selected, "People" > VBA would automatically know alright the severity and likelihood data for this entry need to be put in the people section.
If a user selected, "Environment" > VBA would know to enter the severity and likelihood data need to be put under the Environment section.

Again, I'm not sure what you mean here, but I'm guessing the answer is yes because automation and making decisions is just what programming's about.

I think you get the idea of how my sheet looks, and what Im trying to do

No I don't! I'm seeing a tiny part of your sheet. I don't know what it's being used for. I see a few snippets of code. That's it. Crucially, your screenshot doesn't show the kind of data put into each column. Is it just a checkmark? A score? Narrative?
I do see 15 headings under Risk Ranking when I suspect 2 would do, but I'm not sure because I don't know what kind of data is held there.

This thread started by you wanting to know how to automate numbering - it's now about something entirely different.
I suggest (a) you start a new thread and (b) provide a link in that new thread to a version of your workbook on the interweb somewhere.
That way, more people get to see your new problem and they will have a clearer picture of it.

 

[excelenergy]

Hey,

Sorry for the confusion, it just meant the data was already in a pivott table, and is no longer because its no longer a viable solution for what we are trying to do at work.

The userform is only for staff to enter information, they have no access to the sheet where the entries go once they submit them. The risk section (what I mentioned in my previous post), is something the users will enter on the user form. And is something that will be viewed by managers only so it has to be entered into the appropriate areas. (Ie: Environment, People, etc...)

Anyways - that said - I thank you for all your help with this post. I do agree, I think I should close the thread off, and post a new one since this was solved a while ago. Thank you muchly for your time

I don't know what this means!

Both quotes above seem to imply that that you can somehow enter information with a pivot table.

The whole idea of a pivot table is to summarise data in a way to make it easy for people to see and absorb - not for it to be hidden away on a hidden sheet.
You seem to have some misapprehension as to what pivot tables are for - I'm not going to go into a long spiel about them here - Google is your friend there.

Again, I'm not sure what you mean here, but I'm guessing the answer is yes because automation and making decisions is just what programming's about.

No I don't! I'm seeing a tiny part of your sheet. I don't know what it's being used for. I see a few snippets of code. That's it. Crucially, your screenshot doesn't show the kind of data put into each column. Is it just a checkmark? A score? Narrative?
I do see 15 headings under Risk Ranking when I suspect 2 would do, but I'm not sure because I don't know what kind of data is held there.

This thread started by you wanting to know how to automate numbering - it's now about something entirely different.
I suggest (a) you start a new thread and (b) provide a link in that new thread to a version of your workbook on the interweb somewhere.
That way, more people get to see your new problem and they will have a clearer picture of it.