In a loaded .xla add-in I have the following code in the ThisWorkbook
module:

Option Explicit
Private WithEvents xlApp As Excel.Application

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
On Error GoTo ERROROUT
If Len(Target.Name.Name) > 10 Then
Application.StatusBar = Space(8) & Target.Name.Name
Else
Application.StatusBar = False
End If
Exit Sub
ERROROUT:
Application.StatusBar = False
End Sub

This code runs fine on many machines, but on one particular machine only
(Excel 2003, Win XP) the line
If Len(Target.Name.Name) > 10 Then
crashes Excel when there is a filter in the sheet.

What possibly could be the trouble here?
I can't reproduce this at all on my machine, so difficult to unravel this
problem, but maybe somebody
has an idea.

RBS

That workbook has the right error handling setting, that is Break on
unhandled errors.
It is not the name of the sheet, but the name of the selected range.
Baffling me this.

RBS


"joel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> check the error setting in VBA menu Tools - Options - general - Error
> trapping.
>
> It may be that the book that crashes has a different setting.
>
> I think your code really wants to get the name of the sheet and I would
> change the code to this
>
> from:
> If Len(Target.Name.Name) > 10 Then
>
> to:
> If Len(Target.parent.Name) > 10 Then
>
>
> The parent of a cell or range opn a worksheet is the worksheet.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread:
> http://www.thecodecage.com/forumz/sh...d.php?t=206051
>
> http://www.thecodecage.com/forumz
>

Hi Bart,

Nothing obvious springs to mind. A Sheet with an autofilter (or sheet that
has had an autofilter) will include a worksheet level name like this

Sheet1!_FilterDatabase

If user selects the cells with the dropdown filters that's what would be
returned with
Target.Name.Name

However that shouldn't cause a problem. Best thing would be to ask the user
to send you a copy of the problematic workbook, stripped down with just
enough to reproduce the problem.

Regards,
Peter T


"RB Smissaert" <(E-Mail Removed)> wrote in message
news:%23mmZ2O0$(E-Mail Removed)...
> In a loaded .xla add-in I have the following code in the ThisWorkbook
> module:
>
> Option Explicit
> Private WithEvents xlApp As Excel.Application
>
> Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
> Range)
> On Error GoTo ERROROUT
> If Len(Target.Name.Name) > 10 Then
> Application.StatusBar = Space(8) & Target.Name.Name
> Else
> Application.StatusBar = False
> End If
> Exit Sub
> ERROROUT:
> Application.StatusBar = False
> End Sub
>
> This code runs fine on many machines, but on one particular machine only
> (Excel 2003, Win XP) the line
> If Len(Target.Name.Name) > 10 Then
> crashes Excel when there is a filter in the sheet.
>
> What possibly could be the trouble here?
> I can't reproduce this at all on my machine, so difficult to unravel this
> problem, but maybe somebody
> has an idea.
>
> RBS
>

Hi Peter,

I got the workbook, but I couldn't reproduce this problem.
The only purpose of that code was to show the selected
range name in the statusbar if it possibly wouldn't show
fully in the name box (at the top left).
So, not really essential and I have thrown that code out now.
I know in fact a way (with the API) to widen that name
box and that is a better way.

RBS


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> Hi Bart,
>
> Nothing obvious springs to mind. A Sheet with an autofilter (or sheet that
> has had an autofilter) will include a worksheet level name like this
>
> Sheet1!_FilterDatabase
>
> If user selects the cells with the dropdown filters that's what would be
> returned with
> Target.Name.Name
>
> However that shouldn't cause a problem. Best thing would be to ask the
> user to send you a copy of the problematic workbook, stripped down with
> just enough to reproduce the problem.
>
> Regards,
> Peter T
>
>
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:%23mmZ2O0$(E-Mail Removed)...
>> In a loaded .xla add-in I have the following code in the ThisWorkbook
>> module:
>>
>> Option Explicit
>> Private WithEvents xlApp As Excel.Application
>>
>> Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
>> As Range)
>> On Error GoTo ERROROUT
>> If Len(Target.Name.Name) > 10 Then
>> Application.StatusBar = Space(8) & Target.Name.Name
>> Else
>> Application.StatusBar = False
>> End If
>> Exit Sub
>> ERROROUT:
>> Application.StatusBar = False
>> End Sub
>>
>> This code runs fine on many machines, but on one particular machine only
>> (Excel 2003, Win XP) the line
>> If Len(Target.Name.Name) > 10 Then
>> crashes Excel when there is a filter in the sheet.
>>
>> What possibly could be the trouble here?
>> I can't reproduce this at all on my machine, so difficult to unravel this
>> problem, but maybe somebody
>> has an idea.
>>
>> RBS
>>
>
>