VBA Date Picker

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top