VBA for Networkdays

[kakiebos]

Hi Everyone.

I'm still busy with a workbook for our weekly timesheets. I have created a UserForm for the user to fill in everyday. This information will then be sent to the relevant day in the spreadsheet. On top of the UserForm the day of the week as well as the date is displayed. I got the code to do that from Dave on this forum. Once again, Thanks Dave.

There is TextBoxes that the user have to fill in for the time worked. There is also boxes for any and all overtime worked. In our case all work carried out on weekends and South African public holidays is considered as overtime.

I have originally used the Networkdays function in the spreadsheet to highlight all the public holidays. I would like to incorporate that function in the UserForm. What I need to do is to have the TextBox for Normal time {tbNT} become invincible if the UserForm is called and the date on top (in the form of a label {lDate} ) is over a weekend or on a public holiday. The public holidays is stored in a table (with table name {PublicHolidays}). This table is located in the Instructions Sheet with the property name {shLists}. On public holidays the TextBox for public holiday overtime {tbPPHot} must become visible on the UserForm

Will it be possible to use a similar function in VBA? Your assistance will be highly appreciated.

Below is the code that I have for the initialization of the above UserForm

Private Sub UserForm_Initialize()
'PURPOSE: Position userform to center of Excel Window (important for dual monitor compatibility)
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault


'Start Userform Centered inside Excel Screen (for dual monitors)
  Me.StartUpPosition = 0
  Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
  Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)


    
'Dave's Code from Mr Excel Forum
'PURPOSE: Initialize the Resource UserForm
'Show the date and day at the top of the UserForm
    
    Dim sh As Worksheet
    Dim shAct As String
    Dim m As Variant
    Dim d As Date




    Set sh = shSummary
    shAct = ActiveSheet.CodeName
    d = sh.Range("Y7").Value
    
'display date for selected daily sheet
'if activesheet not a daily sheet show week start date
    m = Application.Match(shAct, Array("shMon", "shTue", "shWed", "shThur", "shFri", "shSat", "shSun"), 0)
    Me.lDate.Caption = CStr(Format(IIf(Not IsError(m), d + CLng(m) - 1, d), "dd MMMM YYYY"))
    Me.lDay.Caption = CStr(Format(IIf(Not IsError(m), d + CLng(m) - 1, d), "dddd"))
    
'Display Branch/Site
  Me.tbBranch.Value = Range("BranchSite")
End Sub