Generate ID Number to textbox in userform

[bj1280]

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

 

[Wanderer_geologist]

Good day

Few give insight, I recommend to always name your items in the Userform with proper name, it become dreadful to navigate on the long term otherwise (always name the script for a button, C_myscript, the checkbox CB_myvalue) etc. That before anycode is associated to it (otherwise, it's going to disapepar). That make things a lot more easier to understand for someone else.


I can propose the alternative, in commandButton1 and the userform. In theory the repaint command should refresh but I prefer to reinitialize the form with the last command, that proove more effective.

Hope it can help.

Private Sub CommandButton1_Click()
			Dim ws As Worksheet
			Dim addme,myvalue As Long
			Set ws = Sheet3


			addme = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
			myvalue= ws.cell(1,1).value
	
			With ws
			.cells(1,1).Value = myvalue+1
			.Cells(addme, 2).Value = Me.TextBox2
			.Cells(addme, 3).Value = Me.TextBox3
			.Cells(addme, 4).Value = Me.TextBox4
			.Cells(addme, 5).Value = Me.ComboBox1
			.Cells(addme, 6).Value = Me.TextBox6
			.Cells(addme, 7).Value = Me.TextBox7
			.Cells(addme, 8).Value = Me.DTPicker1
			.Cells(addme, 9).Value = Me.ComboBox4
			.Cells(addme, 10).Value = Me.ComboBox2
			.Cells(addme, 11).Value = Me.TextBox11
			.Cells(addme, 12).Value = Me.ComboBox3
			.Cells(addme, 13).Value = Me.TextBox9
			End With


			Unload Me
			
			userform1_initialize






End Sub




Private Sub userform1_initialize()


Dim ws As Worksheet: Set ws = Sheet3
TextBox1.Text =  ws.Range("A1").Value


End Sub

 

[bj1280]

have it exactly as you have it - but still does not populate the textbox and comes back with an error - .Value = .Value + 1=type mismatch
The Value is "NMBC - 1001". Is this where I am having problems. Cheers in advance.

 

[Wanderer_geologist]

Ha got it
There is a little workaround, it's going on error as the operation for value +1 apply only to number, the Value in question is string.
Keep in a separate a value which is a number, that one you can concatenate to your string.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim addme,myvalue As Long
Set ws = Sheet3


addme = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
myvalue= ws.cell(1,13).value
textbox1.caption = "NMBC-" & myvalue

With ws
.cells(1,1).Value = me.textbox1
.Cells(addme, 2).Value = Me.TextBox2
.Cells(addme, 3).Value = Me.TextBox3
.Cells(addme, 4).Value = Me.TextBox4
.Cells(addme, 5).Value = Me.ComboBox1
.Cells(addme, 6).Value = Me.TextBox6
.Cells(addme, 7).Value = Me.TextBox7
.Cells(addme, 8).Value = Me.DTPicker1
.Cells(addme, 9).Value = Me.ComboBox4
.Cells(addme, 10).Value = Me.ComboBox2
.Cells(addme, 11).Value = Me.TextBox11
.Cells(addme, 12).Value = Me.ComboBox3
.Cells(addme, 13).Value = Me.TextBox9
.Cells(addme, 13).Value = Myvalue ' That one will store the value
End With




Unload Me
userform1_initialize


End Sub








Private Sub userform1_initialize()


Dim ws As Worksheet: Set ws = Sheet3
TextBox1.Text = ws.Range("A1").Value


End Sub

 

[bj1280]

Wish I knew how to send you the excel file - your code looks brilliant, however still no joy - get error messages throughout. Thanks for your efforts.

 

[Logit]

Post your entire code please.

 

[bj1280]

Hi logit, entire code has been posted - a few posts back.

 

[Logit]

Please post your code.

 

[bj1280]

This is the code
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