Hi all.
Another problem. In my worksheet I have a total of 5 sheets. 1 of my sheets has a formula for calculating number of days off taken from a start date and end date, as follows.
Start Date
=DATE(YEAR(TODAY()),1,9 )
End Date
=DATE(YEAR(TODAY()),2,12 )
Number of days off
=NETWORKDAYS(Employee_Leave_Tracker!$B4,Employee_Leave_Tracker!$C4,lstHolidays)
So, when using my UserForm to input these days, it is not including the formula, so simply enters the data literally as a date.
Here is my VB code for the UserForm.
Private Sub CommandButton1_Click()
CommandButton1.Show
End Sub
Private Sub UserForm_Initialize()
'Empty StartDateBox
StartDateBox.Value = ""
'Empty EndDateBox
EndDateBox.Value = ""
'Empty selectemployeeBox
selectemployeeBox.Clear
'Fill selectemployeeBox
With selectemployeeBox
Dim rngselectemployeeBox As Range
Dim ws As Worksheet
Set ws = Worksheets("List_of_Employees")
For Each rngselectemployeeBox In ws.Range("lstEmployees")
Me.selectemployeeBox.AddItem rngselectemployeeBox.Value
Next rngselectemployeeBox
End With
'Empty LeaveTypeBox
LeaveTypeBox.Clear
'Fill LeaveTypeBox
With LeaveTypeBox
Dim rngLeaveTypeBox As Range
Set ws = Worksheets("Leave_Types")
For Each rngLeaveTypeBox In ws.Range("lstHolidayTypes")
Me.LeaveTypeBox.AddItem rngLeaveTypeBox.Value
Next rngLeaveTypeBox
End With
'Set Focus on selectemployeeBox
selectemployeeBox.SetFocus
End Sub
Private Sub SaveEntryBox_Click()
Dim i As Integer
'position cursor in the correct cell A4.
Range("A4").Select
i = 1 'set as the first ID
'validate first three controls have been entered...
If Me.selectemployeeBox.Text = Empty Then 'name
Me.selectemployeeBox.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If
If Me.StartDateBox.Text = Empty Then 'StartDate
Me.StartDateBox.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If
If Me.EndDateBox.Text = Empty Then 'EndDate
Me.EndDateBox.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If
If Me.LeaveTypeBox.Text = Empty Then 'LeaveType
Me.LeaveTypeBox.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If
'if all the above are false (OK) then carry on.
'check to see the next available blank row start at cell A2...
Do Until ActiveCell.Value = Empty
ActiveCell.Offset(1, 0).Select 'move down 1 row
i = i + 1 'keep a count of the ID for later use
Loop
'Populate the new data values into the 'Data' worksheet.
ActiveCell.Value = i 'Next ID number
ActiveCell.Offset(0, 0).Value = Me.selectemployeeBox.Text 'set col A
ActiveCell.Offset(0, 1).Value = Me.StartDateBox.Text 'set col B
ActiveCell.Offset(0, 2).Value = Me.EndDateBox.Text 'set col C
ActiveCell.Offset(0, 3).Value = Me.LeaveTypeBox.Text 'set col D
'Clear down the values ready for the next record entry...
Me.selectemployeeBox.Text = Empty
Me.StartDateBox.Text = Empty
Me.EndDateBox.Text = Empty
Me.LeaveTypeBox.Value = Empty
Me.StartDateBox.SetFocus 'positions the cursor for next record entry
End Sub
Private Sub ClearBox_Click()
Call UserForm_Initialize
End Sub
Private Sub CancelBox_Click()
'close the form (itself)
Unload Me
End Sub
How can I include the formulas using my UserForm ?
And Im still learning Excel and VB
Bookmarks