VBA: Invalid Procedure,Call or Argument

[SAMUSER]

Hi Everyone,

I am trying to add a simple property to the macro. If someone pastes information, there is a run-time 9 error. I want to have an "If Error" statement that alerts the user, and tells them to press ctrl+z to undo, then the procedure works fine.

However, the If Error statement is causing me problems. When I run it, I get a Runtime 5: Invalid procedure, call or argument. I have highlighted the line with the bug.

Any help would be much appreciated!

Thanks a lot!

Sub getResults()


ActiveSheet.Unprotect Password:="Syncos123!"


Sheets("selector").Activate


Dim resultDrop As Range
Set resultDrop = Sheets("Selector").Range("A12")


Dim outcomeDrop As Range
Set outcomeDrop = Sheets("Selector").Range("A24")


Dim dOutcomeSelect As String
dOutcomeSelect = Sheets("Selector").Range("A8").Value


'Sheets("Master List").Activate


Dim dOutcomeList As Range
Set dOutcomeList = Sheets("Master List").Range("C2:BD4")


Dim masterRange As Range
Set masterRange = Sheets("Master List").Range("C2:BD28")


Dim dOutcomePos As Range
Set dOutcomePos = masterRange.Find(dOutcomeSelect)


Dim resultQuant As Long


[COLOR=#b22222]If Error Then MsgBox "You cannot paste information. Press ctrl+Z to undo, and then select from the drop"[/COLOR]


resultQuant = dOutcomePos.Offset(2, 0).Value


Dim outcomeQuant As Long
outcomeQuant = dOutcomePos.Offset(2, 1).Value


Dim resultRange As Range
Set resultRange = Range(dOutcomePos.Offset(3, 0), dOutcomePos.Offset(resultQuant + 2, 0))


Dim outcomeRange As Range
Set outcomeRange = Range(dOutcomePos.Offset(3, 1), dOutcomePos.Offset(outcomeQuant + 2, 1))


ActiveWorkbook.Names.Add Name:="ValListR", RefersTo:=resultRange
 
ActiveWorkbook.Names.Add Name:="ValListO", RefersTo:=outcomeRange


With resultDrop.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=ValListR"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With




With outcomeDrop.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=ValListO"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With


resultDrop.Value = resultRange(1, 1).Value
outcomeDrop.Value = outcomeRange(1, 1).Value


MsgBox "The 'Results' and 'Outcomes' have been updated based on your selection of the 'Desired Outcome'. Please select the applicable Result and Outcome from the drop-down menu and copy it to the clipboard using the button before pasting it into GAIMS"
ActiveSheet.Protect Password:="Syncos123!", DrawingObjects:=True, Contents:=True, Scenarios:=True


End Sub

 

[ZAX]

Change The If Error Line With This:

On Error Goto ErrorHandler

ErrorHandler:
MsgBox "You cannot paste information. Press ctrl+Z to undo, and then select from the drop"

Your Problem that the code DOESN'T Know what "Error" is; It considers it as an undifined variant.

ZAX

 

[SAMUSER]

ZAX- Thanks a lot! I totally forgot that I am using VBA- still sticking to Excel syntax.

I have tried your suggestion, and it works. However, the error handler comes up every time (even after the normal loop has ended). Do you know why this may be happening?

Regards,
Saim

 

[SAMUSER]

Hi Guys,

Any idea how this can be fixed? I have been trying, but no luck so far

 

[rjwebgraphix]

Typically error handlers are after exiting the sub, so that any previous code runs through to its end.

Something like this.

On Error Goto ErrorHandler
'run a bunch of code

Finish:
Exit Sub  

ErrorHandler:
MsgBox "You cannot paste information. Press ctrl+Z to undo, and then select from the drop"

 

[rjwebgraphix]

If pasting is an issue, you might also want to consider handling the pasting issue before it becomes an issue.

http://www.vbaexpress.com/forum/showthread.php?t=19391

This thread was just updated last week with a much better code than the original author wrote, so you might want to start at the bottom and read upwards, rather than starting at the top.

 

[SAMUSER]

Thanks a lot, but I am struggling to incorporate this into my code. Any clues as to how I can do it? Please let me know.

Thanks!

If pasting is an issue, you might also want to consider handling the pasting issue before it becomes an issue.

Solved: Disable Cut, Copy, Paste Macro for One Column - VBA Express Forum

This thread was just updated last week with a much better code than the original author wrote, so you might want to start at the bottom and read upwards, rather than starting at the top.

 

[rjwebgraphix]

Which part are you struggling with? The error handler or the disabling of pasting?

If you're looking at disabling pasting then look at SNB's post. Copy the whole thing into the code for ThisWorkbook.

The only thing you need to change is this section:

Sub ChkSelection(ByVal Sh As Object)
    Select Case Sh.Name
    Case "Sheet1"
        ToggleCutCopyAndPaste Intersect(Selection, Sh.Columns(1)) Is Nothing
    Case "Sheet2"
        ToggleCutCopyAndPaste Not Intersect(Selection, Sh.Range("G1:G20")) Is Nothing
    Case Else
        ToggleCutCopyAndPaste True
    End Select
End Sub

Case "Sheet1" needs to change to the sheet name you want to disable pasting on. If you don't need a 2nd sheet, delete that section and you'll need to change this line....

ToggleCutCopyAndPaste Not Intersect(Selection, Sh.Range("G1:G20")) Is Nothing

Change Range("G1:G20") to the range you do not want them to paste into.

So lets say your sheet name is MyData and you don't want them to paste into Anything from A1 to F110. you would change it to look like this.

Sub ChkSelection(ByVal Sh As Object)
    Select Case Sh.Name
    Case "MyData"
        ToggleCutCopyAndPaste Intersect(Selection, Sh.Range("A1:F110")) Is Nothing
    Case Else
        ToggleCutCopyAndPaste True
    End Select
End Sub

You don't need to change anything else.

If you're still having trouble, post what you have and we'll take a look at it.

 

[SAMUSER]

Hey! I'm sorry- I did figure it out (it was some syntax issue). Thanks so much for your help though!

Which part are you struggling with? The error handler or the disabling of pasting?

If you're looking at disabling pasting then look at SNB's post. Copy the whole thing into the code for ThisWorkbook.

The only thing you need to change is this section:

Sub ChkSelection(ByVal Sh As Object)
    Select Case Sh.Name
    Case "Sheet1"
        ToggleCutCopyAndPaste Intersect(Selection, Sh.Columns(1)) Is Nothing
    Case "Sheet2"
        ToggleCutCopyAndPaste Not Intersect(Selection, Sh.Range("G1:G20")) Is Nothing
    Case Else
        ToggleCutCopyAndPaste True
    End Select
End Sub

Case "Sheet1" needs to change to the sheet name you want to disable pasting on. If you don't need a 2nd sheet, delete that section and you'll need to change this line....

ToggleCutCopyAndPaste Not Intersect(Selection, Sh.Range("G1:G20")) Is Nothing

Change Range("G1:G20") to the range you do not want them to paste into.

So lets say your sheet name is MyData and you don't want them to paste into Anything from A1 to F110. you would change it to look like this.

Sub ChkSelection(ByVal Sh As Object)
    Select Case Sh.Name
    Case "MyData"
        ToggleCutCopyAndPaste Intersect(Selection, Sh.Range("A1:F110")) Is Nothing
    Case Else
        ToggleCutCopyAndPaste True
    End Select
End Sub

You don't need to change anything else.

If you're still having trouble, post what you have and we'll take a look at it.