Calendar

[Guest]

I have recently started teaching myself VBA and am still getting to grips
with it. I have created a calendar and its all working fine but how do I get
it to only pop up if they select a certain column or cell.

Regards

 

[Dave Peterson]

Maybe you could use a worksheet_selectionchange event.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then
Exit Sub
Else
UserForm1.Show
End If
End With
End Sub

Or you could tie into a rightclick or doubleclick event.

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[Guest]

In the Worksheet_SelectionChange event hander, you'll need something like this:

If Target.Address = "$A$1" Then
'pop up your calendar when user selects cell A1
End If


If you wanted to pop up your calendar when a certain column is selected, you
do it like this:

If Target.Column = 1 Then
'pop up your calendar when user selects any cell in column A
End If


Note that the selectionchange event does not distinguish wether the cell was
selected via a mouse click or by moving the selection cursor with the
keyboard.

 

[Guest]

Tried this out but I am getting nowhere with it.

Maybe you could use a worksheet_selectionchange event.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then
Exit Sub
Else
UserForm1.Show
End If
End With
End Sub

Or you could tie into a rightclick or doubleclick event.

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[Ron de Bruin]

Do you use the calendar control on a worksheet ?
See
http://www.rondebruin.nl/calendar.htm

 

[Dave Peterson]

Ah, maybe it's time to add some more explanation.

Tried this out but I am getting nowhere with it.

 

[Guest]

Hi,

Yes it will be used on a worksheet. I have made the calendar in the user
forms bit and i have also got it set up so it automatically feeds into the
cells when clicked on, but I don't have any code to make it start up when I
click on the specific cells.

 

[Ron de Bruin]

See Dave's example for column A