Month view calender in cell open on double click

[ipbr21054]

Evening,

This will refer to column M only "M6 & down the page"

I have added the month view calender to a userform but when i drag the handles to make the calender a little bigger it reverts back to its original small size,see attached photo.
Ive also tried changing the values in the propeties box but still the same.
Do you see why its not allowing me to alter its size ?


As per post title i would like the calender to pop up when a cell in column M is double clicked.
Currently using the code below which opens up on a single click.
Please can you advise.

Thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    If (Target.Count = 1) Then
    If Not Intersect(Target, Range("M6:M5000")) Is Nothing Then UserForm2.Show
    End If

End Sub

 

[smozgur]

Not sure about the sizing issue as I don't have the control to test, but I think you might want to use Worksheet_BeforeDoubleClick event procedure.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count = 1 And Not Intersect(Target, Range("M6:M5000")) Is Nothing Then
        UserForm2.Show
        ' Add the following if you don't want the cell to be in the edit mode after user form is closed.
        Cancel = True
    End If
End Sub

Edit: Added Cancel = True.

 

[ipbr21054]

Thanks for the advice.
I have that procedure in use so my new code is like so,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
    
    If Target.Count = 1 And Not Intersect(Target, Range("M6:M5000")) Is Nothing Then
    DatabaseCalendar.Show
    End If
End Sub

Double clicking a cell in column A still works fine.

Double clicking a cell in column M also works BUT
I hear a DING sound as if something is wrong ?
If i tab acroos each cell say J6 K6 L6 M6 as soon as im on the M6 cell the calender opens.
Not sure why it opens as i didnt click on it ?

 

[smozgur]

I hear a DING sound as if something is wrong ?

You have Database.LoadData Me, Target.Row call that might be causing the DING. It has nothing to do with showing the user form, and I can confirm no DING in my implementation.

If i tab acroos each cell say J6 K6 L6 M6 as soon as im on the M6 cell the calender opens.
Not sure why it opens as i didnt click on it ?

Are you sure that you cleared the Worksheet_SelectionChange event procedure?

 

[ipbr21054]

I have that procedure in use so this is me trying to write it for both BEFORE DOUBLE CLICK

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
    
    If Target.Count = 1 And Not Intersect(Target, Range("M6:M5000")) Is Nothing Then
    DatabaseCalendar.Show
    End If
End Sub

So the Double Click must work for the part to run if column A is double clicked BUT also column M

 

[smozgur]

Right. I just saw that.

If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub will never let that procedure to work when you double click on M cells. It is basically checking if the clicked cell is in the A column or not. You should also check if it is in M column to decide the continue execution.
Following should help (I don't know your project specifics, so I am not touching how you decide the control range in the columns).
Also from your code, Database.LoadData looks to be working in either condition, no matter A or M cell is clicked. If not, then you need to use Else If for M column match.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count = 1 Then
        If Intersect(Target, Range("M6:M5000")) Is Nothing And _
            Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
        Cancel = True
        Database.LoadData Me, Target.Row
        If Not Intersect(Target, Range("M6:M5000")) Is Nothing Then
            DatabaseCalendar.Show
        End If
    End If
End Sub