[mackinlay]

Hi there,

In Excel 2003, I am trying to write some code so that when I click on a tick box next to a list of headings (eg B4:B20), the heading and its related sub-headings (that are located on a separate sheet) are copied into a new sheet and arranged in a table (the macros have a function embedded in them that draws the lines and headings of the table). This means that only the ticked headings will be put into the new table and the irrelevant ones for a certain situation are not included in the table.

I currently have the code set up so that the title and sub-titles are pasted into the new sheet, but they are pasted into a specified cell. I would like to get it to paste into the next available free cell, rather than a pre-determined cell.

On top of this, I can currently paste in everything except for the tick boxes which are permanent features of the worksheet, whether I want them or not. Can these be pasted or do they have to stay assigned to a cell and remain there at all times?

I would greatly appreciate any help you can give me.

Thanks,
Mack

[Dirizhor]

Use auto filter and select only the checked rows, copy them and paste into new sheet either by doing paste special or paste as required

[Aj_old]

Hi Mack!
Welcome to TSG Forum
Could you upload a sample of your workbook, with code, so we could take a look at it! It will be easier to find a solution!

[mackinlay]

here is an extremely reduced version of the workbook because of the file size limit i had to cut a load out! The only real difference though is that there are only a couple of options for the list, rather than the 20ish in the main workbook for both horizontal & vertical!

Cheers!

[Aj_old]

Is possible to use something else, beside Checkboxes?
In which order should data be pasted in the new worksheet, in the order they are checked or in other order?

[mackinlay]

yeah, check-boxes are not essential, just something equivalent is needed(preferably not having to type any thing or a drop down menu, but drop downs will do if necessary!)

It is the order that they are clicked on the dictates the order on the second list.

Thanks!

[Dirizhor]

How about a Y for yes and N for no..
You can sort it with y or n and go ahead with it

[Aj_old]

Try this one!

[mackinlay]

Cheers AJ, it works really well. But I'm having a bit of trouble interpreting the code so that I can apply it to the entire workbook I have here. The 'makecheckboxes' section:

when you call it:

   Call makechekboxes(nr, 4, 2, shSh1)

and what is called:

Sub makechekboxes(nr, fistrw, Column, shsheet)
    For i = 1 To nr
        With shsheet.CheckBoxes.Add(406.5, 350.25, 72, 72)
            .Name = "Waw"
 
    Next
End Sub

I dont understand what the various numbers and abbreviations stand for. If you could explain this to me I would be VERY grateful!!

Thanks!!

[Aj_old]

Originally Posted by mackinlay

Cheers AJ, it works really well. But I'm having a bit of trouble interpreting the code so that I can apply it to the entire workbook I have here. The 'makecheckboxes' section:

I tried to make a version with checkboxes, but, I didn't had enough time, so I made the version I posted, but forgot to remove these lines!

As for the code:

Sub makechekboxes(nr, fistrw, Column, shsheet)
    For i = 1 To nr
        With shsheet.CheckBoxes.Add(406.5, 350.25, 72, 72)
            .Name = "Waw"
 
    Next
End Sub

First to numbers refferes to the position of the checkbox, in the worksheet, and the second to it's dimensions (if I remember right)

[mackinlay]

so is it possible for me to update the code you've provided to add the checkboxes in? It appears that the majority of it is already in place?!

[Aj_old]

One question.
What should happen when the checkbox (suppose you have checkboxes) is unchecked?

[mackinlay]

On the 'inital check list' sheet, I really like the fact that currently, when you choose true the item is pasted and then when you choose false it disappears. So when the checkbox is unchecked, i would prefer it if the pasted items were removed.

Is that what you were asking?!

[Aj_old]

Yes it is!

In this case when the chekbox on the 'inital check list' sheet, you need to create a new set of checkboxe (one for each line of text which will be pasted), and remove them when it's uncheked!
It's a little more difficult whit the second part, thats why I preferred the True/False combo!

[mackinlay]

yeah, the true/false seems to work really well in the sheet you sent. Its not the end of the world if the checkbox idea doesnt come off, it is just a much more physical selection of which items in the list are required!