pasting problem

I

icestationzbra

i have this excel file in which there is code all over the place.

until recently i was able to copy and paste from other files onto thi
file without a problem.

off late, when i copy from other files and come over to this file, th
paste button is disabled. i figured out that if i keep the design mod
on from control toolbar and then paste, it allows me.

is there any specific reason this is happening? i am very sure i di
not put any code that would prevent such an operation when the file i
not in design mode.

mac
 
D

Dave Peterson

My first guess is that you have some events that are running (maybe a selection
change or worksheet activate???).

Lots of macros clear the clipboard.

By going into design mode, you're into "development mode" and macros (including
events) won't be running.

You could verify this:
Open excel with macros disabled
(close excel)
windows start button|Run
excel /safe

Or even just disabling events (temporarily).
Hit alt-f11 to see the VBE
hit ctrl-g to see the immediate window
type this and hit enter

application.enableevents = false

Then back to excel and test it out.

When you're done testing, turn the event handling back on. (True in the
immediate window.)

Else your workbook won't work as designed.

======
that event could be in your workbook or an addin--and starting in Safe mode
stops macros from running.
 
I

icestationzbra

dave,

thanks for the reply. i did as suggested by you and i have one thin
confirmed, its the selectionchange.

i inserted an 'enableevents = false', and paste function work
properly. unfortunately, i need the selectionchange code to function.

is there a possibility i could have the cake and eat it too?

thanks,

mac
 
D

Dave Peterson

You could do it toggle it on/off manually (via the immediate window in the VBE).

Or you could have a little macro that you run that toggles it on/off.

Option Explicit
Sub testme03()
With Application
.EnableEvents = (Not .EnableEvents)
End With
End Sub

Or you could do the copy|paste in code. And your code would turn off the
events, do the paste and reenable events.

A basic example with no options (like paste special stuff):

Option Explicit
Sub testme03()

Dim fRng As Range
Dim tRng As Range

Set fRng = Nothing
On Error Resume Next
Set fRng = Application.InputBox _
(Prompt:="Select a single area range to copy", Type:=8).Areas(1)
On Error GoTo 0

If fRng Is Nothing Then
Exit Sub 'user hit cancel
End If

Set tRng = Nothing
On Error Resume Next
Set tRng = Application.InputBox _
(Prompt:="Select top left cell of range to paste", _
Type:=8).Cells(1)
On Error GoTo 0

If tRng Is Nothing Then
Exit Sub 'user hit cancel
End If

Application.EnableEvents = False
fRng.Copy _
Destination:=tRng
With Application
.Goto tRng.Resize(fRng.Rows.Count, fRng.Columns.Count), scroll:=True
.EnableEvents = True
End With

End Sub
 
Top