Can't get a macro to run if a criteria is met.

[owen4512]

Here's the Vb Code I used but I keep getting a "Argument Not Optional" message box.

I want for the macro to run if the value equals to "Iawn I Bwcio". But if the value equals to "Dyddiad Anghywir"
then I want a message box to appear and say ;
"Mae yna dyddiad anghywir wedi ei fewnbynnu! Mae angen newid hyn er mwyn cario ymlaen!"

Private Sub mewbynnuDataSioe(ByVal target As Range)
    If (Range(D5) = "Iawn I Bwcio") Then
    '
    ' Mewnbynnu_Data_Sioe Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+Q
    '
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("E4").Select
        ElseIf (Range(D5) = "Dyddiad Anghywir") Then
        MsgBox "Mae yna dyddiad anghywir wedi ei fewnbynnu! Mae angen newid hyn er mwyn cario ymlaen!"
        
    End With
    End If
    Exit Sub
End Sub

Thanks in advanced, Owen.

 

[Andrew Poulsom]

Welcome to MrExcel.

Try removing the red text:

Private Sub mewbynnuDataSioe(ByVal target As Range)

 

[owen4512]

Okay I've done that but I now get another error message.

Compile error:
End With without With

 

[Comfy]

Remove the "End With" from your code.

 

[owen4512]

I tried that, but yet another error.

Run-time error '1004'
Method 'Range' of object '_Global' failed

When I press debug the
If (Range (D5) = "Iawn I Bwcio") then
gets highlighted yellow

 

[Comfy]

If Range("D5") = "Iawn I Bwcio"

 

[RoryA]

Try:

Sub mewbynnuDataSioe()

    '
    ' Mewnbynnu_Data_Sioe Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+Q
    '
    If Range("D5").Value = "Iawn I Bwcio" Then
    Range("E3").Value = "1"
    Range("E4").Select
        ElseIf Range("D5").Value = "Dyddiad Anghywir" Then
        MsgBox "Mae yna dyddiad anghywir wedi ei fewnbynnu! Mae angen newid hyn er mwyn cario ymlaen!"
       
    End If

End Sub

 

[owen4512]

Argument not optional again

As you can tell I'm not very educated in Visual basic.
This macro will be assigned to a button, not sure if that helps.

 

[Andrew Poulsom]

See Post #2.

 

[RoryA]

Sorry missed the amendment to the declaration. I've updated the code.