CODEWITHSUNDEEP
vbaexcel
Faraz Masroor
Faraz Masroor

Reputation: 238

Excel VBA prevents me from pasting values

I made a macro that computes the STDEV of a selection and puts it in the status bar, and so is called whenever the selection is changed. However, it has the unintended effect of preventing me from pasting values anywhere when I copy a range. I can copy, but then when I click somewhere else to paste the paste button becomes greyed out and I lose the selection. How do I get my paste back? Preferably by changing the macro but not by adding new ones.

Here is the code: 

Public st As Double
Public rng as Range

Sub stdev()
On Error Resume Next
st = Application.WorksheetFunction.stdev(rng)
If Err.Number <> 0 then
    st = 0
End If
Application.DisplayStatusBar = True
Application.StatusBar = "Stdev: " & st
End Sub

Then: 

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
Set rng = Target
Call stdev
End Sub

Upvotes: 2

Views: 128

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Calling Application.DisplayStatusBar is what clears the office clipboard. You could remove that line entirely or use

Sub stdev()
On Error Resume Next
st = Application.WorksheetFunction.stdev(rng)
If Err.Number <> 0 Then
    st = 0
End If
If Not Application.DisplayStatusBar Then Application.DisplayStatusBar = True
Application.StatusBar = "Stdev: " & st
End Sub

That way it would only clear the clipboard if the status bar wasn't displayed.

Upvotes: 0

Malil
Malil

Reputation: 143

What if you change

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
Set rng = Target
Call stdev
End Sub

to

Private Sub Worksheet_SelectionChagne(ByVal Target as Range)
If Application.CutCopyMode = 1 Then Exit Sub
Set rng = Target
Call stdev
End Sub

That should bypass your subroutine while you're copying and pasting something, without having to add a lot more code.

Upvotes: 1

Related Questions