Worksheet_SelectionChange not firing

GPS72 · Apr 7th 2016

Hi All,

I'm not an experienced programmer and I've been trying to use the code below to prevent users from deleting / adding rows or columns in a particular spreadsheet. However, it is not working at all. I properly inserted the code in the object "Worksheet".

Can you please help?

MANY TKS!

Code

[B]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/B]
[B]If ((Target.Address = Target.EntireRow.Address Or Target.Address = Target.EntireColumn.Address)) Then[/B]


[B]        With Application[/B]
[B]            .EnableEvents = False[/B]
[B]            .Undo[/B]


[B]        MsgBox "No deleting rows or columns", 16[/B]


[B]            .EnableEvents = True[/B]
[B]        End With[/B]




[B]Else[/B]
[B]End If[/B]


[B]End Sub[/B]

Display More

skywriter · Apr 7th 2016

Re: Worksheet_SelectionChange not firing

The selection change event fires when you select a cell or range other than the one that was previously selected.

If A1 is currently the selected cell and I take my mouse and click on cell H1 then the event fires and the target cell is H1.

Are you confusing this event with the worksheet change event which fires when an actual change has been made in a cell.

For example I type "Hello World" in cell A1 and then hit the enter key and the worksheet change event fires and cell A1 is the target cell?

GPS72 · Apr 7th 2016

Re: Worksheet_SelectionChange not firing

Quote from skywriter;768732

The selection change event fires when you select a cell or range other than the one that was previously selected.

If A1 is currently the selected cell and I take my mouse and click on cell H1 then the event fires and the target cell is H1.

Are you confusing this event with the worksheet change event which fires when an actual change has been made in a cell.

For example I type "Hello World" in cell A1 and then hit the enter key and the worksheet change event fires and cell A1 is the target cell?

Display More

It is strange... it work only once and then the following error msg appeared:

"Run-time error '1004': Method 'Undo' of the object '_Application' failed"

Really have no clue of what is going on...

skywriter · Apr 7th 2016

Re: Worksheet_SelectionChange not firing

Quote

Really have no clue of what is going on..

Neither do I because you quoted my whole post, which was unnecessary.

You didn't address what I wrote and then you make a comment about "it" working once and I don't know what "it" is.

I have no idea what code you ran and exactly what you did before the code was triggered and what you expected to happen.

rollis13 · Apr 7th 2016

Re: Worksheet_SelectionChange not firing

I think it shoud be a WorkSheet_Change, not a WorkSheet_SelectionChange event.

Code

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


    If ((Target.Address = Target.EntireRow.Address Or Target.Address = Target.EntireColumn.Address)) Then
        With Application
            .EnableEvents = False
            .Undo
            MsgBox "No deleting rows or columns", 16
            .EnableEvents = True
        End With
    End If
 
End Sub

Display More

Worksheet_SelectionChange not firing

Participate now!

Share