Worksheet_SelectionChange

[jstevens]

Is it possible to apply a "SpecialCells" function to an intersect range? I'm getting a Run-time error: Method 'Range' of object '_Worksheet' failed.

Example:

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'If Not Intersect(Range("R_MyRange), Target) Is Nothing Then    'This works without special cells.

    If Not Intersect(Range("R_MyRange").SpecialCells(xlCellTypeVisible), Target) Is Nothing Then

           'Do something
    Else

        'Do Something else

    End If

End Sub

[Doc.AElstein]

Hi John,
As far as I know, there are two possibilities with .SpecialCells(xlCellTypeVisible).
It will either
return a range object
or
it will return an error if it finds no visible cells

If it returns a range object , then I see no reason why it should not work in intersect range.
If you have no visible cells, then .SpecialCells(xlCellTypeVisible) will error, and then it will not work in your code as you cannot intersect an error with a range..

Putting it another way, the syntax for intersect is
____( Range1 , Range2 ……. Etc )
In the brackets you must have range objects. It won’t accept errors
Alan


Alan


Ref
https://www.mrexcel.com/forum/excel-que ... #post99145" onclick="window.open(this.href);return false;

[jstevens]

Alan,

There is at least one row of visible cells so I would agree that it should work.

[Doc.AElstein]

I can’t think of any other reason in itself why it should not work
If you have a file , preferably with reduced data , (desensitized if necessary ) then I can take a look
Alan

[HansV]

It looks like it is not possible to call SpecialCells at all from worksheet event procedures...

[Doc.AElstein]

Strange…. ...... I confess I had never tried using .SpecialCells in any “Events coding” . ( I have never used .SpecialCells(xlCellTypeVisible) either )
I had a little play around.

A couple of comments from after i had a little play about.....
_1) I have not done extensive tests, but at first glance, it seems that .SpecialCells kicks off the Sub Worksheet_SelectionChange( ) event routine.
As long as I wrap any call of SpecialCells in the “.EnableEvents False / True pair “ then it seems that all is well.
As example, In the code below. I do a few things in the Sub Worksheet_SelectionChange( ) , including using SpecialCells to get ranges which include the cell B1.
I then use Intersect with all those ranges to return me the range of cell B1.
It seems to work OK as all calls of SpecialCells are done like
Application.EnableEvents = False
' do SpecialCells stuff
Application.EnableEvents = True
_.__________________

