userform question

plost33

Well-known Member
Joined
Oct 2, 2008
Messages
866
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
here is what i have now:

If MyDate = Value.Range("H18:H" & Mylrow) Then
MsgBox "DOR of the date DATE has already been entered! Please select another date."
Exit Sub
End If


but i am getting an error on the line in red. also instead of exit sub, how would i say just return to the beginning of the code, just showing the userform without closing??
 
Upvote 0
Try

Code:
If IsNumeric(Application.Match(MyDate, Range("H18:H" & Mylrow), 0)) Then
    MsgBox "DOR of the date DATE has already been entered! Please select another date."
    Exit Sub
End If
 
Upvote 0
that did not work. it did not stop my code from running, as i want it too, if the date matches. would it be because the the dates currently in those cells was not placed in my the code, they were typed in?? or is it perhaps the format of the date in those cells, m/d/yyyy?? heres my entire code:


Code:
Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim Mylrow As Long, PLLrow As Long
Dim MyDate As String, MyVsle As String
Dim R
Mylrow = Sheets("All DOR'S").Range("B18").End(xlDown).Row + 1
Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
With UserForm4
    MyDate = Calendar1.Value
    
    If IsNumeric(Application.Match(MyDate, Range("H18:H" & Mylrow), 0)) Then
    MsgBox "DOR of the date DATE has already been entered! Please select another date."
    Exit Sub
End If
    
    For R = 0 To .lstbVessel.ListCount - 1
        If .lstbVessel.Selected(R) = True Then ''get the vessle name
            MyVsle = lstbVessel.List(R)
        End If
    Next
    '''' 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 ''change to your userform
MsgBox "DOR data added successfully!"
End Sub
 
Upvote 0
Shouldn't it be?

Rich (BB code):
Dim MyDate As Date, MyVsle As String
 
Upvote 0
i made that change but it still is not working.
I just want the code to stop befor pasting in any data if the date is already in column H row 18 or higher. but it is not working right now.
 
Upvote 0
here is the one I used.. The date I used to populate the mydate variable was input into cell A1.. this serves the same function as the date from the calendar.


Code:
Sub test()
mydate = Range("A1").Value
Range("H1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = mydate Then
MsgBox ("Date already exists")
'code to show the form again
End
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
 
Upvote 0
i think there has be be a fast and more efficient way to check. vog any ideas why yours did not work?
 
Upvote 0
how many records will you have in column H?

you could turn off screenupdating if you are worried about it running slowly..
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top