Using VBA Code and checkBoxes to transfer data to another sheet

[carlandtina02]

Hi all,

I'm a newbie to Excel 2003 and didn't really know how powerful of a program it was, so after being introduced to it I have decided to collate all Parts and their Part Numbers from a couple of machines that I work on to create a Service Report, to detail all work carried out on them.

In the workbook I have four sheets, the first three Worksheets are where the Part Descriptions, Part Numbers and Exploded Diagrams are, and are set up in columns B,C and D (Starting at row 5) with the relevant data underneath.

The fourth Worksheet is the Service Report with relevant fields for data. The two main fields are the Used Parts section (That starts at 22 B & C onwards) and the Further Work Required section (That starts at 38 B & C onwards).

What I'm requiring is VBA code that I could apply to the first three sheets that would create two checkBoxes (in each row of column A) that when checked, one would transfer data from column B & C of the selected row, to cells 22 B & C onwards, and one that would transfer the same data to cells B & C 38 onwards in Sheet4. The code would also select the next available row in the main two fields of sheet4 and continue the border. I could move everything along one so that there is two columns, one for each checkBox if needed.

Sorry to drone on a bit, but just thought I'd try to give as much info as I could.

If anyone could be so kind to help me on this, it would mean the world as it's starting to drive me crazy

Kind Regards
Carl

[rscsmith]

Hi Carl

Are you able to upload a sample spreadsheet (doesn't need to have all your data in it) so that we can see how the sheet is set up? Ideally it would include, an example of sheet 4 partially completed.

Also, would it be acceptable to use a value in column A of the first three sheets, rather than a check box? This could be U or F (Used Future) or 1 / 2, etc. This would make adding/deleting parts easier, as you wouldn't need to add checkboxes. The values could be on a drop down list. Another alternative would be to select the part then hit one of two buttons at the top.

What happens if you have more than 16 parts in the USED PARTS list? Do you want the section to expand, or just stop and say "No more room"?

Cheers, Rob.

[rylo]

Hi

Rather than building heaps of checkboxes, it may be simpler to just use the beforedoubleclick event activity on the nominated range and have that perform the required actions.

Can you attach an example workbook so we have your structure, representative data, and an example on what you would want to happen.

rylo

[carlandtina02]

Hi Guys,

Thanks for your suggestions so quickly. Rob, your suggestion of the U or F value in a drop down list sounds great, it would also be ideal if the Used Parts and Further Work Required section would expand. I have attached a copy of the workbook (which I should of done).

I have removed the photos and worksheets 3/4 as the file was a little on the big side and wouldn't upload, I plan to take most of them out anyway when I condense the workbook as it is still a work in progress kind of thing and maybe roll it out to use on a tablet PC in the future.

Rylo, could you give me little more info on what the beforedoubleclick function is and how its used please.

Again Guys many thanks with your help.

Kind Regards
Carl

[rylo]

Carl

As an example of what I mean, clear out the data on sheet Service Report, Select sheet Q300 Mk1 & 2, right click on the tab and paste in the code below.

Now if you double click in a single cell in column B it will transfer the data to B22:C22, or if you double click in a single cell in column C, then it will transfer the data to B38:C38. As this is only an example, it doesn't work out the next clear cell, or expand the range if required, and all those other things that will be necessary in the full solution. This is really only meant to give you some idea of what it can do.

rylo

Please Login or Register  to view this content.

[carlandtina02]

Hi Rylo,

Yeah that works pretty well thanks, I just need it to find the next clear cell and to extend the border format now, but yeah thanks for that it's a big help.

Regards
Carl

[carlandtina02]

Hi,

I'm trying to use the above BeforeDoubleClick code in my workbook but want it to find the next clear row to transfer data the to, I also want it to copy the format of the other cells in that row as I am only transferring data to two cells (one in each column), But I can't seem to get it to offset.

Has anyone done this before and have a bit code I could drop in please? I have attached the new workbook as an example.


Thanks in advance
Carl

[rylo]

Carl

This should bring in the data for you to the preformatted rows. At what point do you want to start adding rows and bringing in the formatting?

Please Login or Register  to view this content.

rylo

[carlandtina02]

Hi Rylo,

Thanks for that, I would like it to start adding rows after B/C26 and B/C39 and to copy the row format, also would it return back to the five rows if I deleted the data for a fresh report or will it leave the formatted rows behind?

Thanks for your help
Carl

[rylo]

Carl

I presume that you will want to print this report. If so, then is there any limit to the size of the page? What I'm thinking is that maybe you should preset your page to the largest size that you can comfortably print, fix the page to this size, and then only insert items until the page (either block) is filled. If there are more entries than a page can handle, then give a warning message that the page has to be printed, reset and then the selections continued.

rylo

[carlandtina02]

Hmm, I never really give that a thought.
Yeah that sounds good but I don't mind it going onto a second page if that's easier.

Cheers,
Carl

[rylo]

Carl

Actually easier is to have a fixed page size so that you can build all your formatting. So which way do you want to go?

rylo

[carlandtina02]

Hi Rylo,

I'll go with the fixed page size please.

Cheers,
Carl

[rylo]

Carl

Great. Can you either update your existing example file, or put up a new file that will be in the final structure size and I'll work towards that.

rylo

[carlandtina02]

Hi Rylo,

I think I've managed it, I've just updated the original file and added your code to it.

Cheers,
Carl

[rylo]

Carl

OK, see how this goes.

Please Login or Register  to view this content.

rylo

[carlandtina02]

Hi Rylo,

Thank you very much this works a treat, I have altered it slightly as I now understand how it works and what it is doing. There is a small typing error on the line of code below highlighted in bold;

OutSH.Range("B39").End(xlUp).Offset(1, 0).Resize(1, 2).Value = targer.Offset(0, -1).Resize(1, 2).Value (Targer should say Target) (I have only mentioned this to help others wanting to use this code)

Again Rylo many thanks for your help with this, it is greatly appreciated.

Kind Regards
Carl

[rylo]

Carl

Thanks for picking that up. I edited the post with the correction to the typo.

rylo