Selecting dates from a drop down menu

  • Thread starter Thread starter robert_woodie
  • Start date Start date
R

robert_woodie

Is it possible to be able to select a date from a small drop down
calendar instead of putting it in manually everytime??...if so how!?

Thanks in advance

:)
 
Robert,

You can use a Calendar ActiveX control (Insert - Object ...). This'll get
you started. While in design mode, double click it, and put in this code:

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
End Sub

You'll need code to make it appear when desired. This'll get you started.
It'll appear when you click in column B, show the date in the active cell,
allow you to click the calendar to change the date in the active cell, and
go away when you select another column. It goes in the sheet module (the
same sheet you got put in when you double-clicked the calendar in design
mode above.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B1").EntireColumn) Is Nothing Then
Calendar1.Visible = True
Calendar1.Value = Target
Else
Calendar1.Visible = False
End If
End Sub

Earl Kiosterud
mvpearl omitthisword at verizon period net
 
that help was great earl but is there some code to make it move relevant
to the cell selection as at the moment it only stays where positioned.

Also how do i make it appear when EITHER row B or G is selected
 
the easier question first:

If Not Intersect(Target, Range("B1,g1").EntireColumn) Is Nothing Then

And this seemed to work ok for me for positioning directly to the right of the
target.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B1,g1").EntireColumn) Is Nothing Then
With Calendar1
.Visible = True
.Value = Target
.Top = Target.Offset(0, 1).Top
.Left = Target.Offset(0, 1).Left
End With
Else
Calendar1.Visible = False
End If
End Sub
 
thanks dave, it does exactlly what i wanted but for some reason when i
click on a day in the calendar it wont put it in the active cell
 
That still needs the code that Ron de Bruin (if I remember correctly) gave you.
(I don't have it handy.)

I googled for posts you authored and couldn't find it. If you can't make it
work, include it in your next post.
 
thanks it works now. didnt at first so i deleted the calendar and
started again, this seems to have solved it!!
 
When using this calendar in a worksheet if a click on the row number t
highlight the entire row i get a VB runtime error (1004). Clickin
Debug takes me to the line: .Top=target ....

SUBMACRO:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B1,g1").EntireColumn) Is Nothing Then
With Calendar1
.Visible = True
.Value = Target
.Top = Target.Offset(0, 1).Top
.Left = Target.Offset(0, 1).Left
End With
Else
Calendar1.Visible = False
End If
End Sub

Is there anyway round this?

Thanks
Rober
 
What do you want to happen when you select more than one cell (like that whole
row)?

If you want the sub to not do anything, then start your code like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if target.cells.count > 1 then exit sub
''''your code''''

If you want to show the calendar for the first cell in the intersection of
(columns B or G) and the selection:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B1,g1").EntireColumn) Is Nothing Then
Set Target = Intersect(Target, Range("b1,g1").EntireColumn)(1)
With Calendar1
.Value = Target
.Top = Target.Offset(0, 1).Top
.Left = Target.Offset(0, 1).Left
.Visible = True
End With
Else
Calendar1.Visible = False
End If
End Sub

ps. I moved the .visible to the bottom of the with/end with portion. Move it,
then make it visible was less distracting than showing, then moving (well, for
me anyway).

And another way to write this:
Intersect(Target, Range("B1,g1").EntireColumn)
is
Intersect(Target, Range("B:b,g:g"))

(just another one of my likes <bg>.)

Or did you really mean B through G?
Intersect(Target, Range("B1:g1").EntireColumn)
 
Back
Top