Date picker Codes help!!!

[silverskye787]

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 =)

 

[BrianB]

Hopefully you can use this. My Calendar looks a lot different to yours.
To get more control of the Calendar it can go into a UserForm.

'=================================================================================
'- 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]

i have tried the codes but there's an error showing me this error

.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]

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 ?