Userform vba problem

[Lobsterboy1]

Hi,
I have a userform that with some code that I am having a problem with, my code is

Private Sub CommandButton1_Click()
Dim shp As Control
Dim Txt As String
For Each shp In Me.Controls
    If TypeName(shp) = "TextBox" Or TypeName(shp) = "ComboBox" Then
        If shp.value = vbNullString Then
            Txt = Txt & Chr(10) & shp.Name
        End If
    End If
Next shp
If Not Txt = vbNullString Then
    MsgBox """No Values In """ & Chr(10) & Mid(Txt, 2)
 Exit Sub
End If
    Range("A1").Select
    ActiveCell.End(xlDown).Select
    lastrow = ActiveCell.Row
    Cells(lastrow + 1, 1).value = DATETXT.Text
    Cells(lastrow + 1, 2).value = SHIFTCBOX.Text
    Cells(lastrow + 1, 3).value = EMPLOYEECBOX.Text
    Cells(lastrow + 1, 4).value = ITEMCBOX.Text
    Cells(lastrow + 1, 5).value = QTYTXT.Text
    Range("A2").Select
    DATETXT.Text = ""
    SHIFTCBOX.Text = ""
    EMPLOYEECBOX.Text = ""
    ITEMCBOX.Text = ""
    QTYTXT.Text = ""
End Sub

I am getting an error on the lines underlined. The error is "run time error '380' Could not set the Text Property. Invalid property value."

I think it is because the combobox's are set to only allow data from the dropdown list and there is no blank in the list. How do I get the userform to reset to all the boxes blank at the end of my code.

Cheers for any help.

 

[Carpaccio]

Hi Lobsterboy1
Can I ask why you need to set the comboboxes to "" at the end of your sub?
Don't the selections get lost when the Sub ends?

 

[JMeendering]

    DATETXT.Value = ""
    SHIFTCBOX.Value = ""
    EMPLOYEECBOX.Value= ""
    ITEMCBOX.Valuet = ""
    QTYTXT.Value = ""

 

[Lobsterboy1]

Hi Lobsterboy1
Can I ask why you need to set the comboboxes to "" at the end of your sub?
Don't the selections get lost when the Sub ends?

Hi, no the last selection stays in the boxes which doesn't look as tidy for the people who will be using it.

    DATETXT.Value = ""
    SHIFTCBOX.Value = ""
    EMPLOYEECBOX.Value= ""
    ITEMCBOX.Valuet = ""
    QTYTXT.Value = ""

That's it, I have been messing about for ages with this. Thanks for that.

I have also found another problem which I wonder if you could help with. I have some more code

Private Sub DATETXT_Change()


    Dim Char As String
    Dim x As Date
    Dim y As Date
    Char = Right(DATETXT.Text, 1)
    Select Case Len(DATETXT.Text)
    Case 1 To 2, 4 To 5, 7 To 8
        If Char Like "#" Then
            If Len(DATETXT) = 8 Then
                On Error Resume Next
                x = DateValue(DATETXT.Text)
                y = DateSerial(Right(DATETXT, 2), Mid(DATETXT, 4, 2), Left(DATETXT, 2))
                If Err = 0 And x = y Then
                    On Error GoTo 0
                    Exit Sub
                Else
                    Err.Clear
                    On Error GoTo 0
                    DATETXT.SelStart = 0
                    DATETXT.SelLength = Len(DATETXT.Text)
                    MsgBox "Please enter a valid date in the form dd/mm/yy", vbCritical + vbOKOnly, "Error"
                    Exit Sub
                End If
            Else
                Exit Sub
            End If
        End If
    Case 3, 6
        If Char Like "/" Then Exit Sub
    End Select
    
    On Error Resume Next
    DATETXT.Text = Left(DATETXT.Text, Len(DATETXT.Text) - 1)
    DATETXT.SelStart = Len(DATETXT.Text)


End Sub

Which I am trying to use to make the input the date properly. It works in as much if the date is wrong ie 37/15/17 it will throw up the error box but it will let them enter 37/15 and then move to the next box. How can I adjust the code to make sure they fill in dd/mm/yy.

Cheers.

 

[mikerickson]

Have you set the Combobox's .MatchRequired property to False and the .MatchEntry to matchEntryNone.

Or, if you don't want to change those properties, you could set the .ListIndex of the combobox's to -1 rather than setting their .Text property.

 

[Lobsterboy1]

Hi, J.Meendering solution has sorted the first problem for me, I am stumped on my second problem which is a textbox.
Cheers for any help.

 

[JMeendering]

if i understand correctly, you are trying to validate that the value entered into the textbox is a valid date in the dd/mm/yy format.
is the year going to be always in the 2000's or dealing with 1900's too?

I think this might work for what you are trying to accomplish
alternative would be to use the Date Time picker control if available in your version of excel.

Private Sub DATETXT_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'only allows numeric and /
Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc("/")
    Case Else
        KeyAscii = 0
End Select
End Sub

Private Sub DATETXT_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
' makes sure the date is a valid date
Dim dtArr As Variant
Dim dtDay As Integer
Dim dtMonth As Integer
Dim dtYear As Integer
Dim dtEOM As Date
dtArr = Split(DATETXT, "/") ' split the date into array
'first validation make sure all parts of the date are present
If Not UBound(dtArr) = 2 Then GoTo DateError 'if there are not 3 parts to the array then it is not a valid date
'set the variables
dtDay = Val(dtArr(0)) ' first part of the array is the day
dtMonth = Val(dtArr(1))
dtYear = Val(dtArr(2)) + 2000 ' adding 2000 to the year date so will be in current millinium format when dealing with the rest of the date formatting, will convert 16 to 2016


'additional date validations
If dtYear > Year(Now()) + 1 Then GoTo DateError ' just make sure they are not using some way out there number


If dtDay > Day(DateSerial(dtYear, dtMonth + 1, 0)) Then GoTo DateError ' if the day is greater than what the month allows then not a valid date
If dtMonth > 12 Or dtMonth < 1 Then GoTo DateError ' if the month is greater than 12
If IsDate(DateSerial(dtYear, dtMonth, dtDay)) = False Then GoTo DateError ' one last check construct the date if it is not a valid date then not a valid date.
DATETXT.Value = Format(DateSerial(dtYear, dtMonth, dtDay), "DD/MM/YY")
Exit Sub
DateError:


DATETXT.Value = "" 'clear the text box
MsgBox "Please enter a valid date in the form dd/mm/yy", vbCritical + vbOKOnly, "Error"


Exit Sub


End Sub