PDA

View Full Version : VBA Validation Fields and Date and Time Picker



AL1483
10-15-2019, 12:31 PM
Hello all,

I have been put in charge of modifying a macro that generates files for new hires afetr filling out a user form, my issues are the following. The CboxCorpName field should only populate based on the values selected in the cboxSite field and its not doing that. Also, my cboxHour, cboxMinute and so forth I would like to have a calendar and clock instead of having to select the date and time from a few drop downs I created, and finally I want to remove the Generate Contract part of the userform and when I do the CorpName box doent load information anymore.

There used to be a date and time picker available but I can't seem to find it anymore in Office 365. Any input would be greatly appreciated to make this code look better and more tidy sorry for the mess.


Private Sub btnStartProcess_Click()
Dim MyWB As Workbook
If ValidFields = True Then
Set MyWB = ThisWorkbook
Call mProcess.CreatePassNewHire(MyWB)
MsgBox "Se ha generado correctamente el correo y/o los archivos para el nuevo ingreso"
End If
End Sub


Private Sub btnOpenFileFormatWorkDay_Click()
Dim intChoice As Integer
Dim strFileToOpen As String


Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show


If intChoice <> 0 Then


strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Me.txtFileFormatWorkDay.Text = strPath
End If
End Sub




Private Sub cboxGenerateContract_Change()
If cboxGenerateContract.Text = "Si" Then
framnewHireContract.Enabled = True
LabelContract.Enabled = True

With Me.cboxCorpName
.Clear
On Error Resume Next
MyWB.Worksheets("Catalogo").ShowAllData
Set MyWB = ThisWorkbook
c = MyWB.Sheets("Catalogo").Cells.CurrentRegion.Rows.Count
For Each rw In MyWB.Sheets("Catalogo").Range("B2:B" & c)
If rw.row <> 1 Then
.AddItem MyWB.Sheets("Catalogo").Range("D" & rw.row).Value
End If
Next
MyWB.Worksheets("Catalogo").ShowAllData
End With
ElseIf cboxGenerateContract.Text = "No" Then
framnewHireContract.Enabled = False
LabelContract.Enabled = False
End If
End Sub


Private Sub cboxSite_Change()
Dim c, i As Long
Dim rw As Range
Dim MyWB As Workbook


If cboxSite <> "" Then
With Me.cboxCorpName
.Clear
On Error Resume Next
MyWB.Worksheets("Catalogo").ShowAllData
Set MyWB = ThisWorkbook
c = MyWB.Sheets("Catalogo").Cells.CurrentRegion.Rows.Count
MyWB.Sheets("Catalogo").Range("A1:D1").AutoFilter Field:=1, Criteria1:=cboxSite
For Each rw In MyWB.Sheets("Catalogo").Range("D2:D" & c).SpecialCells(xlCellTypeVisible)
If rw.row <> 1 Then
.AddItem MyWB.Sheets("Catalogo").Range("D" & rw.row).Value
End If
Next
MyWB.Worksheets("Catalogo").ShowAllData
End With
End If
End Sub


Private Sub chkboxViewNewFilePassContract_Click()
If chkboxViewNewFilePassContract.Value = True Then
txtNewFilePassContract.PasswordChar = ""
ElseIf chkboxViewNewFilePassContract.Value = False Then
txtNewFilePassContract.PasswordChar = "*"
End If
End Sub


Private Sub UserForm_Initialize()
Dim i As Long
With Me.cboxHour
.AddItem "HH"
For i = 1 To 12
.AddItem i
Next i
.Text = "HH"
End With


With Me.cboxMinute
.AddItem "MM"
.AddItem "00"
.AddItem "05"
.AddItem "10"
.AddItem "15"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "35"
.AddItem "40"
.AddItem "45"
.AddItem "50"
.AddItem "55"
.Text = "MM"
End With


With Me.cboxPeriod
.AddItem "PM"
.AddItem "AM"
End With


With Me.cboxGenerateContract
.AddItem "Si"
.AddItem "No"
.Text = "Si"
End With


