Use VB to move data to a "Form" after inputting the row number

I’m new to VB. Understand some and have figured out others.
I have a problem that I just can’t figure out.

I’ve got a Worksheet for each day. Each day will represent a day of the month.
As you see from the example, there are 13 rows of unique data.

I need to copy data to a “Form” on Worksheet “Form” and then print that form.

I’m trying to have 1 button per sheet that basically says “Print Form”

With this pressed, another “User Form” will open to ask you
1) Which line of data do you need transferred to the “Form” to print
2) Who is the boss requesting the form print. (From a drop down select list)

With these 2 variables, excel should transfer the row of data to the “Form”, print it and then return to the original Worksheet.

I know how to carve out the names based upon the “/”, how to print and how to return to the original sheet after printing.

Any ideas?

Thanks again!

Mike

For the question. Which line of data do you need transferred... you want to pick line 1 of 13 lines from the table?
Or would you rather have it so when you click one of the cells in column B like 1 for example and then press print form so it copys every value in that row to the userform?

Thanks for replying..
I want it so that when the Print Form button is pressed, a userform is opened that asks which line to print and which boss is working.

That line number is used to transfer that line info and the boss that’s working, to the Form.
It’s printed and then I return to the original sheet.

I have a userform with a ListBox and a SpinnerControl, but I don’t really understand how it works in relation to the information, and how the info is stored.

I made a macro to transfer all the info from a line to the form…. But I want to avoid having 13 print buttons on 31 different sheets. That’ll take a year to program.

Ideas?

Still looking for assistance.

Thanks

Can I clarify more, for some help?

Thank you

Hello,

I have looked at your example sheets.

Am working on versions in Office 2003 and 2007, which version are you using.

To the point of the button asking for the line to print and getting the boss name.

Form is being filled with the data from selected row from sheet1.

Currently in the process of writing code to separate the riders names into single entries for rider1 ...etc.

Will post what is done so far if you would like to see it. Maybe you can pick up and add more info.

cplmckenzie

Yes! Appreciate your help. I actually am using 2007 and 2010. Our office has both.

The following formulas are what I'm using to dig out the individual riders. It's not in VBA though, but it works. I think I need variables in order to use one userform to grab info from 31 potential days, at a single line at a time. Everything is single line, copy that line to a "Form" and then print that Form.

Q9 has Smith/Jones/Miller/Stephens/

Please Login or Register  to view this content.

Results are;

Please Login or Register  to view this content.

This is based on your first post and the example sheet you supplied.

Your example has 3 sheets

sheet1 - with the 13 rows of data.

Sheet2 - a blank data sheet

According to what you say, eventually there will be 31 sheets.

Run the attached example (Office 2010), and click the button that says "Fill Out Form"

Answer the questions the MsgBoxes request and then check the Sheet "Form"

If this is like what you are looking for, keep in mind that a 3rd msgbox could popup asking "Which of the 31 sheets do you wish to report for?"

The code behind the button click could then activate that sheet as the sheet to work from.

No print function yet.

cplmckenzie

That gives me a lot to look at. I really like how you cut out the names.

Is it possible to use a spinner and combo box like i have in the userform the same way you use input boxes? Also,
I'd like to reference the "Date" as the date from the Sheet I came from... instead of just using Today().

I have not seen a spinner control in use.

Yes, a combobox can be loaded withe the names of bosses.

Yes, if you are referring to the area in Sheet1 that reads "Wednesday August 1,2012", that data can be placed into Sheet "Form" date cell.

cplmckenzie

Excellent,

Try this modification. It addresses the questions from your last post.

cplmckenzie

cplmckenzie,

Thanks for everything!
I got it to work with the great assist of your help. I have to admit that I don't really understand the walker/stepper loop. Are those official terms or are they just common terms to keep things organized?

I've got a good handle on excel but I'm just starting to understand a little of the VBA process. I now understand about 4%.... so that's double the 2% I used to know!

Thanks again!
Mike

Excellent,

No, walker and stepper are not Reserved words.

Just a technique I use in certain programming situations.

Naming variables with verbs which specify their actions.

Walk(er) simply walks through the string, 1 Step(per) at a time.

Also, this was just rough code to make sure I understood what you
wanted.

The CutString routine, ideally, should be done with 1 For-Next Loop
inside a Do-Loop.

Do will Loop six times, once for each rider in your list with the necessary calculations
done in between the Do and For and also between the Next and Loop portions of your code.

But if you are satisfied with it's functionality then we will leave it as is.

cplmckenzie