Date picker Codes help!!!

silverskye787

Board Regular
Joined
Jun 18, 2007
Messages
109
celldateqw0.jpg


Cell B2 is a combo box that let the user to choose the number of dates to be created.. example with it is 2, B3 and B4 will have a cell like the above picture created.

I need help on generating B3:B4 when the user has choose the number at cell B2 ( Number range from 1 to 9)

I also need to know how to create that "Date Picker" thing. Is there any VBA codes for that date thing? The above picture is just a sample.

thanks =)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Hopefully you can use this. My Calendar looks a lot different to yours. :biggrin:
To get more control of the Calendar it can go into a UserForm.
Code:
'=================================================================================
'- MACRO TO ENTER DATES INTO A RANGE OF CELLS USING THE CALENDAR CONTROL
'=================================================================================
'- (Open the Controls Toolbox, Click "More Controls",
'-          Select "Calendar Control" & draw on the sheet)
'- Set the Visible property to False
'- This code goes into the Worksheet module (right click tab/View Code)
'-
'- Brian Baulsom  July 2007 using Excel 2000
'=================================================================================
'=================================================================================
'- SHEET : DOUBLE CLICK SHEET CELL B2 TO RUN THE CALENDAR CONTROL
'=================================================================================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$B$2" Then
        With Calendar1
            .Top = Range("B2").Top + 10  ' calendar position
            .Left = Range("D4").Left
            .Visible = True
        End With
        '------------------------------------------------------------------------
        '- clear date cells
        Range("B3:B11").ClearContents
        With Range("B3")
            .Interior.ColorIndex = 6     ' highlight cell
            .Select
        End With
    End If
End Sub
'--------------------------------------------------------------------------------


'================================================================================
'- CALENDAR : CLICK DAY TO INSERT DATE
'- NB. This runs from the beginning when the Calendar is clicked
'================================================================================
Private Sub Calendar1_Click()
    Dim DateCount As Integer
    Dim n, rw
    Dim MyCell As Range
    DateCount = ActiveSheet.Range("B2").Value
    '---------------------------------------------------------------------------
    '- find next empy cell
    For n = 1 To DateCount
        rw = n + 2
        Set MyCell = ActiveSheet.Cells(rw, 2)
        '-
        With MyCell
            If CStr(.Value) = "" Then
                .Value = Calendar1.Value
                '---------------------------------------------
                '- change highlight. Select next cell
                .Interior.ColorIndex = xlNone
                If n < DateCount Then
                    .Offset(1, 0).Interior.ColorIndex = 6
                    .Offset(1, 0).Select
                End If
                '---------------------------------------------
                If DateCount = 1 Then Calendar1.Visible = False
                Exit Sub
            End If
        End With
        '----------------------------------------------------------------------
        '- All cells entred. Hide Calendar Control again
        If n = DateCount - 1 Then Calendar1.Visible = False
    Next
End Sub
'-----------------------------------------------------------------------------------
 

silverskye787

Board Regular
Joined
Jun 18, 2007
Messages
109
i have tried the codes but there's an error showing me this error

Code:
.Top = Range("B2").Top + 10  ' calendar position

this is the one that prompt me that there's an error...

mind to show me how's your format looks like? maybe a printscreen?
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
The control looks much the same as yours but grey instead of blue and does not show "Forward:14 days".

Its name shows as "Calendar Control 9.0" in 'More Controls' of the toolbar.
The file name is MSCAL.OCX 106,496 bytes dated 19/2/1999 21:42:06. Very commomn in use.

What is your control ? I may be able to download it. You could try the same. The MS ones are free. If you do, you will need to register it on the machine - Start/Run/ ........ type
regservr32 (space) then full path/file name.

My file is in E:\Programs\Office 2000\Ofice (see where you have other .OCX contros - may be in c:\ ...System32\)

You need to check what properties are available in your version of the control. (select it and click Properties in the Controls Toolbar)

You can comment out that line for the time being because it only positions the control - i.e. does not stop it working. If similar occurs you could try commenting lines out too. Use your judgement.

What are your Office - Windows versions ?
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

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