ThisWorkbook of personal.xls

[Jack Sons]

Hi all,

Hi all,

When I have in ThisWorkbook of personal.xls the code below without the last
sub (the Private Sub xlApp_SheetSelectionChange), all functions well.
But when the last sub is added I have problems with copying because when I
try to copy data, the items paste and past special of the right mouse click
menue are greyed out, the same with the paste buttons in the bar (don't know
its name) above in my screen. Also ctr c - ctr v does not work in the proper
way.

What causes this behaviour and how can I overcome it?

Jack Sons
The Netherlands
------------------------------------------------------------------------------------------------------------------
Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub

Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Round(Application.Average(Target), 2) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Round(Application.sum(Target), 2) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If
End Sub

 

[Jim Rech]

This line exits CutCopyMode:

Application.DisplayStatusBar = True

It doesn't make sense that it has this affect but so it is. Perhaps you can
set DisplayStatusBar in Auto_Open and assume it has remained set and so
remove this line from your macro.

--
Jim
| Hi all,
|
| Hi all,
|
| When I have in ThisWorkbook of personal.xls the code below without the
last
| sub (the Private Sub xlApp_SheetSelectionChange), all functions well.
| But when the last sub is added I have problems with copying because when I
| try to copy data, the items paste and past special of the right mouse
click
| menue are greyed out, the same with the paste buttons in the bar (don't
know
| its name) above in my screen. Also ctr c - ctr v does not work in the
proper
| way.
|
| What causes this behaviour and how can I overcome it?
|
| Jack Sons
| The Netherlands
| ------------------------------------------------------------------------------------------------------------------
| Option Explicit
| Public WithEvents xlApp As Application
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| Set xlApp = Nothing
| End Sub
|
| Private Sub Workbook_Open()
| Set xlApp = Application
| End Sub
|
| Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
| Range)
| Application.DisplayStatusBar = True
| On Error Resume Next
| If Target.Count < 2 Then
| Application.StatusBar = False
| Else
| Application.StatusBar = _
| "Average=" & Round(Application.Average(Target), 2) & _
| "; " & _
| "Count=" & Application.CountA(Target) & "; " & _
| "Count nums=" & Application.Count(Target) & _
| "; " & _
| "Sum=" & Round(Application.sum(Target), 2) & "; " & _
| "Max=" & Application.Max(Target) & "; " & _
| "Min=" & Application.Min(Target)
| End If
| End Sub
|
|

 

[Jack Sons]

Jim,

What exactly sall I put in Private Sub Workbook_Open()
DisplayStatusBar
or
set DisplayStatusBar ?

Both give an error message. Should it come before of after the line with Set
xlApp = Application?
I'm afraid I did not understand your tip well enough.

Jack.

 

[Jim Rech]

What exactly sall I put in Private Sub Workbook_Open()

Application.DisplayStatusBar = True


--
Jim
| Jim,
|
| What exactly sall I put in Private Sub Workbook_Open()
| DisplayStatusBar
| or
| set DisplayStatusBar ?
|
| Both give an error message. Should it come before of after the line with
Set
| xlApp = Application?
| I'm afraid I did not understand your tip well enough.
|
| Jack.
|
|
| "Jim Rech" <[email protected]> schreef in bericht
| | > This line exits CutCopyMode:
| >
| > Application.DisplayStatusBar = True
| >
| > It doesn't make sense that it has this affect but so it is. Perhaps you
| > can
| > set DisplayStatusBar in Auto_Open and assume it has remained set and so
| > remove this line from your macro.
| >
| > --
| > Jim
| > | > | Hi all,
| > |
| > | Hi all,
| > |
| > | When I have in ThisWorkbook of personal.xls the code below without the
| > last
| > | sub (the Private Sub xlApp_SheetSelectionChange), all functions well.
| > | But when the last sub is added I have problems with copying because
when
| > I
| > | try to copy data, the items paste and past special of the right mouse
| > click
| > | menue are greyed out, the same with the paste buttons in the bar
(don't
| > know
| > | its name) above in my screen. Also ctr c - ctr v does not work in the
| > proper
| > | way.
| > |
| > | What causes this behaviour and how can I overcome it?
| > |
| > | Jack Sons
| > | The Netherlands
| >
| ------------------------------------------------------------------------------------------------------------------
| > | Option Explicit
| > | Public WithEvents xlApp As Application
| > | Private Sub Workbook_BeforeClose(Cancel As Boolean)
| > | Set xlApp = Nothing
| > | End Sub
| > |
| > | Private Sub Workbook_Open()
| > | Set xlApp = Application
| > | End Sub
| > |
| > | Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
| > As
| > | Range)
| > | Application.DisplayStatusBar = True
| > | On Error Resume Next
| > | If Target.Count < 2 Then
| > | Application.StatusBar = False
| > | Else
| > | Application.StatusBar = _
| > | "Average=" & Round(Application.Average(Target), 2) & _
| > | "; " & _
| > | "Count=" & Application.CountA(Target) & "; " & _
| > | "Count nums=" & Application.Count(Target) & _
| > | "; " & _
| > | "Sum=" & Round(Application.sum(Target), 2) & "; " & _
| > | "Max=" & Application.Max(Target) & "; " & _
| > | "Min=" & Application.Min(Target)
| > | End If
| > | End Sub
| > |
| > |
| >
| >
|
|

 

[Jack Sons]

Jim,

It really seems to work nicely. Many tanks.

Jack.