userform question

P

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.
 
Sort by date Sort by votes
Perhaps

Code: 
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
With UserForm4
    MyDate = Calendar1.Value
End With
        
Sheets("all dor's").Activate
    
Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
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 has already been entered! Please select another date."
    Exit Sub
End If

    '''' 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
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
compile error: end if without block if


highlighting line in red.

Rich (BB code): 
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
With UserForm4
    MyDate = Calendar1.Value
End With
        
Sheets("all dor's").Activate
    
Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
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 has already been entered! Please select another date."
    Exit Sub
End If
    '''' 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
 
Upvote 0
well i deleted that line and as i step thru the code now it appears that the section of code which tries to select a value for R is not finding a value, even though a selection is made in the listbox on the userform. the lines of code are:

For R = 0 To .lstbVessel.ListCount - 1
If .lstbVessel.Selected(R) = True Then ''get the vessle name
MyVsle = lstbVessel.List(R)
End If
Next


it just cycles thru 10 times, but finds nothing selected, although one is selected.
 
Upvote 0
I am still getting an error saying end with without with four line from the bottom of the following code:


Code: 
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
    
'Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
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(CDbl(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
    Else
    
    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



anyone know why???
 
Upvote 0
Try

Code: 
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
    
'Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
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(CDbl(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
 
Upvote 0
i got a type mismatch error on line:

Code: 
If IsNumeric(Application.Match(CDbl(MyDate), Range("H18:H" & Mylrow), 0)) And IsNumeric(Application.Match(CDbl(MyVsle), Range("I18:I" & Mylrow), 0)) Then
 
Upvote 0
Why do you keep changing the code that I gave you:confused:

Code: 
If IsNumeric(Application.Match(CDbl(MyDate), Range("H18:H" & Mylrow), 0)) And IsNumeric(Application.Match(MyVsle, Range("I18:I" & Mylrow), 0)) Then
 
Upvote 0
okay it appears to be working except that in the list box that has the values for myvsle i get the msgbox if one of the first four is selected even though the value I am selecting is diffrent and I should not get the msgbox. but if I select a value after the fourth one shown it works fine.
 
Upvote 0
vog:

i pasted int he lines in red to unprotect the sheet and now i am getting an error on line in green

Rich (BB code): 
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 ("techedit")
End With
    
'Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Userform textbox launches another userform
Replies
8
Views
358
FedElecQaEng
F
R
  • Question
VBA loop through date type mismatch
2
Replies
19
Views
779
Georgiboy
Georgiboy
P
  • Solved
Advice needed on where to put code that stops duplicate entries
Replies
5
Views
309
Pacman52
P
R
  • Question
Copy and Paste from one workbook to another...
Replies
6
Views
259
RandyD123
R
P
  • Question
Check one Combobox value against another Combobox value
Replies
6
Views
236
Pacman52
P

Forum statistics

Threads
1,214,974
Messages
6,122,536
Members
449,088
Latest member
RandomExceller01

Share this page

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