Generate ID Number to textbox in userform

bj1280 · Jan 17, 2019

Hi all,
Very new to all this - I'm an old dog trying to learn new tricks. I have created a userform with textbox's and combobox's along with a save button. When you press the save button, all the entered data does what it is meant to do - sent to sheet3 (info) and then resets the form for the next entry.
The one problem area is textbox1 - I need it to populate with a unique ID every time it is either opened for the first time, or reset via the save button. This number will ultimately form the invoice number.
Sheet3 has a defined range of CustomerId is column one - formatted as special with NMBC1001 in place of numbers. The first available row is A2.
I have literally slaved over the keyboard and internet for nearly a week or more just on this one area - any help will be greatly appreciated.
As a footnote - I actually had it working once by mistake, only to have it disappear when I deleted what I thought was irrelevant code

Logit · Jan 17, 2019

.
You can place this in your userform, within the sub for the SAVE button.

Play around with it and see what you can do ..

Code:

With Sheet1.Range("A1")  '<-- can be any cell
        .Value = .Value + 1
    End With

Then in the UserForm INITIALIZE sub be certain to include a line :

Code:

TextBox1.Text = Sheet1.Range("A1").Value  '<-- or whatever cell you had selected

Edit the SHEET number and TEXTBOX description to match your project.

bj1280 · Jan 18, 2019

Logit, thanks for the info - however when I inserted the codes in the correct places and ran the form it came back with a compile error: invalid or unqualified reference.
Any thoughts - seems to be referring to the "With Sheet3.Range("A2").Value = .Value + 1 part.

My Aswer Is This · Jan 18, 2019

Did you include the End With ?

TotallyConfused · Jan 18, 2019

bj1280 said:
Hi all,

As a footnote - I actually had it working once by mistake, only to have it disappear when I deleted what I thought was irrelevant code

Hello Bj1280

Welcome to the site.
I’ll leave your coding problem to the experts here because I’m fairly new to VBA also. One trick I have learned through sad experience, is BEFORE I delete any code, I first put an apostrophe in front which deactivates that line. Then I run the macro again to make sure it still works. If it does, then I’ll delete the code. If it doesn’t run, I can remove the apostrophe, and everything is back to the beginning. That way I don’t loose anything.

TotallyConfused

bj1280 · Jan 18, 2019

sure did - does it matter that I have two with / end with statements in the code under the save button command??
Is there code I have to put in the textbox portion?

bj1280 · Jan 18, 2019

Thanks - I'll remember that for next time.

My Aswer Is This · Jan 18, 2019

Show us the complete code your using if your still having problems

bj1280 · Jan 18, 2019

My Aswer Is This said:
Show us the complete code your using if your still having problems

This sis what I have -

Private Sub Booking_Click()

End Sub

Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub TextBox10_Change()
TextBox10.Value = Format(TextBox10.Value, "$###,##")
End Sub

Private Sub TextBox11_Change()
TextBox11.Value = Format(TextBox11.Value, "$###,##")
End Sub

Private Sub ComboBox4_Change()

End Sub

Private Sub ComboBox1_Change()
Dim rw As Range
For Each rw In Range(ComboBox1.RowSource)
If ComboBox1.Value = rw Then
TextBox5.Value = rw.Next
End If
Next rw
End Sub

Private Sub ComboBox2_Change()
Dim rw As Range
For Each rw In Range(ComboBox2.RowSource)
If ComboBox2.Value = rw Then
TextBox11.Value = rw.Next
End If
Next rw
End Sub

Private Sub UserForm_activate()

ComboBox1.ListIndex = 0
ComboBox2.ListIndex = 0
ComboBox3.ListIndex = 0
ComboBox4.ListIndex = 0

End Sub

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim addme As Long
Set ws = Sheet3
addme = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

With Sheet3.Range("A1").Value = Value + 1
End With

With ws
ws.Cells(addme, 2).Value = Me.TextBox2
ws.Cells(addme, 3).Value = Me.TextBox3
ws.Cells(addme, 4).Value = Me.TextBox4
ws.Cells(addme, 5).Value = Me.ComboBox1
ws.Cells(addme, 6).Value = Me.TextBox6
ws.Cells(addme, 7).Value = Me.TextBox7
ws.Cells(addme, 8).Value = Me.DTPicker1
ws.Cells(addme, 9).Value = Me.ComboBox4
ws.Cells(addme, 10).Value = Me.ComboBox2
ws.Cells(addme, 11).Value = Me.TextBox11
ws.Cells(addme, 12).Value = Me.ComboBox3
ws.Cells(addme, 13).Value = Me.TextBox9
End With

Unload Me
Userform1.Show

End Sub

Private Sub Frame1_Click()

End Sub

Private Sub Label2_Click()

End Sub

Private Sub Label3_Click()

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub TextBox5_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub CommandButton1_activate()

End Sub

Private Sub userform1_initialize()

TextBox1.Text = Sheet3.Range("A1").Value

End Sub

Apologies in advance if I don't respond much in the next days - work commitments etc but I will be checking to see if we have a solution.
Thanks everyone for helping.

My Aswer Is This · Jan 18, 2019

It should be like this:

Code:

With Sheet3.Range("A1")
.Value = .Value + 1
End With

Generate ID Number to textbox in userform

bj1280

New Member

Excel Facts

Logit

Well-known Member

bj1280

New Member

My Aswer Is This

Well-known Member

TotallyConfused

Board Regular

bj1280

New Member

bj1280

New Member

My Aswer Is This

Well-known Member

bj1280

New Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Generate ID Number to textbox in userform

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Board Regular

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock