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]

For your first ptoblem try:

Option Explicit

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
    Else
        Select Case Target.Address
        Case "$A$3": Builder "B"
        Case "$A$4": Builder "C"
        Case "$A$5": Builder "D"
        '
        '
        '
        Case Else: MsgBox "Address not in Case routine."
        End Select
    End If
End Sub

 

[largeselection]

Hi Bill,

Thanks for your help! I added the option explicit statement, but it didn't help my problem. Sorry for the confusing post, but I'm not having an issue with the first part of code. I can click on a cell and have it run the "builder" code successfully. The issue I'm having is that if I use my "selectvisible" code to check off a range of visible cells I can't figure out how to get it to run the builder code for the cells that have been selected as a result of the "selectvisible" code.

Thanks for your prompt response!

 

[Bill Rockenbach]

Option Explicit just forces you to Dimension all your variables

here is the code revised for you SelectVisible

Sub SelectVisible()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'add check marks
    With Range("A3:A4083").SpecialCells(xlCellTypeVisible)  
        .Formula = "=CHAR(252)"
        .Value = .Value
        With .Font
            .Name = "Wingdings"
            .FontStyle = "Bold"
            .Size = 8
        End With
    End With
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

With the two sets of code I provided your Builder subroutine will be called if you select a checked cell.

I can't figure out how to get it to run the builder code for the cells that have been selected as a result of the "selectvisible" code.

Do you want the builder code to run automatically on all checked cells after the routine SelectVisible has run or just if a checked cell is selected.

I would use the doubble click evet to run the builder code and not the select routine, its more percise. I would also limit the range of cells that triggeres the builder code.

 

[Bill Rockenbach]

Thanks for your help! I added the option explicit statement, but it didn't help my problem.

Did you just add the Option Explicit statment to your old code or did you copy my code in as is?

 

[largeselection]

I put option explicit in and used your code. How can I get this to either run as I make each checkmark or run for all checked at the end (by clicking a button or something). As it stands now with the code you've given me, I can make checkmarks by clicking on the cells, but nothing is added to the report (via the BUILDER code) until I click on the checkmarked cell again.

Similarly, I can run the "selectvisible" code and it will place checkmarks into the cells, but I have to still click on each checked cell in order for it to run the "builder" code.

 

[largeselection]

I tried removing the else and now the "BUILDER" code will run on each checkmark as I make them just as before, but "BUILDER" still won't activate when I use the "selectvisible" code.

 

[Bill Rockenbach]

Bulider code will run after check mark entered by seletion change event

Option Explicit
 
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
        Select Case Target.Address
        Case "$A$3": Builder "B"
        Case "$A$4": Builder "C"
        Case "$A$5": Builder "D"
        '
        '
        '
        Case Else: MsgBox "Address not in Case routine."
        End Select
    End If
End Sub

Builder code will run for every checkark entered by the SelectVisible procedure

Sub SelectVisible()
    Dim c As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    'add check marks
    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.Address
        Case "$A$3": Builder "B"
        Case "$A$4": Builder "C"
        Case "$A$5": Builder "D"
            '
            '
            '
        Case Else: MsgBox "Address not in Case routine."
        End Select
    Next c
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[largeselection]

Thanks Bill! It's so close. Only issue I have remaining is that I have too many cases. I got around this issue the first time (for the individual checks) by putting more cases into different private sub "parts". Then at the end of the first "Worksheet_selectionchange" I have call Part1, call Part 2, call Part 3.

I tried applying the similar concept to the second code you gave me (for the "selectvisible" code) and I had a lot of duplicate columns after the Builder code ran.

 

[largeselection]

It repeats the columns in the partX that I Call. So for example, the select visible action will select A1, A5, A10, A15, A20. In this example, A15 and A20 are part of the second called part. So after running "selectvisible" my report sheet has A1,A5,A10, A15, A20, A15, A20.
I end the "selectvisible" code with:

Case Else
Call PartA
 
end select
 
next c
 
application.enableevents=true
application.screenupdating=true
 
end sub

This is the code for "PartA"

Private Sub partA()

Dim c As Range
For Each c In Range("A3:A4083").SpecialCells(xlCellTypeVisible)
Select Case c.Address
        
Case "$A$203"
Builder "GT"
Case "$A$204"
Builder "GU"
.
.
.
 
end select
 
next c
 
end sub