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.

 

[VoG]

Try

Sheets("all dor's").Activate
With ActiveSheet
    .Unprotect Password:="techedit"
End With

 

[plost33]

vog, i am getting a weird error now it says:

runtime error '1004': pastespecial method of range class failed


the line is:
Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues



any idea why. i have userinterfaceonly=true....

need something else??

 

[VoG]

What is this meant to do?

Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues

I can't see from your code what it is supposed to be pasting.

 

[plost33]

that pastes in value that user have on the clipboard prior to starting this macro code. this ocde paste the data in for them, making sure it is values only and no formats or forumlas get copied in the process.

 

[VoG]

Unprotecting the sheet clears the clipboard. Don't ask me why, it makes no sense to me. So you don't have anything to paste - hence the error.

Is it just a single cell that they are copying or a range?

 

[plost33]

a range that my users will select from another workbook/worksheet. and the range is always a diffrent size.

 

[plost33]

any ideas on a fix????

 

[VoG]

Once they've copied it you could:

Paste the data to a (hidden) worksheet

In the code that you've posted

- copy the UsedRange from the other sheet
- paste special into your unprotected sheet
- delete the UsedRange in the other sheet

 

[plost33]

could you add that for me tot he code listed below:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim Mylrow As Long, PLLrow As Long
Dim MyDate As Date, MyVsle As String
Dim R
Mylrow = Sheets("All DOR'S").Range("B18").End(xlDown).Row + 1
Sheets("all dor's").Activate
With ActiveSheet
    .Unprotect Password:="techedit"
End With
    
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
        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
    
    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
MsgBox "DOR data added successfully!"
End Sub

 

[VoG]

There's nothing in that code to do with users selecting and copying anything.