Sequential number generation

Paul15

New Member
Joined
Jun 25, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Hi Team,

I have used the code in question 965869 and can get a sequential number to populate via my user form to the required location on the spreadsheet, however this is writing to the spreadsheet before the other entered data is submitted to the sheet via the command button. This is forcing my remaining data to populate 1 line below the sequential number. Code is:

Private Sub CommandButton1_Click()
With ThisWorkbook.Worksheets("Sheet1")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1) = TextBox1.Value
TextBox1.Text = Application.Max(.Range("A:A")) + 1
End With
End Sub

Private Sub UserForm_Initialize()
With ThisWorkbook.Worksheets("Sheet1")
TextBox1.Text = Application.Max(.Range("A:A")) + 1
End With
End Sub

If we can maybe change the sequence of events such that the generated number does not write to the sheet until the command button is pressed I think this might fix it

Any help please
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Comment out the code within the Private Sub UserForm_Initialze() Use Rem or ' apostrophe

VBA Code:
Rem  With ThisWorkbook.Worksheets("Sheet1")
Rem        TextBox1.Text = Application.Max(.Range("A:A")) + 1
Rem    End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,610
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
This is the code that is writting to the sheet
VBA Code:
Private Sub CommandButton1_Click()
   With ThisWorkbook.Worksheets("Sheet1")
       .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = TextBox1.Value
       TextBox1.Text = Application.Max(.Range("A:A")) + 1
   End With
End Sub
If you remove it then you should be ok.
 

Paul15

New Member
Joined
Jun 25, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Hi Trevor,

commenting out the code as suggested stops the remaining code from working. I no longer have a generated number

I have got it to work, but on clicking the command button to submit the updates from the form to the sheet, the sequence number seems to write first; on a blank sheet, A1, the remainder of the submitted data goes to Row 2. This would make sense as the VBA is trying to write data and will always write to the next blank row. As the sequential number had filled a row then the codec writes to the next row down. What I wish to happen is all data is written at the same time with the sequential number and data entering sequentially.

Regards
 

Paul15

New Member
Joined
Jun 25, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Fluff, I have astrixed out the code as you suggest, however whilst my data goes to the correct place, the sequential number i wish to be sent to the sheet and sequentially done so for every data input

Regards
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,610
Office Version
  1. 365
Platform
  1. Windows
In that case you will need to add that code, to the code which updates your sheet.
 

Paul15

New Member
Joined
Jun 25, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi. I have a worksheet with data being entered via a user form. This uses a command button ( submit button) to submit data to sheet. I wish to have a sequential number added to each new row of data; my sheet this will be at column AS of whatever row the new data is copied to upon activation of the command / submit button.
The number must increment by 1 for each new row from a predetermined start value, say 120000.
The number must remain sequential no matter if the form is closed or cleared before submission, last number, eg 120010, form cleared or closed, reopened next number 120011
All data should write to the sheet at the same time and all be on the same row

Regards
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,610
Office Version
  1. 365
Platform
  1. Windows
What code do you have for adding the values to the sheet?
 

Paul15

New Member
Joined
Jun 25, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
What code do you have for adding the values to the sheet?
Code is below. I found this in an earlier question.

On the user form there is a text box called SQNum, a sequential number is seen here. Other data fields are entered in the various other areas of the user form. Upon clicking the Command Button to submit all the data to the sheet ‘IS81s’ the sequential number goes to the sheet at AS1, the remaining data goes to row 2.
I need all data to appear on the same row


Private Sub CommandButton1_Click()
With ThisWorkbook.Worksheets("IS81s")
.Cells(.Rows.Count, "AS").End(xlUp).Offset(1) = SQNum.Value
SQNum.Text = Application.Max(.Range("AS:AS”)) + 1
End With
End Sub

Private Sub UserForm_Initialize()
With ThisWorkbook.Worksheets("IS81s")
SQNum.Text = Application.Max(.Range("AS:AS”)) + 1
End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,610
Office Version
  1. 365
Platform
  1. Windows
That code only adds the SQNum to the sheet nothing else. Please post the code that puts the rest of the data to the sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,662
Messages
5,637,632
Members
416,977
Latest member
kdoederlein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top