userform question

[plost33]

I have a userform which allows user to selected a date formt he calendar. In my code I name the value selected myDate. I want to have a line of code at the begginning of my code that checks if myDate = a date already listed in column H on a sheet named "all dor's". if it does match then a msgbox appear saying "date already entered" and the code return to the opened userform.

 

[plost33]

vog, on the following code i am getting an invalid or unqualified refrence error on the line in red.

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim Mylrow As Long, PLLrow As Long
Dim MyDate As Date, MyVsle As String
Dim mylrow2 As Long
Dim r
Sheets("Z").Activate
Sheets("Z").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("all dor's").Activate
Sheets("all dor's").Unprotect Password:="techedit"
 
Mylrow = Sheets("All DOR'S").Range("B18").End(xlDown).Row + 1
Sheets("Z").UsedRange .Copy
With UserForm4
    MyDate = Calendar1.Value
    For r = 0 To .lstbVessel.ListCount - 1
        If .lstbVessel.Selected(r) = True Then ''get the vessle name
            MyVsle = lstbVessel.List(r)
        End If
    Next
   If IsNumeric(Application.Match(CDbl(MyDate), Range("H18:H" & Mylrow), 0)) And IsNumeric(Application.Match(MyVsle, Range("I18:I" & Mylrow), 0)) Then
    If Application.Match(CDbl(MyDate), Range("H18:H" & Mylrow), 0) = Application.Match(MyVsle, Range("I18:I" & Mylrow), 0) Then
        MsgBox "A DOR of the same date and vessel name has already been entered! Please select another date or a diffrent vessel."
        Exit Sub
    End If
End If
    Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
 
    '''' now det the last row form the pasted values '''
 
    PLLrow = Sheets("All DOR'S").Range("B65536").End(xlUp).Row
 
    '''' fill column H for Date and Column I for Vessle '''
 
    Sheets("All DOR'S").Range("H" & Mylrow & ":H" & PLLrow).Value = MyDate   '' this is for the Date
 
    Sheets("All DOR'S").Range("I" & Mylrow & ":I" & PLLrow).Value = MyVsle
 
End With
 
Unload UserForm4
Application.DisplayAlerts = False
Sheets("Z").UsedRange.ClearContents
Sheets("all dor's").Protect Password:="techedit"
Application.DisplayAlerts = True
MsgBox "DOR data added successfully!"
 
End Sub

 

[plost33]

here the line in red:

Sheets("Z").UsedRange , Copy

 

[plost33]

the message says expected function or variable when a comma is used. I had mistyped in previous post.

 

[plost33]

vog, am i right in understand this line of code:

If Application.Match(CDbl(MyDate), Range("H18:H" & Mylrow), 0) = Application.Match(MyVsle, Range("I18:I" & Mylrow), 0) Then


it just says if the date matches anything in column H then true = if any vessel name matches in column I then true. so if true=true then...msgbox

thats not the same as verifying if the row each was matched on was the same row. that just makes sure both are matched before moving on. I am in need of a match being made on both plus having that match happen on the same row. to move on to msgbox.

 

[VoG]

That does check if they are on the same row. Take a look at the MATCH function in Excel.