Object property doesn't support the metod

[Guest]

The debugger complain "Pastespecial failed in range class" in "Sub
FormulaPaste" if an selection before the with-block is missing as earlier.

I feel I need something like "RngSel.Select" before With - block (in "Sub
FormulaPaste"), but I don't know the syntax . Just a "RngSel.Select" make a
"missing object" -failure and a "Application.RngSel.Select" give "Object
property doesn't support the metod"

How should I write it? The two subs in question again:

/Regards

Private Sub Worksheet_Activate()

'*************************************
' Disable draganddrop, turn protection off and on
' Call an sub to ensure right format in sheet
' Call an sub to Ctrl-V only make Pastespecial Formulas
' Deliver selection as "target" as Range to callsubs
'**************************************
Dim RngSel As Range
Set RngSel = Application.Selection
Application.CellDragAndDrop = False

Application.EnableEvents = False
Application.OnKey "^v", "FormulaPaste"

Call FormatTemp

RngSel.Activate

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

Application.EnableEvents = True

End Sub


Sub FormulaPaste()
'*************************************
' Demand a selection
' Causes Loop with ActiveSheet_Change if appl.event not disabled
' Make PasteSpecial - Formulas
'*************************************
Application.EnableEvents = False
Application.RngSel.Select <------------------------------------"Object
property doesn't support the metod" error !!!!!
With Selection
.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With

Call FormatTemp

Application.EnableEvents = True

 

[Rob Bovey]

You're getting the error because the Excel Application object doesn't
have a RngSel method or property. As far as I can see, if you just delete
this line of code you'll be fine.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Guest]

Thank you Rob. The reason why I put it there in the first place is bacause
if I leave it out, i get an other error:
"Pastespecial-method in range-class failed ". Clues?

/Regards