Excel 2007 checkbox on row to print to label sheet

[MSealy]

Hi,

I have a database sheet 'Data', where I wish to add 4 buttons/checkboxes to each row of data. The idea being that I can check only 1 of the 4 per row, up to any 4 rows and this data is then passed to my other sheet, 'Labels'. The label sheet contains four spine labels based on L7171 Avery spine labels for folders.

With this I can print any 4 spine labels without wastage. The trouble is it seems very complex and I'm not sure if I need command buttons and or tickboxes.

I have some code based on an old sheet where I made a command button to print one line of data in this sheet to a another sheet that acted as a form. It went like this..

Private Sub PrintForm_Click()
With ActiveSheet
.Range(.Cells(ActiveCell.Row, "A"), _
.Cells(ActiveCell.Row, "K")).Copy _
Worksheets("calc").Cells( _
Rows.Count, 1).End(xlUp)(1)
End With
Sheets("Form").PrintOut
End Sub

This worked well, but how to modify this for what I'm trying to do now is beyond me. Could anyone please give me any pointers/suggestions?

Many thanks,


Mark.

 

[Gruuuu]

Mark, why not just link the check boxes to cells, and use that box (which will show up as TRUE or FALSE) to build out a string for the label?

Unless my interpretation is incorrect.

If you're instead looking for a contiguous list of items that you check off, you can start by doing the same thing I mentioned above, but numbering your labels as they are checked. Just something like "=MAX(all cells above this one)+1"

And then do a lookup on your label printout sheet.

 

[MSealy]

Thanks for that. I'd perhaps explain better what I'm trying to do as it's a bit fiddly.

Unfortunately I can't copy a row because I'm using two separate colums of data on the page one is 1-50, the other is 51-100. These represent box files.

A couple of row examples from 'Data'
File TransFrom TransTo Label File TransFrom TransTo Label
1 1 233 YNNN 51 3298 3456 NNNN
2 234 680 NYNN 52 3457 4879 NNNN

In the 'Calc' sheet I've put 4 rows. The 4 labels in 'Labels' are linked to the value of the 4 rows. So far so good.

The YNNN part represents the empty cells on my page, where either a tickbox (eg shown the Y) or macro button clicked will copy the 3 cells to the left and paste them to the row in 'Calc'.

This way any line of my data can be copied into any label depending on which box I tick. So in this example, Files 1 & 2 will show on labels 1 & 2 but 3 & 4 will be blank.

Being that is so long since I've done any macro programming, I'm trying to do this by formula using the method:
"IF Box1 is ticked, COPY Data!A1:A3 to Calc!A1:A3, ELSE don't"
"IF Box2 is ticked instead, COPY Data!A1:A3 to Calc!B1:B3, ELSE don't"

The trouble is with the examples I've seen online, trying to enter this into one of the tickbox cells waon't work because as soon as I enter the Y or tick it overwrites the formula or I get into the territory of circular references.

Something tells me I can only achieve this with a macro, although Excel should be able to copy cells with a formula??

 

[Gruuuu]

Ok, let me paraphrase to make sure I understand:

You have 3 sheets that are involved here:
1st sheet: 4 different labels, numbered 1-4
2nd sheet: 2 columns of 50 items each, with the option to select one of the four labels for each item
3rd sheet: a list of labels, selected from the four on the first sheet, according to the selections from the 2nd sheet, listed in order. 1-100 skipping items that have no label selected (so if your 2nd sheet only had the data listed above, you would have a list on the 3rd sheet that showed the first label, the second label, and nothing else.)

Does that sound correct?

 

[MSealy]

The descriptions of the first 2 are spot on - only the labels aren't numbered as such - the number is determined by what's selected on the 2nd sheet.

The 3rd sheet has just 4 rows, where the labels take their data from. The idea being that whatever 4 boxes are ticked on the 2nd sheet will copy the data into those 4 rows on the 3rd, which the 1st will pick up.

I've got the basic structure in place, and I think it'll work this way but I'm having difficulty with the programming side.

 

[Gruuuu]

Ok so page 1 is to have an output of 4 labels, which display information based on 4 items on the page 2 that have been checked, and the check selection determines what the label sequence is?

So, if on the second page I had:
[tt]
File TransFrom TransTo Label File TransFrom TransTo Label
1 1 233 [red]Y[/red]NNN 51 3298 3456 NNNN
2 234 680 N[red]Y[/red]NN 52 3457 4879 NNNN
3 681 1127 NNNN 53 4880 5160 NNN[red]Y[/red]
4 1128 1261 NNNN 54 5161 5542 NN[red]Y[/red]N
... ...
[/tt]

