Hi,
i need one help on this. I don't know it can be possible or not.
When i select the month "October" and year "2016" then 31 days shows but when i change the month of "November" then the label & Text box of "31,Oct,16,Monday" also shows. Actually i want when i change the month then these label and text box also hide. only show those labels and text boxes from first day to last day of the selected month.
please help
here is the code:
Private Sub cboEmpl_change()
Dim ws As Worksheet
Dim x As Long
Dim fcell As Range
Set ws = ThisWorkbook.Sheets("Employee_Details")
With ws
Set fcell = .Range("B:B").Find(Me.cboEmpl.Value, , xlValues, xlWhole)
If fcell Is Nothing Then Exit Sub
x = fcell.Row
Me.txtEmployeeID.Value = .Cells(x, 4).Value
Me.txtAlias.Value = .Cells(x, 3).Value
End With
End Sub
Private Sub cbomonth_Change()
If Me.cboyear.Value = "" Then
Exit Sub
Else
Call Rename_Captions
End If
End Sub
Private Sub cboyear_Change()
If Me.cbomonth.Value = "" Then
Exit Sub
Else
Call Rename_Captions
End If
End Sub
Sub Rename_Captions()
Dim x As Integer
Dim DaysInMonth As Long, i As Long
Dim MonthNum As Long
MonthNum = Month(DateValue("03/" & Me.cbomonth.Value & "/" & Me.cboyear.Value))
DaysInMonth = DateSerial(Me.cboyear.Value, MonthNum + 1, 1) - _
DateSerial(Me.cboyear.Value, MonthNum, 1)
ReDim Days(1 To DaysInMonth)
For i = 1 To DaysInMonth
Days(i) = DateSerial(Me.cboyear.Value, MonthNum, i)
Next
i = i - 1
For x = 1 To i
Me.Controls("lb_m" & x).Visible = True
Me.Controls("cbod" & x).Visible = True
Me.Controls("lb_m" & x).Caption = Format(Days(x), "DD,MMM,YY, DDDD")
Next x
End Sub
Private Sub ComboBox3_Change()
Dim x As Integer
If Me.ComboBox3.Value = "ANZ" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("ANZ").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "ASEAN" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Asean").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Morning" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Morning").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Afternoon" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Afternoon").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Evening" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Evening").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Night" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Night").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Support" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Support").RefersToRange)
Next
End If
End Sub
Private Sub UserForm_Initialize()
Dim x As Integer
For x = 1 To 31
Me.Controls("lb_m" & x).Visible = False
Me.Controls("cbod" & x).Visible = False
Next
End Sub
i need one help on this. I don't know it can be possible or not.
When i select the month "October" and year "2016" then 31 days shows but when i change the month of "November" then the label & Text box of "31,Oct,16,Monday" also shows. Actually i want when i change the month then these label and text box also hide. only show those labels and text boxes from first day to last day of the selected month.
please help
here is the code:
Private Sub cboEmpl_change()
Dim ws As Worksheet
Dim x As Long
Dim fcell As Range
Set ws = ThisWorkbook.Sheets("Employee_Details")
With ws
Set fcell = .Range("B:B").Find(Me.cboEmpl.Value, , xlValues, xlWhole)
If fcell Is Nothing Then Exit Sub
x = fcell.Row
Me.txtEmployeeID.Value = .Cells(x, 4).Value
Me.txtAlias.Value = .Cells(x, 3).Value
End With
End Sub
Private Sub cbomonth_Change()
If Me.cboyear.Value = "" Then
Exit Sub
Else
Call Rename_Captions
End If
End Sub
Private Sub cboyear_Change()
If Me.cbomonth.Value = "" Then
Exit Sub
Else
Call Rename_Captions
End If
End Sub
Sub Rename_Captions()
Dim x As Integer
Dim DaysInMonth As Long, i As Long
Dim MonthNum As Long
MonthNum = Month(DateValue("03/" & Me.cbomonth.Value & "/" & Me.cboyear.Value))
DaysInMonth = DateSerial(Me.cboyear.Value, MonthNum + 1, 1) - _
DateSerial(Me.cboyear.Value, MonthNum, 1)
ReDim Days(1 To DaysInMonth)
For i = 1 To DaysInMonth
Days(i) = DateSerial(Me.cboyear.Value, MonthNum, i)
Next
i = i - 1
For x = 1 To i
Me.Controls("lb_m" & x).Visible = True
Me.Controls("cbod" & x).Visible = True
Me.Controls("lb_m" & x).Caption = Format(Days(x), "DD,MMM,YY, DDDD")
Next x
End Sub
Private Sub ComboBox3_Change()
Dim x As Integer
If Me.ComboBox3.Value = "ANZ" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("ANZ").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "ASEAN" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Asean").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Morning" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Morning").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Afternoon" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Afternoon").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Evening" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Evening").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Night" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Night").RefersToRange)
Next
End If
If Me.ComboBox3.Value = "Support" Then
For x = 1 To 31
Me.Controls("cbod" & x).List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Support").RefersToRange)
Next
End If
End Sub
Private Sub UserForm_Initialize()
Dim x As Integer
For x = 1 To 31
Me.Controls("lb_m" & x).Visible = False
Me.Controls("cbod" & x).Visible = False
Next
End Sub