'Name: SansWH (without weekends or holidays) 'If the day is not a holiday, put it on the calendar. 'For every day in the month, determine if the day is a holiday. Private Sub WithWsansH(ByVal iMonth As Integer) 'Description: Creates a calendar for the specified month, including weekends, but not holidays. 'Name: WithWsansH (with weekends, without holidays) If WorksheetFunction.Weekday(datDay, 2) < 6 Then 'If the day is not a weekend, put it on the calendar. 'For every day in the month, determine if the day is a weekend. Private Sub WithHsansW(ByVal iMonth As Integer) 'Description: Creates a calendar for the specified month, including holidays, but not weekends. 'Name: WithHsansW (with holidays, without weekends) ThisWorkbook.Worksheets("Holidays").Cells(var, 2).Value) 'Add the appropriate formatting that indicates a holiday or weekend. Var = Application.Match(CDbl(datDay), ThisWorkbook.Worksheets("Holidays").Columns(1), 0) Set rng = Range(Cells(1, iCol), Cells(WorksheetFunction.CountA(Columns(1)), iCol)) 'Go through every day of the month and put the date on the calendar in the first row.įor datDay = DateSerial(iYear, iMonth, 1) To DateSerial(iYear, iMonth + 1, 0) Private Sub WithHW(ByVal iMonth As Integer) 'Description: Creates a calendar for the specified month, including both weekends and holidays. 'Name: WithWH (with weekends and holidays) 'Do some final cleanup, and then close out the sub. 'Delete the first worksheet, because there was not anything in it.ĪctiveWindow.Caption = "Yearly calendar " & iYear If And ThenĮlseIf And = False ThenĮlseIf = False And Then 'Call the private subs, depending on what options are chosen for the calendar. Range("A1").Value = "'" & ActiveSheet.Name & " " & iYear WorksheetFunction.CountA(wks.Columns(1)) + 1, 1)).Copy Range("A2") 'Copy the employee names to the first column, and add the dates across the remaining columns. Worksheets.Add after:=Worksheets(Worksheets.Count)
SMonth = Format(DateSerial(1, iMonth, 1), "mmmm")Īpplication.StatusBar = "Place month " & sMonth & " on." 'Create a new worksheet and label the worksheet tab with the name of the new month Set wks = ThisWorkbook.Worksheets("Employee") 'In this new workbook, clear out all the worksheets except for one. 'Create a new workbook to hold your new calendar. 'Initialize iYear with the value entered in the first spin button on the worksheet.
'In the current application, turn off screen updating, save the current state of the status bar, Sub CreateCalendar()ĭim iMonth As Integer, iCol As Integer, iCounter As Integer, iYear As Integer Your workbook must also contain a worksheet named "Employee" that lists the names of the employees you want on your calendar in column A starting in cell A3, and a worksheet named "Holidays" that lists the dates of the holidays in column A starting in cell A2 and the name of the holidays in column B starting in cell B2. To run this code, your workbook must have a worksheet named "Cover" that contains the following:Ī spin control that contains a list of years name "SpinButton1"Īn option button for the "with weekends" option named "OptionButton1"Īn option button for the "without weekends" option named "OptionButton2"Īn option button for the "with holidays" option named "OptionButton3"Īn option button for the "without holidays" option named "OptionButton4"
#2500 excel vba examples free download code
Sample code provided by: Holy Macro! Books, Holy Macro! It's 2,500 Excel VBA Examples
#2500 excel vba examples free download how to
The following code example shows how to use information in one workbook to create a scheduling calendar workbook that contains one month per worksheet and can optionally include holidays and weekends.
0 kommentar(er)
