VBA for Networkdays


Board Regular
Jun 10, 2018
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

Forum statistics

Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...