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.

 

[Bill Rockenbach]

I didn't know the caser statement had a limit on "cases". Mybe sub cases would work
(Not trested)

For Each c In Range("A3:A4083").SpecialCells(xlCellTypeVisible)
    Select Case c.Row
    Case 3 To 10
        Select Case c.addres
        Case "$A$3": Builder "B"
        Case "$A$4": Builder "C"
        Case "$A$5": Builder "D"
            '
            '
            '
        End Select
        
    Case 11 - 20
        Select Case c.Address
        Case "$A$3": Builder "B"
        Case "$A$4": Builder "C"
        Case "$A$5": Builder "D"
            '
            '
            '
        End Select
        
    Case 21 - 30
        Select Case c.Address
        Case "$A$3": Builder "B"
        Case "$A$4": Builder "C"
        Case "$A$5": Builder "D"
            '
            '
            '
        End Select
        
    Case Else: MsgBox "Address not in Case routine."
    End Select
Next c

You confused me with your last post

So after running "selectvisible" my report sheet has A1,A5,A10, A15, A20, A15, A20.

But you rever to partA routine

Select Case c.Address
        
Case "$A$203"
Builder "GT"
Case "$A$204"
Builder "GU"

Can you provide sample code of one of your build routines?

 

[largeselection]

Hi Bill,

The subcases did not work out, I still got a procedure too large error. Here is the code I am working with:
BUILDER CODE:

