Using Selection with isempty

[largeselection]

I'm having some trouble getting a private sub to initiate and I'm sure it's because I don't have a target specified.

The first part of code puts a checkmark into a cell if it is clicked and that initiates another code called "BUILDER":

Private sub worksheet_selectionchange(ByVal target As Range)
If IsEmpty(target) Then
        target.Formula = "=CHAR(252)"
        target.Value = target.Value
        With target.Font
            .Name = "Wingdings"
            .FontStyle = "Bold"
            .Size = 8
        End With
 
If Not IsEmpty(target) Then
        Select Case target.Address
Case "$A$3"
Builder "B"

Then there are a lot of "cases". I have another sub which is assigned to a button which basically puts a checkmark in the cells of a selected range if they are visible and the issue is that even though the checkmarks appear, I can't get them to register as a "case" and thus launch the "BUILDER" code. After some reseach I'm sure it is because there is no target specified in my "selectvisible" code. So I can't Call/run/ etc the worksheet_selectionchange code since it doesn't know which targets. So how can I either specify my selection should equal targets? Or can I use something like if not isempty(selection) then select case selection.address?

Here is the code for the selectvisible:

Sub SelectVisible()
Range("A3:A4083").Select
selection.SpecialCells(xlCellTypeVisible).Select
    selection.Formula = "=CHAR(252)"
    selection.Value = selection.Value
    With selection.Font
        .Name = "Wingdings"
        .FontStyle = "Bold"
        .Size = 8
    End With

PS- All Code is located in the Sheet1 Code.

 

[largeselection]

You are right! Sorry about my failure to use correct terminology...one of these days I'll get it.

I do have one giant report and I am ripping pieces off of it to create smaller reports. The smaller reports are much easier to analyze and look at, and this seems to be the fastest way to see what I want to see on the fly.

I could take the reports that I get and put them into a database as you outlined, but then if I want to analyze the data I have to make a pivot table and I don't have as much flexibility with regard to column placement so usually in the past if I've done this I end up having to value out the pivot table and then manually rearranging things.

-The rows do sync up by name or ID number so that's why in my current version it does an index/match when it appends a new report.

-In a finished report (the smaller pieces that are ripped off the giant report) I would probably have 60-70 columns only.

I see the pros+cons to both methods. So it really is about the best way to efficiently select out the columns that I want to rip out of the giant report.

 

[Bill Rockenbach]

getting late here, so tomorow I'l take a look at my ideas and then share.

I know you have a sheet that you seelct the columns on for the reports. How is it laid out?

 

[largeselection]

Here is the master report (the giant one):

Excel Workbook
	A	B	C	D	E	F	G	H
1		A	B	C	D	E	F	G
2		LABEL 1	LABEL 4	LABEL 7	LABEL 10	LABEL 13	LABEL 16	LABEL 19
3		LABEL 2	LABEL 5	LABEL 8	LABEL 11	LABEL 14	LABEL 17	LABEL 20
4		LABEL 3	LABEL 6	LABEL 9	LABEL 12	LABEL 15	LABEL 18	LABEL 21
5	1	DATA	DATA	DATA	DATA	DATA	DATA	DATA
6	2	DATA	DATA	DATA	DATA	DATA	DATA	DATA
7	3	DATA	DATA	DATA	DATA	DATA	DATA	DATA
8	4	DATA	DATA	DATA	DATA	DATA	DATA	DATA
9	5	DATA	DATA	DATA	DATA	DATA	DATA	DATA
10	6	DATA	DATA	DATA	DATA	DATA	DATA	DATA
11	7	DATA	DATA	DATA	DATA	DATA	DATA	DATA
12	8	DATA	DATA	DATA	DATA	DATA	DATA	DATA
13	9	DATA	DATA	DATA	DATA	DATA	DATA	DATA
14	10	DATA	DATA	DATA	DATA	DATA	DATA	DATA
Sheet3

Here is the mini-report generated by copying columns from the giant report:

Excel Workbook
	A	B	C	D
16		A	C	E
17		copied label 1	LABEL 7	LABEL 13
18		copied label 2	LABEL 8	LABEL 14
19		copied label 3	LABEL 9	LABEL 15
20	1	Copied Data col A	Copied Data col C	Copied Data col E
21	2	Copied Data col A	Copied Data col C	Copied Data col E
22	3	Copied Data col A	Copied Data col C	Copied Data col E
23	4	Copied Data col A	Copied Data col C	Copied Data col E
24	5	Copied Data col A	Copied Data col C	Copied Data col E
25	6	Copied Data col A	Copied Data col C	Copied Data col E
26	7	Copied Data col A	Copied Data col C	Copied Data col E
27	8	Copied Data col A	Copied Data col C	Copied Data col E
28	9	Copied Data col A	Copied Data col C	Copied Data col E
29	10	Copied Data col A	Copied Data col C	Copied Data col E
Sheet3

