Create A UserForm and Its Function

[aeshacksouquiel]

Hi Guys,

Good Day.

I am creating a VL/SL tracker.

Can you help me with the code?

The scenario is that if I place the Employee Number I want the Name and the Department will be shown automatically.

And regarding the date list, Can I have a code that when you click February the days would be just up to 29?

Then if I save it the VL or SL will be put into VL-SL Employee Data with the exact date?

I hope you could help me with this.

I've attached the sample for your review.

Appreciate your feedback. Thank you so much...

-Mark-

[6StringJazzer]

Make the Employee Number a combo box since you know in advance all the employee numbers. This prevents a data entry error and removes the need for a Find button.

Don't use lists for months and days. It is easier to calculate it on the fly. Months is always 1-12.

If you want to use lists for Year and Type, it is easier to set the RowSource property of the combobox, rather than populating it in the code.

Require the user to select year, then month, then day. You have to know year and month first to know valid values for day.

Day Month and Year are poor control names because they are also built-in function names.

Using With when there is only one line of code does not give you any advantage, it just adds lines of code.

Disable Name and Dept text boxes because the user cannot enter data there.

Then if I save it the VL or SL will be put into VL-SL Employee Data with the exact date?

I don't understand what you mean here. You only have data for one month shown. I do not think this is a good design for your data.

[Tinbendr]

I don't know If I have the save button saving your date in the right place, but it's a start.

[aeshacksouquiel]

Make the Employee Number a combo box since you know in advance all the employee numbers. This prevents a data entry error and removes the need for a Find button.

Don't use lists for months and days. It is easier to calculate it on the fly. Months is always 1-12.

If you want to use lists for Year and Type, it is easier to set the RowSource property of the combobox, rather than populating it in the code.

Require the user to select year, then month, then day. You have to know year and month first to know valid values for day.

Day Month and Year are poor control names because they are also built-in function names.

Using With when there is only one line of code does not give you any advantage, it just adds lines of code.

Disable Name and Dept text boxes because the user cannot enter data there.

I don't understand what you mean here. You only have data for one month shown. I do not think this is a good design for your data.

Thanks for this 6StringJazzer. Appreciate your help

Another query is that what if I have a lot of Employees already around 500 employees is it more hustle to use the combo box instead of a label box. that if you enter will appear the name and the department?

For the save button, the input should be place on the date assigned to it and it would appear VL(Vacation Leave) with blue background or SL(Sick Leave) with red background. Like for example (Tan, Michael Sy February 29 2016 VL) then I should see in the VL-SL Employee Data from Tan, Michael Sy From February 29 the VL sign.

And could I also have in the Summary Sheet the pivot table/data including the months, SL/VL and the Name?

Thank you so much for your help appreciate it.
Hoping for your response . Thanks again.

-Mark-

[aeshacksouquiel]

I don't know If I have the save button saving your date in the right place, but it's a start.

HI Tinbendr,

Thank you for your feedback.
But yes, that's what I am looking for.

But could I use a label box instead of combo box for the employee number? Because I have a lot of data to work on. Could that be possible?

And for the result or when you save could it be possible that I could see a VL/SL input for whatever the data have been entered?
It is for Vacation Leave or a Sick Leave.

And could it be possible that if VL background color would be blue, and for SL background color would be red?


And for the Summary Sheet.
Could this be program. Like I can see the Employee No/Name/Month/VL/SL. Already attached the data.
For your review.xlsm

Thanks in advance. Appreciate your work a lot. I am so sorry I have a lot of queries. just need it for reporting purposes. thanks in advance.

-Mark-

[Tinbendr]

But could I use a label box instead of combo box for the employee number? Because I have a lot of data to work on. Could that be possible?

Sure, but why would you? With a combobox, you can start typing and the list will autocomplete.

And for the result or when you save could it be possible that I could see a VL/SL input for whatever the data have been entered? It is for Vacation Leave or a Sick Leave.

Just change the output of C to the cboType control.

And could it be possible that if VL background color would be blue, and for SL background color would be red?

Use conditional formatting for this. ExcelIsFun on youtube has several good examples of how to accomplish this.

And for the Summary Sheet.
Could this be program. Like I can see the Employee No/Name/Month/VL/SL. Already attached the data.

If you plan to have all the employees on the summary page, then use formulas to CountIf the results.

You should use naming conventions when dealing with controls like combobx and textbox. Use an abbreviated prefix to help identify it's function. For example: cboEmpNum for combobox employee number and txtMonth instead of just Month. (For this you should be using a date picker anyway.) Using controls with kown functions like Month can cause errors that can be hard to pinpoint.

[aeshacksouquiel]

Hi Tinbendr,

Thanks for the help. I appreciate your urgent response.

Sure, but why would you? With a combobox, you can start typing and the list will autocomplete.

I see, I am so sorry with this I am confuse. I thought it could just a list and not able to type the employee number.
I've tried putting an employee number which is not on the list and name would still how could i program it?

for this code...

Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim A As Long
Dim LastRow As Long
Dim C As Range

Set WS = Worksheets("VL-SL Employee Data")
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2:A" & LastRow)
Set C = .Find(Me.cbEmpnum, , xlValues)
If Not C Is Nothing Then
C.Offset(0, Me.cbDay + 2) = DateSerial(Me.cbYear, Me.cbMonth.ListIndex + 1, Me.cbDay)
End If
End With
End With
End Sub

How could I change that the TYPE would appear and not the date?

For the conditional formatting yes, I'll view it on youtube. but it is possible right to have a code for that?

Could countif be in the code? and if yes, how?

Appreciate your genuinely help. it really helps me a lot and it broaden my mind when it comes to macros.

I've attached the file still needed to be improve. Thank you very much in advance for helping me with the code. I have just a little background regarding macros. I'm sorry for that. Thanks for the time and effort.

-Mark-

[Tinbendr]

I've tried putting an employee number which is not on the list and name would still how could i program it?

Honestly, I would create another userform to enter new employees. But I'm confused as to why an employee would be taking leave, but not on the list already.

How could I change that the TYPE would appear and not the date?

Please Login or Register  to view this content.

For the conditional formatting

Highlight the range in question, Home -> Conditional Formatting -> Highlight Cell Rules -> Equal To. In the field type ="VL", and pick a fill color you want.
Do this a second time for the sick leave. Whenever the VL/SL get posted to the sheet (or enter by hand), the cond. format. will change the color accordingly.

I'll have to get back with you on the summary page.

[aeshacksouquiel]

Thank you for responding.

Honestly, I would create another userform to enter new employees. But I'm confused as to why an employee would be taking leave, but not on the list already.

Sorry for the confusion. I don't know if you'll get my point but here's how I've entered 101013 and Tan, Michael Sy would still appear even if his Employee Number is 101010. Could it have a code that will appear a msg box saying Employee Number does not exist?

Please Login or Register  to view this content.

I've used this code but nothing would appear. is it just applicable for just one month? what if VL-SL Employee data contains all months? How could VL/SL Appear?


Thanks for the code for conditional formatting. I thought it could have a code that could automatically input the background color for lesser mb/kb excel would used. I thought there is other way around not using a conditional formatting.


Could I use another Userform for Saturdays and Sundays would appear R(Rest Day) and H(for Holiday) and P(for regular work days) after doing VL/SL encoding?

Appreciate your effort helping me with my excel file.
Thank you for your consistent reply.
Have a great day Tinbendr!