_2 ) I call SpecialCells Special Mental Cells as they are a bit quirky. I don’t have any experience with .SpecialCells(xlCellTypeVisible , but I do know that in some other uses it does not always return what you might expect. So I would suggest that you always check what it is giving you. The error that John got still suggests to me that possibly his SpecialCells call did not find anything and so returned an error. But then again, I am not completely sure how he got his routine to do anything as if I try a routine like his then it goes off endlessly calling itself every time it tries to use SpecialCells

Alan
_._______________

I tried this code below in the uploaded workbook in both Excel 2003 and Excel 2007. It works for me and gives what I think are the correct results.

Code: Select all

 Option Explicit ' Coding in Worksheet code module
Sub SpecialMentalCells() ' Demo code to call  Sub Worksheet_SelectionChange(ByVal Target As Range)
 Me.Cells.Clear
 Me.Rows.Hidden = False
 Let Application.EnableEvents = True
'
 Me.Rows("2:3").Hidden = True
 Let Range("B1").Value = "Anyfink"
 Call Worksheet_SelectionChange(Range("B1"))
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
 Let Application.EnableEvents = False
 Set Rng2 = Range("A1:H1").SpecialCells(xlCellTypeConstants) 'Returns range B1, because I put Anyfink in it
 Let Application.EnableEvents = True
 MsgBox prompt:="Special Mental Cells address is  " & Rng2.Address
 Set Rng1 = Range("A1:H1") ' This range includes B1
 Let Application.EnableEvents = False
 Set Rng3 = Range("B1:B14").SpecialCells(xlCellTypeVisible) 'returns range B1,B4:B14 because they are visible
 Let Application.EnableEvents = True
 MsgBox prompt:="Special Mental Cells address is  " & Rng3.Address
Dim Intersecual As Range
 Set Intersecual = Application.Intersect(Target, Rng3, Rng1, Rng2) ' All these ranges include B1 , so B1 should be the Intersect return
 MsgBox prompt:="Intersecual address is  " & Intersecual.Address
 Let Application.EnableEvents = False
 Set Intersecual = Application.Intersect(Target, Range("B1:B14").SpecialCells(xlCellTypeVisible), Rng1, Range("A1:H1").SpecialCells(xlCellTypeConstants))
 Let Application.EnableEvents = True
 MsgBox prompt:="Intersecual address is  " & Intersecual.Address
 Let Application.EnableEvents = False
 MsgBox prompt:="Intersecual address is  " & Application.Intersect(Target, Range("B1:B14").SpecialCells(xlCellTypeVisible), Rng1, Range("A1:H1").SpecialCells(xlCellTypeConstants)).Address
 Let Application.EnableEvents = True
End Sub

[jstevens]

Alan,

Here is a sample workbook. One thing to note is that I can replicate the error message intermittently although in the original workbook this happens each time. In the mean time I will take a look at your file.

el_Intersect.xlsm

[jstevens]

Alan,

This is a screenshot of an error message on the file you posted. I clicked on a cell and selected "Ok" to the message buttons then the error appeared.

EL_50.png

[Doc.AElstein]

I would expect that error if you click other than cell B1, because then Intersect will be Nothing. If Intersect is Nothing, then you cannot get its address.
The idea of the coding is to test if SpecialCells works in an events code.

Run routine Sub SpecialMentalCells() and see what happens

[Doc.AElstein]

I do notice
_ (i) that I don’t need to use my “.EnableEvents False / True pair “ fix to prevent .SpecialCells(xlCellTypeVisible) from setting off the Sub Worksheet_SelectionChange(ByVal Target As Range), if I have not made any rows invisible. That is puzzling me. But I have only just started looking into SpecialCells in Event coding, so I am not sure what is going on: Possibly the strange phenomena of SpecialCells setting off the Sub Worksheet_SelectionChange(ByVal Target As Range), only occurs when SpecialCells returns a different range other than the original , in this case Range("R_MyRange").
_ (ii) If I follow your instructions, then I observe the strange phenomena of SpecialCells setting off the Sub Worksheet_SelectionChange(ByVal Target As Range). So the code goes on for ever calling itself and I have to break out, if I can , or pull the plug on my computer to get out
_ (iii) if I add the “fix” as follows, then things seem to be working

Code: Select all

  Let Application.EnableEvents = False ' "fix" to stop SpecialCells settin off this routine
    If Not Intersect(Range("R_MyRange").SpecialCells(xlCellTypeVisible), Target) Is Nothing Then
 Let Application.EnableEvents = True ' you must "switch events back on" , or otherwise this routine, (and all event routines),  will never work again
        Sheets("Sheet1").Shapes("TextBox 2").TextFrame.Characters.Text = "You selected " & Target.Address
    '     Button_01
    Else
 Let Application.EnableEvents = True ' you must "switch events back on" , or otherwise this routine, (and all event routines),  will never work again

Alan

P.s. Your “Out of stack space” error , as shown in your uploaded file, is what I would expect . This is the famous stack overflow error .. that error occurs beacuse of the following: every time you re run a routine ( when it "sets itself off" , ) a new copy of variables and stuff is made. That needs space. When my mentioned phenomena occurs, the routine keeps setting itself off, so you make endless copies … or you would if computer space was infinite. At some point your computer craps out when you have "filled the stack to overflow"

It sounds like we have the problem licked.

[jstevens]

Alan,

I got it to work without encountering the error(s).

Thank you!

[Doc.AElstein]

Hi John
Just some stuff out of general interest..

_ I found only one thing on the internet concerning the issues that we have come across here: https://stackoverflow.com/questions/196 ... excel-2010" onclick="window.open(this.href);return false; ( https://stackoverflow.com/questions/196 ... ged-firing" onclick="window.open(this.href);return false; )

_ It seems to be just the event coding of type SelectionChange which is fired off by a use of .SpecialCells

_.______________


_ Some Tips for finding a problem such as your original problem.
_(i) Test code to set off an Event routine
If you are developing an Event routine , then it can get very tedious in testing if you have to do something like select a cell to se off an Event routine.
A simple alternative way to make your life easier is to use a simple routine to set off your event routine. For example, using your initial code from file “el_Intersect.xlsm” http://www.eileenslounge.com/viewtopic. ... 19#p244612" onclick="window.open(this.href);return false;

First _ 1. Select one Item from Col A's dropdown, so that some cells are hidden. For example, to hide row 12 / Item 12
Hide row 12( Item12) make row12(Item12) not visible from drop down list.JPG : https://imgur.com/s2jilkJ" onclick="window.open(this.href);return false;

Second: _ 2 . To fire off Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Instead of …._
_...... 2. Select a cell outside of the RangeName (R_MyRange) G5:Z22. Preferably between Col A to Col F
We can do alternative:
I can write a routine like this:

Code: Select all

Sub TestMyEventCode() ' To test an event code, Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Call Worksheet_SelectionChange(Target:=Range("E9")) ' This is an alternative to selecting manually cell E9
End Sub

Put that code in the same worksheet code module as Private Sub Worksheet_SelectionChange(ByVal Target As Range) :-

Code: Select all

 Sub TestMyEventCode() ' To test an event code, Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Call Worksheet_SelectionChange(Target:=Range("E9")) ' This is an alternative to selecting manually cell E9
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    
    If Not Intersect(Range("R_MyRange").SpecialCells(xlCellTypeVisible), Target) Is Nothing Then
     Sheets("Sheet1").Shapes("TextBox 2").TextFrame.Characters.Text = "You selected " & Target.Address
    '     Button_01
    Else
     Sheets("Sheet1").Shapes("TextBox 2").TextFrame.Characters.Text = ""
    End If
End Sub








'    http://www.eileenslounge.com/viewtopic.php?f=27&t=31602&p=244545#p244612 
'To reproduce the error on Sheet 1
'1. Select one Item from Col A's dropdown.
'2. Select a cell outside of the RangeName , Range(“R_MyRange”) ( Range(“G5:Z22”) ) ,   Preferably between Col A to Col F - like this https://imgur.com/s2jilkJ
'--- you can do like select cell E9 with this:-
'    Sub TestMyEventCode() '
'     Call Worksheet_SelectionChange(Target:=Range("E9")) '
'    End Sub
'3. Run in  [FONT=Courier New]F8[/FONT]  step mode

_3 . Use F8 step mode to “step through” code
_ 3(i) Go into VB Editor: ( Al+ F11 from Excel ) , or for worksheet Show Code: _1 Right mouse click in Sheet1 tab _2 Show Code .JPG : https://imgur.com/lPeZBGn" onclick="window.open(this.href);return false;
_3(ii) Click in test code:
Select anywhere in test code.JPG : https://imgur.com/7KYJDE6" onclick="window.open(this.href);return false; ( Put cursor inside test code, Sub TestMyEventCode() , and left click mouse )
_3(iii) Use F8 key -- Keyboard Key F8 .JPG : https://imgur.com/fn91cjU" onclick="window.open(this.href);return false;
This will “step through” code – F8, F8 , F8, ……
Step 1.JPG : https://imgur.com/oWrsiv4" onclick="window.open(this.href);return false;
Step 2.JPG : https://imgur.com/Ksg2P9f" onclick="window.open(this.href);return false;
Step 3.JPG : https://imgur.com/KBhItho" onclick="window.open(this.href);return false;
Step 4.JPG : https://imgur.com/GQmSjnR" onclick="window.open(this.href);return false;
Step 5.JPG : https://imgur.com/tLV6eJP" onclick="window.open(this.href);return false;
Step 6.JPG : https://imgur.com/GQmSjnR" onclick="window.open(this.href);return false;
Step 7.JPG : https://imgur.com/tLV6eJP" onclick="window.open(this.href);return false;
Step 8.JPG : https://imgur.com/GQmSjnR" onclick="window.open(this.href);return false;
Step 9.JPG : https://imgur.com/tLV6eJP" onclick="window.open(this.href);return false;
Step 10.JPG : https://imgur.com/GQmSjnR" onclick="window.open(this.href);return false;
Step 11.JPG : https://imgur.com/tLV6eJP" onclick="window.open(this.href);return false;
…..
….. :
….Step 456 , Step 457 , Step 458 ………………
…..
-…………..Step 100000000000, Step 100000000001 , …………….
………..
……… Error : Run time error '28' : Out of stack space !!!!!!!

Or @ Step 5 .. Stop.jpg https://imgur.com/6i0JMa0" onclick="window.open(this.href);return false; !!!!!!

_.______________________
Alan

[jstevens]

Alan,

Thanks for the additional information. It certainly is interesting stepping through the code to see the impact of Special Cells in the worksheet event.