Automatically updating the userid in a userform | Excel forum

Resolved · Medium Priority · Version 2016

Tue 16 Jan 2018, 12:46 Delegate Natalie said...

Natalie has attended:
Excel Advanced course

Automatically updating the userid in a userform

I have developed a userform. I am trying to work out how to automatically assign and update a user ID.

Fri 19 Jan 2018, 09:50 Trainer Jens said...

RE: Automatically updating the userid in a userform

Hi Natalie,

Thank you for the forum question.

What do you mean by assign and update a user id.

You can create userforms using VBA in Excel. Is your userform a VBA userform? If not please let me know more details about what you have created.

If it is a VBA userform the code below will add the user name in a text box on the userform:

Private Sub UserForm_Initialize()
Me.TextBox1.Text = Application.UserName
End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 19 Jan 2018, 10:21 Delegate Natalie said...

RE: Automatically updating the userid in a userform

Hi Jen,

My userform has been made in Visual Basic.

The userform populates a table with customer information about an incident. When this information goes into the table I have to manually enter a code. I would like the form to be able to assign the ID automatically e.g. 001 and the next time the field is completed it will automatically assign 002 to the next entry.

Fri 19 Jan 2018, 10:48 Trainer Jens said...

RE: Automatically updating the userid in a userform

Hi Natalie,

First if you want the user id to be 001, 002, 003 you will need much more coding. 001 is a text string and you cannot just add one to it.

I have done a code below I hope will guide you in the right direction, but I have used numbers 1,2,3.

You will need to tell Excel to get the last ID used. In my example I have the IDs in column A. The code picks up the last ID used and then add one to it and enter it in the table.

Private Sub UserForm_Initialize()
Dim lId As Long
Sheets(1).Range("A1").CurrentRegion.Columns(1).Select
lId = Selection.Rows(Selection.Rows.Count).Value
Selection.Rows(Selection.Rows.Count + 1).Value = lId + 1
End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector