Results 1 to 2 of 2

VBA Date Picker

This is a discussion on VBA Date Picker within the Excel Questions forums, part of the Question Forums category; I need to have a cell on my worksheet allow a date to be picked from a month view, i.e. ...

  1. #1
    Board Regular AD_Taylor's Avatar
    Join Date
    May 2011
    Posts
    687

    Default VBA Date Picker

    I need to have a cell on my worksheet allow a date to be picked from a month view, i.e. show all the dates for that month and whichever one is clicked return that date.

    I know that there can be problems with using the system method of this and a way around it is to use a UserForm. I'm using the example from this site: http://www.vbaexpress.com/kb/getarticle.php?kb_id=543

    I've done some reworking of the code so that it fits my coding style but I'm hitting a problem. All of the command buttons (42 of them, 1 for each day + some extra) should have a bold font if they are for the month currently selected. Any not for the month currently selected should not have bold font.

    The code I'm using now is below. The line that should remove the bold,
    Code:
    Controls("D" & i).Font.Bold = False
    just isn't working. Putting 'Debug.Print' before it and after it shows that this line is actually making the text bold. Is there anyway around this so that the correct command buttons can be unbolded?

    Full Build Calendar code below:
    Code:
    Private Sub Build_Calendar()
        Dim dtDate          As Date
        Dim lngWkDay        As Long
        Dim dtAdjusted      As Date
        
        If bCal Then
            CommandButton1.SetFocus
            Me.Caption = CB_Mth.Value & " " & CB_Yr.Value
            
            dtDate = "1/" & CB_Mth.Value & "/" & CB_Yr.Value
            lngWkDay = Weekday(dtDate, vbMonday)
            
            For i = 1 To 42
                dtAdjusted = DateAdd("d", i - lngWkDay, dtDate)
                
                Controls("D" & i).Caption = Format(dtAdjusted, "d")
                Controls("D" & i).ControlTipText = Format(dtAdjusted, "dd/mm/yyyy")
                
                If Format(dtAdjusted, "mmmm") = CB_Mth.Value Then
                    If Controls("D" & i).BackColor <> &HC0C0C0 Then
                        Controls("D" & i).BackColor = &H80000018
                    End If
                    
                    Controls("D" & i).Font.Bold = True
                    
                    If Format(dtAdjusted, "dd/mm/yyyy") = Format(ThisDay, "dd/mm/yyyy") Then
                        Controls("D" & i).SetFocus
                    End If
                Else
                    If Controls("D" & i).BackColor <> &HC0C0C0 Then
                        Controls("D" & i).BackColor = &H8000000F
                    End If
                    
                    Controls("D" & i).Font.Bold = False
                    
                End If
            Next i
        End If
    End Sub
    Please test any VBA code I suggest in a copy of your file. If the code errors or deletes your data it is not able to be undone.

    Check out my public Excel Templates on my SkyDrive: http://sdrv.ms/1j37KgJ
    I specialise in trying to answer the more difficult questions (Eg copying a screenshot of cells and pasting them inside a cell comment)


    Home: Mac Book Pro | Snow Leopard | Excel for Mac 2011
    Home: Windows 7 | Excel 2010 (Running on Parallels Desktop as a VM)

  2. #2
    Board Regular AD_Taylor's Avatar
    Join Date
    May 2011
    Posts
    687

    Default Re: VBA Date Picker

    Strangely found a way around it. That exact same line works when placed in a sub such as 'CB_Mth_Change()' but not when placed in the 'Build_Calendar' sub. I'm assuming it's because the one is a user defined sub and the other is more of a system sub associated with the combo box.

    Whatever the reson it's working so that's good enough for me!
    Please test any VBA code I suggest in a copy of your file. If the code errors or deletes your data it is not able to be undone.

    Check out my public Excel Templates on my SkyDrive: http://sdrv.ms/1j37KgJ
    I specialise in trying to answer the more difficult questions (Eg copying a screenshot of cells and pasting them inside a cell comment)


    Home: Mac Book Pro | Snow Leopard | Excel for Mac 2011
    Home: Windows 7 | Excel 2010 (Running on Parallels Desktop as a VM)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com