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

JohnnySTI · #1 01-17-2018, 09:58 AM

I have a data base built using advance filters and user forms.

Raw Material is order and received in, the order information along with other data is entered manually into a header based data spread sheet "page" where later this data spread sheet page can be searched using the advance filters and user forms for particular information from particular orders.

What I need to do now is generate a router sheet "form"
This form would need to be populated based on the row that the "just received in" material information was entered into. Then printed out all by using a command button named "generate route sheet"

I know how to take copied data and import it into a PDF form with the fields that match the headers in the data spread sheet page. BUT the people using this database are not capable of such a task as to the one button solution request.

any and all suggestion would be appreciated.

NoSparks · #2 01-17-2018, 10:41 AM

Have you tried using the macro recorder to record what you currently do manually?
The recorded macro would undoubtedly need 'tuned-up' before assigning to the button but would be a great place to start.

JohnnySTI · #3 01-17-2018, 12:05 PM

Yes the Macro recorder would work, but its the fine tuning that is the road block.
The issue would be how to identify the selected line (highlighted) at the start, so the macro would know which material entry you wanted to run a route sheet on.

I'm not sure as to how I identify the selected/highlighted line within the macro so it knows what data to copy and export to the form.

I guess I could at least run the micro and then I would have everything but the identifying section at the beginning and work/"fine tune" from there...

NoSparks · #4 01-17-2018, 12:51 PM

Quote:

I guess I could at least run the micro and then I would have everything but the identifying section at the beginning and work/"fine tune" from there...

Put this in standard module and run it to see one possible way of determining what to work with

Code:

Sub SelectWhatToWorkWith()
    Dim WorkWithThis As Range
' HAVE USER SELECT ROW TO WORK WITH
Set WorkWithThis = Application.InputBox("Select a cell on the row to work with", "Row To Work With", Type:=8)
'if nothing is selected then exit sub
If WorkWithThis Is Nothing Then Exit Sub
'check for only one selected cell
If WorkWithThis.Count = 1 Then
    MsgBox "Going to work with row " & WorkWithThis.Row
Else
    MsgBox "Sorry, you've selected more than one cell."
    Exit Sub
End If
End Sub

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

NoSparks · #6 01-22-2018, 01:17 PM

Well... you've got me baffled. I don't know if your form is a sheet or a real user form or if you're using both.
Without a sample file to give some idea as to what you're working with and talking about, I can only comment on the code you've posted.

The range 'nextrow' is a single cell, assuming you want the data written to that row starting at column 3
try this (you missed copying the sub name)

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.Offset(, X - 1).Value = Me.Controls("Reg" & X).Value
Next X

MsgBox "The data has been sent"

'Clear the controls
For X = 1 To cNum
    Me.Controls("Reg" & X).Value = ""
Next X

End Sub

Pecoflyer · #7 01-23-2018, 12:58 AM

@Johnny
Hello
please always wrap code with code tags - Edit the post - click "Go advanced" - select code - click the #button
Thank you

JohnnySTI · #8 01-23-2018, 06:37 AM

Take a look at the attached PDF. It should be more clearer as to what I'm trying to accomplish.

All my code works as it should. This issue is generating a new ID (see pdf)

and thanks again for all you help

NoSparks · #9 01-23-2018, 09:37 AM

Quote:

All my code works as it should. This issue is generating a new ID

I'd use

Code:

Application.WorksheetFunction.Max(Range("C5:C" & lastrow)) + 1

JohnnySTI · #10 01-23-2018, 11:44 AM

1st error was "Variable not defined" which was "lastrow"

I Dim it as "Range" then got

Run Time error '91':
Object variable or With block variable not set

I tried below but it didn't work
---
lastrow = Me.Controls("Reg1").Value

'Reg1 is the ID control box name and column name for the ID numbers
---

NoSparks · #11 01-23-2018, 11:46 AM

Why don't you post an Excel file I can work with ?

NoSparks · #12 01-23-2018, 05:33 PM

lastrow is a variable for the last row, rows are designated by number, best declared as Long
you need to calculate lastrow as it will continually change, see this for how to.

You may also be interested in a list of VBA sites compiled by hiker95 over at MrExcel and posted in this thread

JohnnySTI · #13 01-24-2018, 01:10 PM

Okay I got it...

Dim lastrow as Long
lastrow = Sheet3.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
MsgBox lastrow
' Message box shows me the first empty row number cell in the ID column, which is good I got something to work with...
then
Cells(lastrow, 3).Value = lastrow - 4
' the (- 4) is the offset of where my data started, meaning my data began being entered on row 5 and my ID numbers began on row 5 starting with 0001 so when the lastrow code above returned me the value of the next empty row I subtracted 4 form lastrow value which give me my next ID number in the ID cell where I needed it to be

I'm sure grateful for your help and direction...

JohnnySTI · #14 01-24-2018, 01:20 PM

by the way I abandon the FORM as the actual document to be printed and instead designed a FORM on a worksheet with the same fields. I had better control of the print functions and the original FORM is now used mainly as an interface everything seems to run much smoother.

Thanks again for all you help

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