With Me.cboxNewHireGender
.AddItem "Femenino"
.AddItem "Másculino"
End With


With Me.cboxYear
.AddItem "AA"
For i = Year(Now) To Year(Now) + 1
.AddItem i
Next i
.Text = "AA"
End With


With Me.cboxMonth
.AddItem "MM"
For i = 1 To 12
.AddItem i
Next i
.Text = "MM"
End With


With Me.cboxDay
.AddItem "DD"
For i = 1 To 31
.AddItem i
Next i
.Text = "DD"
End With


With Me.cboxSite
.AddItem "Aguascalientes"
.AddItem "GDL Norte"
.AddItem "GDL Sur"
.AddItem "JRZ Norte"
.AddItem "JRZ Sur"
.AddItem "Queretaro"
.AddItem "Reynosa"
.AddItem "San Luis Rio Colorado"
.AddItem "Tijuana"
End With
End Sub


Private Function ValidFields() As Boolean
If Len(txtNewHireName.Text) = 0 Then
MsgBox "Escribe el nombre del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireEmail.Text) = 0 Then
MsgBox "Escribe el correo personal del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If cboxSite.Text = "" Then
MsgBox "Seleccione el campus del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If


If Len(txtRoom.Text) = 0 Then
MsgBox "Escribe el nombre de la sala", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtContactName.Text) = 0 Then
MsgBox "Escribe el nombre del Contacto del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtContactPhoneExt.Text) = 0 Then
MsgBox "Escribe la extensión del Contacto del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If IsNumeric(txtContactPhoneExt.Text) = False Then
MsgBox "Escribe el nombre del Contacto del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If




If cboxYear.Text = "AA" Then
MsgBox "Seleccione el año de ingreso del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If cboxMonth.Text = "MM" Then
MsgBox "Seleccione el mes de ingreso del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If cboxDay.Text = "DD" Then
MsgBox "Seleccione el día de ingreso del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If cboxHour.Text = "HH" Then
MsgBox "Seleccione la hora de ingreso del nuevo ingresoo", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If cboxMinute.Text = "MM" Then
MsgBox "Seleccione los minutos de ingreso del nuevo ingresoo", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If cboxPeriod.Text = "" Then
MsgBox "Seleccione la periodo de ingreso del nuevo ingresoo", vbCritical, "Error"
ValidFields = False
Exit Function
End If


If cboxGenerateContract.Text = "Si" Then
If Len(txtNewFilePassContract.Text) = 0 Then
MsgBox "Escribe la contraseña para el contrato del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireAge.Text) = 0 Then
MsgBox "Escribe la edad del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If IsNumeric(txtNewHireAge.Text) = False Then
MsgBox "La edad del nuevo ingreso debe ser númerica", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If cboxNewHireGender.Text = "" Then
MsgBox "Selecciona el genero del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If


If Len(txtNewHireRFC.Text) = 0 Then
MsgBox "Escribe el RFC del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireRFC.Text) < 13 Then
MsgBox "El RFC del nuevo ingreso debe de ser de 13 carácteres", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireRFC.Text) > 13 Then
MsgBox "El RFC del nuevo ingreso debe de ser de 13 carácteres", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireCURP.Text) = 0 Then
MsgBox "Escribe el CURP del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireCURP.Text) < 18 Then
MsgBox "El CURP del nuevo ingreso debe de ser de 18 carácteres", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireCURP.Text) > 18 Then
MsgBox "El CURP del nuevo ingreso debe de ser de 18 carácteres", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireAddress.Text) = 0 Then
MsgBox "Escribe la dirección del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireColony.Text) = 0 Then
MsgBox "Escribe la colonia del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtNewHireCity.Text) = 0 Then
MsgBox "Escribe el municipio del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
If Len(txtJob.Text) = 0 Then
MsgBox "Escribe el nombre del puesto del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If


If txtJobSalary.Text = "" Then
MsgBox "Escribe el salario del del nuevo ingreso", vbCritical, "Error"
ValidFields = False
Exit Function
End If
End If


ValidFields = True
End Function