Results 1 to 15 of 15

VB Calendar Macro to highlight current date

Threaded View

  1. #1
    Registered User
    Join Date
    10-07-2008
    Location
    virginia
    Posts
    26

    VB Calendar Macro to highlight current date

    I have a macro that displays a calendar with code to highlight the current date, but this part of the code does not work and the current date is not highlighted.

    Sub CalendarMaker()
    
          ' Unprotect sheet if had previous calendar to prevent error.
           ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
              Scenarios:=False
           ' Prevent screen flashing while drawing calendar.
           Application.ScreenUpdating = False
           ' Set up error trapping.
           On Error GoTo MyErrorTrap
           ' Clear area c5:am5 including any previous calendar.
           Range("C5:AM5").Clear
           ' Use InputBox to get desired month and year and set variable
           ' MyInput.
           MyInput = InputBox("Type in Month and year for Calendar ")
           ' Allow user to end macro with Cancel in InputBox.
           If MyInput = "" Then Exit Sub
           ' Get the date value of the beginning of inputted month.
           StartDay = DateValue(MyInput)
           ' Check if valid date but not the first of the month
           ' -- if so, reset StartDay to first day of month.
           If DAY(StartDay) <> 1 Then
               StartDay = DateValue(Month(StartDay) & "/1/" & _
                   Year(StartDay))
           End If
           ' Prepare cell for Month and Year as fully spelled out.
           Range("C5").NumberFormat = "mmmm yyyy"
           ' Center the Month and Year label across c5:am5 with appropriate
           ' size, height and bolding.
           With Range("C5:AM5")
               .ColumnWidth = 2.57
               .HorizontalAlignment = xlCenterAcrossSelection
               .VerticalAlignment = xlCenter
               .Font.Size = 18
               .Font.Bold = True
               .RowHeight = 35
           End With
           
           ' Prepare C7:AM7 for dates with center alignment, size, height
           ' and bolding.
           With Range("C7:AM7")
               .ColumnWidth = 2.57
               .HorizontalAlignment = xlCenter
               .VerticalAlignment = xlCenter
               .Font.Size = 10
               .Font.Bold = True
               .RowHeight = 12.75
           End With
           ' Put inputted month and year fully spelling out into "c5".
           Range("C5").Value = Application.Text(MyInput, "mmmm yyyy")
           ' Set variable and get which day of the week the month starts.
           DayofWeek = Weekday(StartDay)
           ' Set variables to identify the year and month as separate
           ' variables.
           CurYear = Year(StartDay)
           CurMonth = Month(StartDay)
           ' Set variable and calculate the first day of the next month.
           FinalDay = DateSerial(CurYear, CurMonth + 1, 1)
           ' Place c "1" in cell position of the first day of the chosen
           ' month based on DayofWeek.
           Select Case DayofWeek
               Case 1
                   Range("C7").Value = 1
               Case 2
                   Range("D7").Value = 1
               Case 3
                   Range("E7").Value = 1
               Case 4
                   Range("F7").Value = 1
               Case 5
                   Range("G7").Value = 1
               Case 6
                   Range("H7").Value = 1
               Case 7
                   Range("I7").Value = 1
           End Select
           ' Loop through range C7:AM7 incrementing each cell after the "1"
           ' cell.
           For Each cell In Range("C7:AM7")
               ColCell = cell.Column
               ' Do if "1" is in first column.
               If cell.Column = 1 Then
               ' Do if current cell is not in 1st column.
                    ElseIf cell.Column <> 1 Then
                        If cell.Offset(0, -1).Value >= 1 Then
                            cell.Value = cell.Offset(0, -1).Value + 1
                                'Stop when the last day of the month has been
                                'entered
                                If cell.Value > (FinalDay - StartDay) Then
                                    cell.Value = ""
                                    ' Exit loop when calendar has correct number of
                                    ' days shown.
                           Exit For
                       End If
                   End If
                 ' Stop when the last day of the month has been entered.
                 If cell.Value > (FinalDay - StartDay) Then
                     cell.Value = ""
                     ' Exit loop when calendar has correct number of days
                     ' shown.
                     Exit For
                 End If
            End If
        Next
        
        For Each cell In Range("C7:AM7")
    
            If cell.Value = "=TODAY()" Then
            
                With Range("C7:AM7")
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=Today()"
                    .FormatConditions(1).Interior.ColorIndex = 1
                End With
            
            End If
        Exit For
        
       Next
        
          
          ' Turn on gridlines
           ActiveWindow.DisplayGridlines = True
           ' Protect sheet to prevent overwriting the dates.
           ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
              Scenarios:=True
    
           ' Prevent going to error trap unless error found by exiting Sub
           ' here.
           Exit Sub
       ' Error causes msgbox to indicate the problem, provides new input box,
       ' and resumes at the line that caused the error.
    MyErrorTrap:
           MsgBox "You may not have entered your Month and Year correctly." _
               & Chr(13) & "Spell the Month correctly" _
               & " (or use 3 letter abbreviation)" _
               & Chr(13) & "and 4 digits for the Year"
           MyInput = InputBox("Type in Month and year for Calendar")
           If MyInput = "" Then Exit Sub
           Resume
       
     End Sub
    Suggestions?

    Thanks,
    jlcford
    Last edited by jlcford; 10-10-2008 at 02:00 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. create macro to delete row with a date previous to current date
    By laserk7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2008, 12:36 PM
  2. Advanced Timesheet
    By DaKohlmeyer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-28-2008, 04:49 PM
  3. Don't open the VB Editor when a macro is executed
    By rajusampathirao in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-21-2008, 06:55 AM
  4. macro for matching date and changing cell color and merging like cells
    By learning_excel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2007, 06:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1