hasemmrs.blogg.se - How to add calendar drop down in excel 2010

Range("A4").Offset(x * 2, 0).EntireRow.Insert ' Create Entry cells, format them centered, wrap text, and border ' Exit loop when calendar has correct number of days ' Stop when the last day of the month has been entered. ' Do only if current cell is not in Row 3 and is in Column 1.ĮlseIf cell.Row > 3 And cell.Column = 1 ThenĬell.Value = cell.Offset(-1, 6).Value + 1 ' Exit loop when calendar has correct number of If cell.Value > (FinalDay - StartDay) Then ' Stop when the last day of the month has been ' Do if current cell is not in 1st column.Ĭell.Value = cell.Offset(0, -1).Value + 1 ' Loop through range a3:g8 incrementing each cell after the "1" ' Place a "1" in cell position of the first day of the chosen

' Set variable and calculate the first day of the next month.įinalDay = DateSerial(CurYear, CurMonth + 1, 1) ' Set variables to identify the year and month as separate ' Set variable and get which day of the week the month starts. Range("a1").Value = Application.Text(MyInput, "mmmm yyyy") ' Put inputted month and year fully spelling out into "a1". ' Prepare a3:g7 for dates with left/top alignment, size, height ' Prepare a2:g2 for day of week labels with centering, size, HorizontalAlignment = xlCenterAcrossSelection ' Center the Month and Year label across a1:g1 with appropriate ' Prepare cell for Month and Year as fully spelled out. StartDay = DateValue(Month(StartDay) & "/1/" & _ ' - if so, reset StartDay to first day of month. ' Check if valid date but not the first of the month ' Get the date value of the beginning of inputted month. ' Allow user to end macro with Cancel in InputBox. MyInput = InputBox("Type in Month and year for Calendar ") ' Use InputBox to get desired month and year and set variable ' Clear area a1:g14 including any previous calendar. ' Prevent screen flashing while drawing calendar.

' Unprotect sheet if had previous calendar to prevent error.ĪctiveSheet.Protect DrawingObjects:=False, Contents:=False, _ Sample Visual Basic procedure Sub CalendarMaker() In Excel 2010, the option is available on the Customize Ribbon menu.

In Excel 2007, the option is available on the Popular menu. If the Developer ribbon is not visible, open Excel Options to enable it.

Select CalendarMaker, and then select * Run to create the calendar.

On the * Developer ribbon, click Macros.

On the Developer ribbon, select Visual Basic.

Select CalendarMaker, and then select Run to create the calendar.

On the Tools menu, point to Macro, and then select Macros.

On the File menu, select Close and Return to Microsoft Excel.

Paste the Visual Basic for Applications script from the "Sample Visual Basic procedure" section into the module sheet.

On the Tools menu, point to Macro, and then select Visual Basic Editor.

To create the calendar, follow these steps. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. Microsoft provides programming examples for illustration only, without warranty either expressed or implied.