Then the output on Page 1 would be something like:
[tt]
Label 1
File: 1 TransFrom: 1 TransTo: 233
Other Pertinent Info: XXXXXXXXXXX

Label 2
File: 2 TransFrom: 234 TransTo: 680
Other Pertinent Info: XXXXXXXXXXXXX

Label 3
File: 54 TransFrom: 5161 TransTo: 5542
Other Pertinent Info: XXXXXXXXXXXXXXXX

Label 4
File: 53 TransFrom: 4880 TransTo: 5160
Other Pertinent Info: XXXXXXXXXXXXXXXX
[/tt]

Is that correct? (Does the order matter? Should it be sorted? Will you ever need more than 4 labels?)

 

[MSealy]

That is spot on. The ticks determine the position of the labels - this way I can reduce wastage. I'll never need more than 4 labels at a time (L7171 sheets only have 4)

The data will never need sorting as there'll never be more than 100 files per year. Each year I'll simply copy the sheet and start again.

I'll also implement a search feature for the trans. ranges - but that's a separate issue.

 

[Gruuuu]

ok, assuming these columns on your Files page (adjust as necessary):

[tt]
[gray]A B C DEFG H I J KLMN[/gray]
File TransFrom TransTo Label File TransFrom TransTo Label
1 1 233 YNNN 51 3298 3456 NNNN
2 234 680 NYNN 52 3457 4879 NNNN
3 681 1127 NNNN 53 4880 5160 NNNY
4 1128 1261 NNNN 54 5161 5542 NNYN
... ...
[/tt]

and on your Labels page

[tt]
Label 1:
File: |Formula 1| TransFrom: |Formula 2| TransTo: |Formula 3|
Other Pertinent Info: XXXXXXXXXXX

Formula 1 =IF(ISNA(INDEX($A:$A,MATCH(TRUE,$D:$D,0))),INDEX($H:$H,MATCH(TRUE,$K:$K,0)))
Formula 2 =IF(ISNA(INDEX($B:$B,MATCH(TRUE,$D:$D,0))),INDEX($I:$I,MATCH(TRUE,$K:$K,0)))
Formula 3 =IF(ISNA(INDEX($C:$C,MATCH(TRUE,$D:$D,0))),INDEX($J:$J,MATCH(TRUE,$K:$K,0)))

Label 2:
File: |Formula 4| TransFrom: |Formula 5| TransTo: |Formula 6|
Other Pertinent Info: XXXXXXXXXXX

Formula 4 =IF(ISNA(INDEX($A:$A,MATCH(TRUE,$E:$E,0))),INDEX($H:$H,MATCH(TRUE,$L:$L,0)))
Formula 5 =IF(ISNA(INDEX($B:$B,MATCH(TRUE,$E:$E,0))),INDEX($I:$I,MATCH(TRUE,$L:$L,0)))
Formula 6 =IF(ISNA(INDEX($C:$C,MATCH(TRUE,$E:$E,0))),INDEX($J:$J,MATCH(TRUE,$L:$L,0)))

Label 3:
File: |Formula 7| TransFrom: |Formula 8| TransTo: |Formula 9|
Other Pertinent Info: XXXXXXXXXXX

Formula 7 =IF(ISNA(INDEX($A:$A,MATCH(TRUE,$F:$F,0))),INDEX($H:$H,MATCH(TRUE,$M:$M,0)))
Formula 8 =IF(ISNA(INDEX($B:$B,MATCH(TRUE,$F:$F,0))),INDEX($I:$I,MATCH(TRUE,$M:$M,0)))
Formula 9 =IF(ISNA(INDEX($C:$C,MATCH(TRUE,$F:$F,0))),INDEX($J:$J,MATCH(TRUE,$M:$M,0)))

Label 4:
File: |Formula 10| TransFrom: |Formula 11| TransTo: |Formula 12|
Other Pertinent Info: XXXXXXXXXXX

Formula 10 =IF(ISNA(INDEX($A:$A,MATCH(TRUE,$G:$G,0))),INDEX($H:$H,MATCH(TRUE,$N:$N,0)))
Formula 11 =IF(ISNA(INDEX($B:$B,MATCH(TRUE,$G:$G,0))),INDEX($I:$I,MATCH(TRUE,$N:$N,0)))
Formula 12 =IF(ISNA(INDEX($C:$C,MATCH(TRUE,$G:$G,0))),INDEX($J:$J,MATCH(TRUE,$N:$N,0)))
[/tt]

