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

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...