Sub Builder(MyHeader As String)
    Dim MyColumn As Integer
    With Worksheets("DATA")
        MyColumn = .Rows(1).Find(What:=MyHeader, After:=.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Column
        .Columns(MyColumn).Copy Worksheets("REPORT").Range("A1").End(xlToRight).Offset(0, 1)
    End With
End Sub

CHECKMARK CODE:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    If target.Column > 1 Then Exit Sub
    If target.Row < 3 Then Exit Sub
    If target.Row > 4084 Then Exit Sub
    If target.Count > 1 Then Exit Sub
    If IsEmpty(target) Then
        target.Formula = "=CHAR(252)"
        target.Value = target.Value
        With target.Font
            .Name = "Wingdings"
            .FontStyle = "Bold"
            .Size = 8
        End With
        Select Case target.Address
Case "$A$3"
Builder "B"
Case "$A$4"
Builder "C"
Case "$A$5"
Builder "D"
.
.
.
end select
end if
 
call part 2(target)
call part 3(target)
call part 4(target)
.
.
.
call part 21(target)
end sub

EXAMPLE PART2 CODE:

Private Sub part2(ByVal target As Range)
If Not IsEmpty(target) Then
        Select Case target.Address
        
Case "$A$203"
Builder "GT"
Case "$A$204"
Builder "GU"
.
.
.

CURRENT SELECT VISIBLE CODE:

Sub SelectVisible()
Dim c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
With Range("A3:A4083").SpecialCells(xlCellTypeVisible)
    .Formula = "=CHAR(252)"
    .Value = .Value
    With .Font
        .Name = "Wingdings"
        .FontStyle = "Bold"
        .Size = 8
    End With
End With
For Each c In Range("A3:A4083").SpecialCells(xlCellTypeVisible)
    Select Case c.Row
    Case 1 - 1000
    Select Case c.Address
    
Case "$A$3"
Builder "B"
Case "$A$4"
Builder "C"
.
.
.
etc

Hope that helps and thanks for all of your help with this issue.

 

[Bill Rockenbach]

How many build routines do you have?

Is this a typical Builder procedure?

Sub Builder(MyHeader As String)
    Dim MyColumn As Integer
    With Worksheets("DATA")
        MyColumn = .Rows(1).Find(What:=MyHeader, After:=.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Column
        .Columns(MyColumn).Copy Worksheets("REPORT").Range("A1").End(xlToRight).Offset(0, 1)
    End With
End Sub

Can you give an overview of this project if it's not convidential?

 

[Bill Rockenbach]

Looks like you are copying in a master column into a report. I can't imagine why you have so many. Is therre a way to combine some of this copying instaed of coping one column at a time.

Instead of copying, can you build the formates in a process that using variables passed to it?

How many columns does a typical user need to build a report?

 

[largeselection]

Hm. Perhaps you are right and there are too many column options. For now, I will tailor it down to the max # allowed columns while I try and figure out how to add additional length. I started with approx 4000 columns, just so that I could support that many if necessary (since 2500 is not so far fetched a need).

I will think about it and see if there is a way I can think of to add length to this part. I'll also see if there is perhaps a more effective way to approach this whole project.

 

[Bill Rockenbach]

My but that's a lot of columns. Again, I can't imagin why you would have so many differently formated columns.

Seams like you should be able to construct the column format on the fly instead of creating 4,000 column samples to be copied.

Or maybe a few master columns that could be copied in and then modified by the procedure.

can you post a few samples?

 

[largeselection]

? The columns all have the same formatting. They just contain different data. So far this has been the most effective way that I've found to be able to select which data columns I want to add to a report from a front sheet.

It's not that bizarre to have so many columns (4000+) with different data, so this is just a way so I can put the columns I want next to each other without having to always copy/paste them from my master database.

 

[Bill Rockenbach]

If teh columns are formated the same why not just have one master formatted with many columns and in one shot copy x amont of columns. and have the data that goes in teh columns in a table (if maybe Access, then read the table write to the columns.

The check boxes could be accumlated in an array and a loop would read through the array and populate the columns with the data from the table.

 

[largeselection]

Not sure if I'm reading what you wrote correctly. I had a different one that would add the columns that were selected all at once rather than each check.

That was ok, but it didn't allow flexibility in column position.

Here's an overview of what I've tried to do here:
I have reports that I get monthly that have approx 60 columns x 600 rows. I want to be able to easily see elements next to each other chronologically.

So as I have it now, I can just paste in the report, hit a button, and it will do a lookup so that the data corresponds to the correct row (this is necessary since the rows are not always in the same position month to month).

So my database after doing this once would have 60 columns of data for January by 600 rows of names + 60 columns of data for February + march, etc. So on the first sheet I can select which columns I want to copy and paste to a report so I can see which of the columns I want to see next to each other.

So I can select on the first sheet by clicking and get "Height Feb", "Height March", "Height Jan", "Weight Feb", "Weight March", "Weight Jan". Then in my report I can quickly see Height month to month and weight month to month by name (in the rows). I do this so I don't have to keep doing lookups and/or hiding columns/moving columns in my huge database.

I do this so that if I have 12 reports that I've put together by month (720 columns since 60 data measures/report). I can quickly and easily see which measures I want to see. So if I want to see weight by month, I can just click those and I get a report that shows the 12 weight columns next to each other, rather than having to find them in my built database and copy/paste them myself. And if I want to see weight then height by month then I can see that how I select them so I can select them by checking them off and see jan weight, jan height, feb weight, feb height, etc or I can check them off in a different order and see jan weight, feb weight, jan height, feb height, etc.

 

[Bill Rockenbach]

Not sure if I'm reading what you wrote correctly. I had a different one that would add the columns that were selected all at once rather than each check.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
My comments don’t make sense because I thought your copying template “columns” with not data in them and then inserting raw data into the formatted columns. From your notes now it appears your database has format columns and your copying those into a report with the data in them. Is this correct?


I have reports that I get monthly that have approx 60 columns x 600 rows. I want to be able to easily see elements next to each other chronologically. <o></o>
How many reports do you get each month and do you need to merge any of the individual monthly reports?<o></o>
Are the monthly reports over the year kept in separate databases?<o></o>
<o></o>
Is the maximum columns in the database = 12 x 60 = 720 ?<o></o>

(this is necessary since the rows are not always in the same position month to month).<o></o>
Do you have to sink the rows up by name? If so this puts empty rows in some months.

I do this so that if I have 12 reports that I've put together by month (720 columns since 60 data measures/report). But how many columns would you have in one compiled finished rport??<o></o>
<o></o>
You really don’t have a database, but a giant report which your ripping pieces out of to form other reports.<o></o>
<o></o>
An Excel database would have columns for report type, year, month and your 60 columns. This would avoid empty rows.<o></o>
<o></o>
The trade off’s are, the true database is more flexible as any formatted report could be built vs your “giant report” has but one format although you can be selective on columns. As once told to me "There is a reason why Excel has more rows than columns."

Your method is simpler and if all you ever need is the report format you have developed then it should work out fine. Just need the most efficient way to select the columns out.
<o></o>
<o></o>
If you would kindly answer the questions above I think I have a way to get around the select case limits. What would be the maximum columns ever copied into a report?<o></o>
<o></o>
Typical Database

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 78px"><COL style="WIDTH: 70px"><COL style="WIDTH: 73px"><COL style="WIDTH: 69px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 106px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Rpt Type</TD><TD style="TEXT-ALIGN: center">Year</TD><TD style="TEXT-ALIGN: center">Month</TD><TD style="TEXT-ALIGN: center">Name</TD><TD style="TEXT-ALIGN: center">Height</TD><TD style="TEXT-ALIGN: center">Weight</TD><TD style="TEXT-ALIGN: center">60 More Col >>></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Report1</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Jan</TD><TD style="TEXT-ALIGN: center">Name1</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Report1</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Jan</TD><TD style="TEXT-ALIGN: center">Name2</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Report1</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Jan</TD><TD style="TEXT-ALIGN: center">Name3</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Report1</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Jan</TD><TD style="TEXT-ALIGN: center">Name4</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">600 rows</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Report1</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Feb</TD><TD style="TEXT-ALIGN: center">Name1</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Report1</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Feb</TD><TD style="TEXT-ALIGN: center">Name2</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Report1</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Feb</TD><TD style="TEXT-ALIGN: center">Name3</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Report1</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Feb</TD><TD style="TEXT-ALIGN: center">Name4</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">600 rows</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left">Rest of months</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>Report2</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Month</TD><TD style="TEXT-ALIGN: center">Name</TD><TD style="TEXT-ALIGN: center">Height</TD><TD style="TEXT-ALIGN: center">Weight</TD><TD style="TEXT-ALIGN: center">60 More Col >>></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>Report2</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Jan</TD><TD style="TEXT-ALIGN: center">Name1</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>Report2</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Jan</TD><TD style="TEXT-ALIGN: center">Name2</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Report2</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Jan</TD><TD style="TEXT-ALIGN: center">Name3</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">600 rows</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>Report2</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Feb</TD><TD style="TEXT-ALIGN: center">Name1</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>Report2</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Feb</TD><TD style="TEXT-ALIGN: center">Name2</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>Report2</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Feb</TD><TD style="TEXT-ALIGN: center">Name3</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>Report2</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">Feb</TD><TD style="TEXT-ALIGN: center">Name4</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">600 rows</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: left">Rest of months</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD>Rest of reports types</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

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