Truth be told, this isn't the way I would generate the labels, but it seemed the least destructive to your current process.

If you ever want a more seamless and reliable method, just let us know

 

[MSealy]

Thanks for that, I've put the (modified to my positions) formula into cells, and told the labels to refer to those cells but I get "#NA".

Is there a specific place I need to place them?

Thanks.

 

[SkipVought]

That is not very good information if you want a cogent answer.

HOW did you 'tell' the lable? CODE PLEASE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

The value that the formulas are looking for is "TRUE" not "Y".
Make sure you're inserted the checkboxes, and that they are linked to the appropriate cells.

I should have gone into this, but in order to have the linked cells, you will need to insert ActiveX checkboxes. The linkedCell property is set in the property dialog, also found in the Developer tab.

Also please note: If nothing is checked for any one of the four labels, it will return '#N/A'.

 

[MSealy]

Ok. I've amended your code to reflect the data on my sheet. There is a frozen pane area above where this starts which contains data that is static on the Labels sheet. This is why it starts at A16. I did that to avoid repetition of data.

A D G JKLM N Q T WXYZ
15 File TransFrom TransTo Label File TransFrom TransTo Label
16 1 1 233 YNNN 51 3298 3456 NNNN
17 2 234 680 NYNN 52 3457 4879 NNNN
18 3 681 1127 NNNN 53 4880 5160 NNNY
19 4 1128 1261 NNNN 54 5161 5542 NNYN

And so the first pat of the formula code would now be:

Formula 1 =IF(ISNA(INDEX($A:$A,MATCH(TRUE,$J:$J,0))),INDEX($N:$N,MATCH(TRUE,$W:$W,0)))
Formula 2 =IF(ISNA(INDEX($D:$D,MATCH(TRUE,$J:$J,0))),INDEX($Q:$Q,MATCH(TRUE,$W:$W,0)))
Formula 3 =IF(ISNA(INDEX($G:$G,MATCH(TRUE,$J:$J,0))),INDEX($T:$T,MATCH(TRUE,$W:$W,0)))

So far I've added 4 checkboxes, and linked them to J16,K16,L16 & M16, and positioned them over those cells. I'm trying to grasp where I place the formulas before I copy and link those 4 checkboxes to the other 99 files.

I can understand that on the labels sheet, some cells on the label will refer to "=[Formula1]" etc, but as for where to place the written formula itself I can't work out. Do I replicate all 12 forumlas in the VB properties of each checkbox?

Sorry if I'm sounding a bit thick. It's been a long time since I've dabbled in coding so I'm very rusty.

Thanks.

 

[Gruuuu]

Ah, I see. Yes my formulas were incorrect, which is why things are so confusing.

Ok two corrections. The first one I'm sure you already guessed:
Need to specify the sheet. I just wasn't paying attention on that one.
Second: My IF statement is dead wrong. It basically says
IF(TEST IS FALSE, DO THIS) (what if test is true?)
[tt]
Formula 1 again
=IF(ISNA(INDEX(Sheet2!$A:$A,MATCH(TRUE,Sheet2!$D:$D,0))),INDEX(Sheet2!$H:$H,MATCH(TRUE,Sheet2!$K:$K,0)),INDEX(Sheet2!$A:$A,MATCH(TRUE,Sheet2!$D:$D,0)))
[/tt]

You can observe that I'm just repeating the INDEX(,MATCH()) formula in the second part of the IF statement. You would have to apply this same pattern to the rest of the formulas above.

I know that this is a huge pain in the butt to set up. But like I said before, it's the least destructive to your process.
It would also be a bear to troubleshoot if something changed (like the number of files or labels)

Not to mention the incredible tedium of setting up 400 checkboxes and linking them to cells.

So, if you aren't in any rush to get this set up, I would recommend thinking about different ways to do this to tighten up the efficiency.

 

[MSealy]

I think I'm going to have to, as the 400 textboxes will be a nightmare especailly as I just can't understand how to relate the file fields to the fields in my labels. Whereever I put the formula I get one result which is either "NA" or "0"

I think I'm going to have to push for the case of keeping MS Access and seeing if I can figure out the checkbox/label system there.

 

[MSealy]

I've cracked it - I was confused until I realised the references were pointing to the original set. It works now - great.

Rather than place the formulas into the labels, I've put them on a separate sheet and linked the labels to them - in case I need to change one for whatever reason - in which case I only lose the cell ref, and not the formula.

Thanks again.

 

[Gruuuu]

As long as it works!
Glad you got it working the way you like.