Here is the sheet where I can select what columns I want to add. It has formulas in which reference the locations of the column labels in the database so that the labels can be flexible if the reports change:

Excel Workbook
	A	B	C	D
32	CheckMark Column	LABEL 1 Column	LABEL 2 Column	LABEL 3 Column
33	X	=reference to Label 1	=reference to Label 2	=reference to label 3
34	X	=reference to Label 4	=reference to Label 5	=reference to label 6
Sheet3

 

[Bill Rockenbach]

Are these 3 statements true?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
1. The Letters in row one of the DATA sheet are the letters that the build code uses to find the column to copy to the REPORT.
<o></o>
2. A check mark in column A of the selection form is associated with the Letter s in row one of the DATA sheet via the Case statement.
<o></o>
3. So you either can build the REPORT one column at a time as you enter check marks or have a program that reads all the check marks building the REPORT as it reads through the column of check marks.
<o></o>
In the selection form do you have a row for every column in the DATA sheet or just a row for every column in the REPORT?
<o></o>
How do you determine the order of the columns copied from the DATA sheet to the REPORT?
<o></o>
How do you determine which label is used in the REPORT or do you always use three labels?
<o></o>
This may seem simple, but why not insert a column in the selecting for and enter the column Letter in it.
<o></o>
Then you would just pass Target.offset(0,1) to the build program. No test code like the Case statement needed.

Excel Workbook
	A	B	C	D	E
1	CheckMark Column	DATA Column	LABEL 1 Column	LABEL 2 Column	LABEL 3 Column
2	X	B	=reference to Label 1	=reference to Label 2	=reference to label 3
3	X	AA	=reference to Label 4	=reference to Label 5	=reference to label 6
Sheet1

<o></o>
<o></o>

 

[Bill Rockenbach]

Here is a format for building the REPORT I was toying with. Not complete by no means, but just a work-in-progress. Each REPORT format could be saved as a template. Teh REPORT would be built all at once by teh clicking a Buid button.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Report Title	ABC Company
2	Report Title	Compare Height and Weight
3			Build
4
5	Database Column Header	Report Column Header	Column Width	Start Report Column	1	2	3	4	5	6	7	8	9	10	11	12	ALL	Starting Month	Report Build Style
6	Height	Height		1														Jan	Every other month
7	Weight	Weight		2														Jan	Every other month
8	Height	Height		13														Jul	Every other month
9	Weight	Weight		14														Jul	Every other month
10	Blank		8	26															Blank Columns
11	Age	Age at Report Date		28														Jan	Every Month
12	DOB	DOB		40														Dec	Freeform
Selecter

<o></o>
As you entered check marks and other data the Column Headings would show up below the entry form in the order they would appear in the REPORT.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
28	Column	1		2		3		4		5		6		7		8
29	DATA Header	Height		Weight		Height		Weight		Height		Weight		Height		Weight
30	Report Header	Height		Weight		Height		Weight		Height		Weight		Height		Weight
31
32	Column	9		10		11		12		13		14		15		16
33	DATA Header	Height		Weight		Height		Weight		Height		Weight		Height		Weight
34	Report Header	Height		Weight		Height		Weight		Height		Weight		Height		Weight
35
36	Column	17		18		19		20		21		22		23		24
37	DATA Header	Height		Weight		Height		Weight		Height		Weight		Height		Weight
38	Report Header	Height		Weight		Height		Weight		Height		Weight		Height		Weight
39
40	Column	25		26		27		28		29		30		21		32
41	DATA Header	(Blank)		Age		Age		Age		Age		Age		Age		Age
42	Report Header			Age @ 12/31		Age @ 12/32		Age @ 12/33		Age @ 12/34		Age @ 12/35		Age @ 12/36		Age @ 12/37
Selecter

<o></o>
<o></o>

 

[largeselection]

See responses in italics. Thanks!

Are these 3 statements true?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
1. The Letters in row one of the DATA sheet are the letters that the build code uses to find the column to copy to the REPORT.
Yes that is how the builder code identifies which column from the DATA (large report) sheet to copy into the REPORT(small report) sheet.

