|
Auto Number In Text Box
Hi All
On a UserForm in TextBox1 - I enter the number 1 fill in all the other Text Boxes hit enter all the details appear on the sheet in Row 2 starting with Column A which has the number1 in from TextBox1. Is it possible to code TextBox1 so that after hitting enter - Number2 auto appears in TexTBox1 ready for the next row of details to be entered - and so on down the rows up to a possible 250 Any help much appreciated Mully |
Hi Mully
TextBox1.Text = Val(TextBox1.Text) + 1 HTH. Best wishes Harald "mully" skrev i melding ... Hi All On a UserForm in TextBox1 - I enter the number 1 fill in all the other Text Boxes hit enter all the details appear on the sheet in Row 2 starting with Column A which has the number1 in from TextBox1. Is it possible to code TextBox1 so that after hitting enter - Number2 auto appears in TexTBox1 ready for the next row of details to be entered - and so on down the rows up to a possible 250 Any help much appreciated Mully |
Hi Harald
I have tried inserting the code you suggested and In Column A it enters the text FALSE. Perhaps it would help if you had a look at the code listed below. (Text Box1) is actually( txtPos) in the code. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("FedNew") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.txtPos.Value) = "" Then Me.txtPos.SetFocus MsgBox "Please Enter A Position Number" Exit Sub End If ws.Cells(iRow, 1).Value = Me.txtPos.Value ws.Cells(iRow, 2).Value = Me.txtSec.Value ws.Cells(iRow, 3).Value = " " & Trim(Me.txtName.Value) + " " & Trim(Me.txtTitle.Value) ws.Cells(iRow, 4).Value = " " & Trim(Me.txtClub.Value) ws.Cells(iRow, 5).Value = Me.txtMiles.Value ws.Cells(iRow, 6).Value = Me.txtYards.Value ws.Cells(iRow, 7).Value = Me.txtHrs.Value ws.Cells(iRow, 8).Value = Me.txtMins.Value ws.Cells(iRow, 9).Value = Me.txtSecs.Value ws.Cells(iRow, 10).Value = Me.txtGen.Value ws.Cells(iRow, 11).Value = Me.txtRing.Value 'clear the data Me.txtPos.Value = "" Me.txtTitle.Value = "" Me.txtSec.Value = "" Me.txtName.Value = "" Me.txtClub.Value = "" Me.txtMiles.Value = "" Me.txtYards.Value = "" Me.txtHrs.Value = "" Me.txtMins.Value = "" Me.txtSecs.Value = "" Me.txtGen.Value = "" Me.txtRing.Value = "" Me.txtPos.SetFocus End Sub Many thanks for looking ---- Mully "Harald Staff" wrote: Hi Mully TextBox1.Text = Val(TextBox1.Text) + 1 HTH. Best wishes Harald "mully" skrev i melding ... Hi All On a UserForm in TextBox1 - I enter the number 1 fill in all the other Text Boxes hit enter all the details appear on the sheet in Row 2 starting with Column A which has the number1 in from TextBox1. Is it possible to code TextBox1 so that after hitting enter - Number2 auto appears in TexTBox1 ready for the next row of details to be entered - and so on down the rows up to a possible 250 Any help much appreciated Mully |
I salute your variable declaration and clean code. Very neat work, Mully.
Now see if this makes sense: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim LngPos As Long Set ws = Worksheets("FedNew") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row LngPos = Val(Me.txtPos.Text) If LngPos = 0 Then Me.txtPos.SetFocus MsgBox "Please Enter A Position Number" Exit Sub End If ws.Cells(iRow, 1).Value = LngPos ws.Cells(iRow, 2).Value = Me.txtSec.Value '... 'clear the data Me.txtPos.Text = LngPos + 1 Me.txtSec.Text = "" '... Me.txtSec.SetFocus End Sub HTH. Best wishes Harald "mully" skrev i melding ... Hi Harald I have tried inserting the code you suggested and In Column A it enters the text FALSE. Perhaps it would help if you had a look at the code listed below. (Text Box1) is actually( txtPos) in the code. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("FedNew") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.txtPos.Value) = "" Then Me.txtPos.SetFocus MsgBox "Please Enter A Position Number" Exit Sub End If ws.Cells(iRow, 1).Value = Me.txtPos.Value ws.Cells(iRow, 2).Value = Me.txtSec.Value ws.Cells(iRow, 3).Value = " " & Trim(Me.txtName.Value) + " " & Trim(Me.txtTitle.Value) ws.Cells(iRow, 4).Value = " " & Trim(Me.txtClub.Value) ws.Cells(iRow, 5).Value = Me.txtMiles.Value ws.Cells(iRow, 6).Value = Me.txtYards.Value ws.Cells(iRow, 7).Value = Me.txtHrs.Value ws.Cells(iRow, 8).Value = Me.txtMins.Value ws.Cells(iRow, 9).Value = Me.txtSecs.Value ws.Cells(iRow, 10).Value = Me.txtGen.Value ws.Cells(iRow, 11).Value = Me.txtRing.Value 'clear the data Me.txtPos.Value = "" Me.txtTitle.Value = "" Me.txtSec.Value = "" Me.txtName.Value = "" Me.txtClub.Value = "" Me.txtMiles.Value = "" Me.txtYards.Value = "" Me.txtHrs.Value = "" Me.txtMins.Value = "" Me.txtSecs.Value = "" Me.txtGen.Value = "" Me.txtRing.Value = "" Me.txtPos.SetFocus End Sub Many thanks for looking ---- Mully "Harald Staff" wrote: Hi Mully TextBox1.Text = Val(TextBox1.Text) + 1 HTH. Best wishes Harald "mully" skrev i melding ... Hi All On a UserForm in TextBox1 - I enter the number 1 fill in all the other Text Boxes hit enter all the details appear on the sheet in Row 2 starting with Column A which has the number1 in from TextBox1. Is it possible to code TextBox1 so that after hitting enter - Number2 auto appears in TexTBox1 ready for the next row of details to be entered - and so on down the rows up to a possible 250 Any help much appreciated Mully |
Hi Harald
Thank you --- worked first time exactly what I needed --- I had to keep going back to the sheet to remember the last Position entered -- at 64years old the memory is not what it used to be -- I've placed the code in my remember Code Folder for future reference. Once again thank you for all your help Cheers ---- Mully "Harald Staff" wrote: I salute your variable declaration and clean code. Very neat work, Mully. Now see if this makes sense: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim LngPos As Long Set ws = Worksheets("FedNew") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row LngPos = Val(Me.txtPos.Text) If LngPos = 0 Then Me.txtPos.SetFocus MsgBox "Please Enter A Position Number" Exit Sub End If ws.Cells(iRow, 1).Value = LngPos ws.Cells(iRow, 2).Value = Me.txtSec.Value '... 'clear the data Me.txtPos.Text = LngPos + 1 Me.txtSec.Text = "" '... Me.txtSec.SetFocus End Sub HTH. Best wishes Harald "mully" skrev i melding ... Hi Harald I have tried inserting the code you suggested and In Column A it enters the text FALSE. Perhaps it would help if you had a look at the code listed below. (Text Box1) is actually( txtPos) in the code. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("FedNew") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.txtPos.Value) = "" Then Me.txtPos.SetFocus MsgBox "Please Enter A Position Number" Exit Sub End If ws.Cells(iRow, 1).Value = Me.txtPos.Value ws.Cells(iRow, 2).Value = Me.txtSec.Value ws.Cells(iRow, 3).Value = " " & Trim(Me.txtName.Value) + " " & Trim(Me.txtTitle.Value) ws.Cells(iRow, 4).Value = " " & Trim(Me.txtClub.Value) ws.Cells(iRow, 5).Value = Me.txtMiles.Value ws.Cells(iRow, 6).Value = Me.txtYards.Value ws.Cells(iRow, 7).Value = Me.txtHrs.Value ws.Cells(iRow, 8).Value = Me.txtMins.Value ws.Cells(iRow, 9).Value = Me.txtSecs.Value ws.Cells(iRow, 10).Value = Me.txtGen.Value ws.Cells(iRow, 11).Value = Me.txtRing.Value 'clear the data Me.txtPos.Value = "" Me.txtTitle.Value = "" Me.txtSec.Value = "" Me.txtName.Value = "" Me.txtClub.Value = "" Me.txtMiles.Value = "" Me.txtYards.Value = "" Me.txtHrs.Value = "" Me.txtMins.Value = "" Me.txtSecs.Value = "" Me.txtGen.Value = "" Me.txtRing.Value = "" Me.txtPos.SetFocus End Sub Many thanks for looking ---- Mully "Harald Staff" wrote: Hi Mully TextBox1.Text = Val(TextBox1.Text) + 1 HTH. Best wishes Harald "mully" skrev i melding ... Hi All On a UserForm in TextBox1 - I enter the number 1 fill in all the other Text Boxes hit enter all the details appear on the sheet in Row 2 starting with Column A which has the number1 in from TextBox1. Is it possible to code TextBox1 so that after hitting enter - Number2 auto appears in TexTBox1 ready for the next row of details to be entered - and so on down the rows up to a possible 250 Any help much appreciated Mully |
Glad to hear that. Thank you for the feedback.
Best wishes Harald "mully" skrev i melding ... Hi Harald Thank you --- worked first time exactly what I needed |
| All times are GMT +1. The time now is 09:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com