asked on

how to insert the calendar comtrol for excel cell

hi,

i have an excel tab with many cells, most of them are dates, like the tracking log...
how to insert a little button next to the cell and every time you click the calendar control it opens the calendar and by selecting the right day you fill the cell with the appropriate date
I assume there is should be a calendar for each date cell, so i need three calendars for my three cells
addcalendar.xlsx

Norie

What you could do is use the sheet's BeforeDoubleClick event to display the calendar control when you double click on one of the cells.

The code could be set up to show the control in a position relative to the cell clicked.

Unfortunately since I'm on Excel 365 I can't access the calendar control but I did come up with this code for an ActiveX combobox which should be able to be adapted for a calendar control.

Private Sub ComboBox1_Change()
    ActiveCell.Value = ComboBox1.Value
    ComboBox1.Visible = False
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Range("B4:D4")) Is Nothing Then
        Cancel = True
        ComboBox1.Visible = True
        With Target.Offset(1, 1)
            ComboBox1.Top = .Top
            ComboBox1.Left = .Left
        End With
    End If
    
End Sub

Open in new window

Roman F

ASKER

Thank you, looks a little complicated...

Martin Liss

Which version of Excel are you using?

Roman F

ASKER

excel 2013, thank you

Martin Liss

Try this. It doesn't use a button, but rather selecting any cell in columns B:D will pop up a calendar.
29017471.xlsm

Roman F

ASKER

Working, great, will use that. Thank you
one more thing, how to insert a new row with updated the sequential number and today's date.
I know the database will be more useful, but they need to use an excel :(

Martin Liss

I think that should be the subject of a new question.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roman F

ASKER

thank you

Martin Liss

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016