Data from a specific row in a spread sheet into a form

JohnnySTI · #5 01-22-2018, 11:41 AM

I did some "fiddling" and ended up using VLookup. I assigned my database list "rows" with an ID. So the employee opens the router form by clicking button...form opens employee enters the ID #...clicks in an empty field in the form and the fields on the router sheet automatically populates then they click print and done...I also made a "Store Information" button so the router sheet can be used to enter data into the database list...BUT I've ran into another issue.

I created a cmdSend button so when clicked the code below looks for the next available empty row in my database list and stores what is currently populated in the router form in that empty row.
What I need is when the form is used to "store" data an ID in not entered and that field on the router form is left blank. I need the code below to store the data as usual (in the next empty row) but if there is no data in the ID control/field of the router form, the code looks at the ID column/row in the database list that has an ID number and increments it by 1 and stores the new ID in the row/cell under the ID column of the current data being stored.

This could be done by itself when the form is closed by looking for the next empty "cell" in the ID column and enter a number that's 1 more that the cell above...point being is the ID is only important when "pulling" data not storing it.
So Keeping to that train of thought maybe generating a number in the ID control field itself by incrementing the last ID number found in the ID column/row database...that would be even better because it wouldn't be stored unless the user hits the cmdSend button. and if they're looking up material only, they would just erase the generated ID number and enter the ID they need to being up the information they're looking for...I going to work on that one

Code:

'Dim the variables
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range

'change the number for the number of controls/text boxes on the userform

cNum = 17
Set nextrow = Sheet3.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
MsgBox "The data has been sent"
'Clear the controls
cNum = 17
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Set nextrow = nextrow.Offset(0, 1)
Next
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Simple Spread Sheet	TThomas	Excel	1	04-07-2016 09:01 PM
extract specific pivot table data to a new sheet	theexpat	Excel Programming	0	02-18-2016 10:08 AM
Using combobox to pass data from an Excel spread sheet to text boxes on userform	Stewie840	Word VBA	14	12-17-2015 10:13 PM
changing the spread sheet location on embeded pie chart	otuatail	PowerPoint	1	03-04-2014 07:20 AM
Condensing a spread sheet	hawkeyefxr	Excel	4	08-22-2012 05:17 AM