Getting VBA Macro to work with a range of cells

roger.stern@mail.com · #1 10-19-2025, 11:26 AM

I've created a macro to launch the mini date picker when I click on cell A1.
It works.

When I change the range to ("A1:A10") it doesn't work at all. how can I launch the date picker when I click in any cell in a range?

Quote:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Set the specific cell you want to monitor
Dim MonitoredCell As Range
Set MonitoredCell = Me.Range("A1")

' Check if the newly selected cell is the monitored cell and it's a single cell
If Target.Address = MonitoredCell.Address And Target.Count = 1 Then
ActiveSheet.Shapes("Calendar").Visible = True
ActiveSheet.Shapes("Calendar").Left = ActiveCell.Left + ActiveCell.Width
ActiveSheet.Shapes("Calendar").Top = ActiveCell.Top + ActiveCell.Height

Else

' hide the calendar if a cell is selected outside the Start or Completion Date columns
ActiveSheet.Shapes("Calendar").Visible = False

End If
End Sub

macropod · #2 10-19-2025, 03:15 PM

This works for me:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With ActiveSheet
  If Intersect(Target, .Range("A1:A10")) Is Nothing Then .Shapes("Calendar").Visible = False: Exit Sub
  If Selection.Cells.Count > 1 Then .Shapes("Calendar").Visible = False: Exit Sub
  .Shapes("Calendar").Visible = True
  .Shapes("Calendar").Left = ActiveCell.Left + ActiveCell.Width
  .Shapes("Calendar").Top = ActiveCell.Top + ActiveCell.Height
End With
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel VLOOKUP - Return cells that are not blank from range of specified cells	djlw84	Excel	10	05-09-2022 11:59 AM
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug)	slaycock	Word VBA	0	02-18-2017 07:00 AM
Copying text range of cells to different cells adds an extra line	jpb103	Word VBA	2	07-23-2014 12:22 PM
Using range object to work with multiple columns	kjworduser	Word VBA	1	11-01-2013 03:03 AM
Count range cells eliminating merge cells	danbenedek	Excel	0	06-15-2010 12:40 AM