Restricting available dates in calendar userform

[bjohnson]

I have developed a userform calendar in my spreadsheet, but I need to
know how to restrict the dates that can be selected to Friday's ONLY.
I also am trying to figure out a way to write code that will pop a
message box if specific Fridays are chosen (about 12 different ones
throught the year). That message box will prompt the user to run a
specific macro. In a perfect world, it would then restrict them from
diong too much else to that page until that macro was run. Am I
asking for too much?? Thanks for any help you can provide!

 

[Guest]

There are three method s that you can use. first you can havve a macro
automatically run when the workbook is opened instead of running a manual
macro. The 2nd method is to use a wroksheet_change function that will run
when data is entered in a cell. the 3rd method is a macro when the cell is
selected.

 

[Guest]

I put a calendar control on a userform and put the following code in the
userform code module:

If Weekday(x, vbMonday) <> 5 Then 'if not a Friday
Calendar1.ValueIsNull = True 'unselects a date on calendar
Else
Select Case x
Case DateValue("3-Aug-2007"), DateValue("31-Aug-2007")
Calendar1.ValueIsNull = True
MsgBox "Not that Friday"
Case Else
MsgBox x & " is a Friday"
End Select
End If
End Sub

Clicking on any non-Friday results in the date clicked on being unselected.
Clicking on any Friday results in the message "'date' is a Friday"
Clicking on Friday 3rd Aug 2007 or on Friday 31 August 2007 results in a
message "Not that Friday".

You get the drift.

 

[Rick Rothstein \(MVP - VB\)]

I have developed a userform calendar in my spreadsheet, but I need to

know how to restrict the dates that can be selected to Friday's ONLY.

You could simply select the Friday for the week of any day the user clicks
on. Here is the code to do that

For a Calendar Control
===================
Private Sub Calendar1_Click()
With Calendar1
.Value = .Value + 6 - Weekday(.Value)
End With
End Sub


For a MonthView Control
===================
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
With MonthView1
.Value = .Value + 6 - .DayOfWeek
End With
End Sub


Rick