Displaying month in a pop-up calendar based on the month entered in the other cell

srinath1611

New Member
Joined
Aug 17, 2010
Messages
27
Hi

I have developed the pop-up calendar in excel using VB and macro. Now I am able to open a pop-up calendar by double clicking in a cell of any spread sheet.
I have one requirement.
Assume I enter a date by selecting from the pop-up calendar in a cell, say for example, I enter 25/09/2010 as date in cell.
When I open the pop-up calendar in another cell, I want the pop-up calendar to open 'Sep' month.

Presently, it is opening the current month instead of the month value entered in the other cell.

Please advise.

Regards
Srinath
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

Assuming you have a UserForm with a Calendar Control, if you Hide the UserForm rather than Unloading it the Calendar will retain the date previously selected.
 

srinath1611

New Member
Joined
Aug 17, 2010
Messages
27
Dear Andrew

Thanks, it is working.

I have more query.

Can I restrict pop-calendar to work only in few selected columns in a worksheet. Presently, it is getting opened through out the worksheet.

Thanks
Regards
Srinath
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Please post the code that shows the UserForm (presumably a Worksheet_BeforeDoubleClick event procedure). With which columns do you want it to work?
 

srinath1611

New Member
Joined
Aug 17, 2010
Messages
27

ADVERTISEMENT

Dear Andrew

Please find below the code that shows the form.


Private Sub cmdClose_Click()
Hide
End Sub

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


Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub


In the yesterday's solution, I have faced one more problem. I am looking for the pop-up calendar to be sensitive to the row selection in the worksheet.
For example:
In the cell 'A5', if I enter date as 12/09/2010, then when I try to open the calendar in cell 'B5 or C5 or D5' etc., I want the calendar to display 'September' month.
Otherwise, if I try to open the calendar in cell number 'A6' or 'B6' or 'C6' etc., I want the calendar to get opened with the current date selection.

I think I made my problem clear.

Regards
Srinath
 

srinath1611

New Member
Joined
Aug 17, 2010
Messages
27

ADVERTISEMENT

Is this the one you are asking for,

Sub OpenCalendar()

frmCalendar.Show

End Sub


If not, please guide me.

Srinath
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You said you could "open a pop-up calendar by double clicking in a cell of any spread sheet". Post the code that causes that to happen.

With which columns do you want it to work?
 

srinath1611

New Member
Joined
Aug 17, 2010
Messages
27
Please find the code below


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If Not Intersect(Target, Range("e5:f43", "h5:h43")) Is Nothing Then
Application.EnableEvents = False
Application.Run ("Assessment_Calendar_New_format.xls!Module1.OpenCalendar")
End If
ErrHandler:
Application.EnableEvents = True
End Sub


I am looking for the calendar to be active in the columns E, F & H only.

Also, in column E, I am looking for the calendar to display the current date, in other columns F & H to display the month based on the month selected in column E.

Please help

Srinath
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Private Sub UserForm_Activate()
    With ActiveCell
        If .Column = 5 Then
            If IsDate(.Value) Then
                Calendar1.Value = DateValue(.Value)
            Else
                Calendar1.Value = Date
            End If
        Else
            If IsDate(.Value) Then
                Calendar1.Value = DateValue(.Value)
            Else
                With .EntireRow.Cells(1, 5)
                    If IsDate(.Value) Then
                        Calendar1.Value = DateValue(.Value)
                    Else
                        Calendar1.Value = Date
                    End If
                End With
            End If
        End If
    End With
End Sub

Not that the Activate event is used in place of the Initialize event.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top