<o></o>
2. A check mark in column A of the selection form is associated with the Letter s in row one of the DATA sheet via the Case statement.
Yes again. the code says to use case target.address and then says for each row in column A, Case "$A$3": Builder "B", Case "$A$4": Builder "C", etc.

<o></o>
3. So you either can build the REPORT one column at a time as you enter check marks or have a program that reads all the check marks building the REPORT as it reads through the column of check marks.
Yes, as it is now it will copy/paste the columns (run the builder code) as each box is clicked (checkmarked). I did have it run at the end by clicking a button until I realized I couldn't get it to add the columns in the order I select them by clicking. It just added them in the order they appeared in the DATA (large report) sheet. I had done that originally by using a long list of If Not isempty($A$3) then Builder "B" ... etc. essentially.

<o></o>
In the selection form do you have a row for every column in the DATA sheet or just a row for every column in the REPORT?
In the selection from I have a row for every column in the DATA sheet. The rows are labeled by formulas which reference the three labels that I put in the columns for each column in the DATA sheet. That way if I change the labels in the DATA sheet, the identifier on the selection sheet will also change.

<o></o>
How do you determine the order of the columns copied from the DATA sheet to the REPORT?
This is determined because it runs after each selection is made. So the order they are added is the order they are selected.

<o></o>
How do you determine which label is used in the REPORT or do you always use three labels?
The labels that are used in the REPORT are copied/pasted from the DATA sheet since the builder code just copies the whole column including the three labels.

<o></o>
This may seem simple, but why not insert a column in the selecting for and enter the column Letter in it.
<o></o>
Then you would just pass Target.offset(0,1) to the build program. No test code like the Case statement needed.
That does seem simpler. Again, I'm pretty new at this so I forget things. So would I just call builder Target.offset(0,1) at the end of the checkmark code?


Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 96px"><COL style="WIDTH: 66px"><COL style="WIDTH: 167px"><COL style="WIDTH: 167px"><COL style="WIDTH: 167px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 53px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; TEXT-ALIGN: center">CheckMark Column</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; TEXT-ALIGN: center">DATA Column</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana">LABEL 1 Column</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana">LABEL 2 Column</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana">LABEL 3 Column</TD></TR><TR style="HEIGHT: 38px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">X</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">B</TD><TD style="FONT-FAMILY: Verdana">=reference to Label 1</TD><TD style="FONT-FAMILY: Verdana">=reference to Label 2</TD><TD style="FONT-FAMILY: Verdana">=reference to label 3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">X</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">AA</TD><TD style="FONT-FAMILY: Verdana">=reference to Label 4</TD><TD style="FONT-FAMILY: Verdana">=reference to Label 5</TD><TD style="FONT-FAMILY: Verdana">=reference to label 6</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4
<o></o>
<o></o>

 

[largeselection]

I love the selection sheet you've made. Much nicer than the one I have. I actually preferred to have it run the report after making all the selections, I just couldn't get around it adding the columns to the REPORT in the order that they appeared in the DATA sheet. So if I wanted to add column A, C, B, D I can now click on the rows corresponding to A, C, B, D in that order and they will add to the REPORT in that order. Before, when I ran it at the end, because all 4 were checked they were added as A, B, C, D since that is how they appear in the DATA sheet.

I'm a little confused at some of the options to select that you have in your selection. So do I have to keep track of which column I want the data to start in? (from the "start report column")

What does "Report Build Style" mean?

LOVE LOVE LOVE the chart that shows in what order the columns will appear. very nice indeed.

 

[Bill Rockenbach]

But, you didn't verify any of the statments or answer any of the questions especially if adding a coulmn with the DATA Column Letter would work for you.

To direct the DATA column to a specific REPORT column you could add another column to indicated the REPORT Column

As far as the form I posted, its a work-in-progrees far from complete. So it would be some time before I could get that working well. And I'll be off line in a few days for over a month. But could get somethng to you that you would have to troublr shoot and cutomize.

 

[largeselection]

I think I did respond to your questions. It's in the post right above my last one. I said I answered in italics, but inadvertently made everything italics. I separated out your questions and my answers are directly beneath. Unless I missed some questions or didn't answer some appropriately.

To answer the one specific one you referenced I could add a column with the target address if that makes it easier. Worst case scenario I could hide that on the selection sheet, but I'm willing to do whatever works best.

 

[Bill Rockenbach]

Sorry, I didn't see your responses, My fault