selection.change event refuses to execute

[littlepete]

hello

I have an excel sheet to type in my lottery numbers ...
all works excellent, but i'm trying to 1. clear all data and 2. start the check the numbers macro with an selection.change event...
selected from the macro list, both macros work as they should...

the macro to clear all data is on range c3:f3, named range "knopvoor" (preparation)
the macro to check all the numbers is on range h3:k3, named range "knoptrek" (choose)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("knopvoor")) Is Nothing Then
            Call voorbereiding
        End If
        If Not Intersect(Target, Range("knoptrek")) Is Nothing Then
            Call trekking
        End If
    End If
End Sub

what could I be doing wrong ?
thanks for the help !

 

[johnnyL]

Do you have that code in a sheet module or a separate module? The separate module is the wrong choice.

 

[littlepete]

Do you have that code in a sheet module or a separate module? The separate module is the wrong choice.

hello

that sub... end sub is placed in sheet one called trekking...
the only thing i doubted about was the merged cells: the two buttons to click are both a merge of four cells
knopvoor = c3:f3 and knoptrek = h3:k3
but also without merged cells no result; also with the cells and not named range no result...

 

[MARK858]

It is probably not getting past the

If Selection.Count = 1 Then

line.

What are you trying to test with that line?

the two buttons to click are both a merge of four cells

What 2 buttons?

 

[Alex Blakenburg]

Instead of If Selection.Count = 1 Then, you might be better off with
If Target.MergeCells Then

But as @MARK858 alluded to, why are using a SelectionChange event using pretend buttons based on Merged Cells ?
Selection change is going to run every single time you move from one cell to another.
If you want a button anyway just create a button linked to the procedure you are calling anyway.That way it will only run when you click on the button.

 

[littlepete]

so, can i ask, without creating a button, but with an event, what should i put in the sheet code?
there is a choice:
1. clicking a cell to start the macro; or:
2. starting the lottery calculation after filling in the seventh number...

what should i write then?

answers:
the two "buttons": are the cells on the picture left top corner (cells in row 3).

i guessed the event would only work when i click one of those two cells, not selecting any cell ?

 

[Alex Blakenburg]

with an event, what should i put in the sheet code?
there is a choice:
1. clicking a cell to start the macro;

I can't see the benefit in having a Cell (pretend) button over a real button (actually I can only see disadvantages) having said that just changing your if statement as previously advised should work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If If Target.MergeCells Then              ' Changed Line
        If Not Intersect(Target, Range("knopvoor")) Is Nothing Then
            Call voorbereiding
        End If
        If Not Intersect(Target, Range("knoptrek")) Is Nothing Then
            Call trekking
        End If
    End If
End Sub

2. starting the lottery calculation after filling in the seventh number...

Instead of SelectionChange use Worksheet_Change.
Check for an intersect with any 1 of the 7 (merged) entry cells.
Then count all 7 entry cells for numerics and if it comes to 7 execute the code.

 

[littlepete]

It is probably not getting past the

If Selection.Count = 1 Then

line.

What are you trying to test with that line?


What 2 buttons?

yeahhh !

removing the selection 1 line did the job !!! thank you for the suggestions !!!

now, next job: win the lottery hahaha !!!

 

[Alex Blakenburg]

now, next job: win the lottery hahaha !!!

Haha, I was eyeing off that € 100k you kept dangling in front of us. ?

 

[littlepete]

Haha, I was eyeing off that € 100k you kept dangling in front of us. ?

i'll send you 10% next time i win, let's see: € 1,25 / 10 = ... too much trouble hahahaha !