How to choose a specific cell when a form closes

[Diving_Dan]

Hi all,

I'm quite new here and new to vba. I have a worksheet that opens a pop up calendar when I click on a certain cell. When I then click on a date on that pop up it puts the date into that particular cell and then the calendar closes. This is all good and working.

Is there a way that when the calendar closes a certain cell is activated/chosen. My calendar is linked to cell H2. When I click on a date on the pop up H2 is populated with that date and the calendar closes. What I would like is for A2 to be chosen rather than H2 remaining the chosen cell.

Is this possible?

Thanks in advance.

This is the current coding I have for the form

Private Sub cmdClose_Click()    Unload Me
End Sub


Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    On Error Resume Next
    ActiveCell.Value = DateClicked
    Unload Me
End Sub


Private Sub UserForm_Initialize()
    If IsDate(ActiveCell.Value) Then
        Me.MonthView1.Value = ActiveCell.Value
    End If
End Sub

This is the code I have within the module for my calendar to pop up.

Sub OpenCalendar()    frmCalendar.Show
End Sub

And this is the code I have within the sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (Target.Count = 1) Then
    If Not Intersect(Target, Range("H2")) Is Nothing Then frmCalendar.Show
    End If
End Sub

 

[Fluff]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (Target.Count = 1) Then
        If Not Intersect(Target, Range("H2")) Is Nothing Then
            frmCalendar.Show
            Range("A2").Select
        End If
    End If
End Sub

 

[Diving_Dan]

Thanks Fluff, worked a treat.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Diving_Dan]

I now seem to be getting this error if i try to select all cells

Run-time error '6':
Overflow

If i click on debug there seems to be an error with the second line of code

Any suggestions?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   [COLOR=#ff0000] If (Target.Count = 1) Then[/COLOR]
        If Not Intersect(Target, Range("H2")) Is Nothing Then
            frmCalendar.Show
            Range("A2").Select
        End If
    End If
End Sub

 

[Diving_Dan]

Strange, i closed it and went back into it and I now don't have the option to debug, but I still get the error message???

 

[Joe4]

Try changing that line to:

   [COLOR=#ff0000] If (Target.CountLarge = 1) Then[/COLOR]

See the "Remarks" section here for the explanation of why this is an error when you select all cells: https://docs.microsoft.com/en-us/office/vba/api/excel.range.countlarge

 

[Diving_Dan]

Thanks Joe4, problem solved

 

[Joe4]

You are welcome.