Using Selection with isempty

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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":
Code:
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:
Code:
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,412
For your first ptoblem try:
Code:
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

Active Member
Joined
Aug 4, 2008
Messages
353
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

Well-known Member
Joined
May 4, 2009
Messages
1,412
Option Explicit just forces you to Dimension all your variables

here is the code revised for you SelectVisible
Code:
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

Well-known Member
Joined
May 4, 2009
Messages
1,412

ADVERTISEMENT

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

Active Member
Joined
Aug 4, 2008
Messages
353
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

Active Member
Joined
Aug 4, 2008
Messages
353

ADVERTISEMENT

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

Well-known Member
Joined
May 4, 2009
Messages
1,412
Bulider code will run after check mark entered by seletion change event
Code:
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
Code:
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

Active Member
Joined
Aug 4, 2008
Messages
353
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

Active Member
Joined
Aug 4, 2008
Messages
353
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:
Code:
Case Else
Call PartA
 
end select
 
next c
 
application.enableevents=true
application.screenupdating=true
 
end sub

This is the code for "PartA"
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,691
Messages
5,597,562
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top