Returning Date from Calendar

[Jon von der Heyden]

Hi,

I am trying to return a date using Calendar Control.

I have Calendar1 within frmCalendar.

Code for Calendar1

Private Sub Calendar1_Click()
    Dim iDate
            iDate = Calendar1.Value
    Unload Me
End Sub

I then have a command button located within worksheets to run another macro. The macro that it calls is call_data:

Sub call_data()

Dim ws As Worksheet
Dim lRow, fRow, Lines As Long
Dim iDate As Date

Application.ScreenUpdating = False

Set ws = ActiveSheet
lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

cmdDatabaseOpen_Click

Lines = Cells(Rows.Count, 1).End(xlUp).Row - 1

Cells(2, 1).Resize(Lines, 2).Copy _
    ws.Cells(lRow, 2)

ActiveWorkbook.Close

frmCalendar.Show

iDate = Calendar1.Value

fRow = lRow
lRow = fRow + Lines - 1

For i = fRow To lRow
    On Error Resume Next
        Cells(i, 5) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 8, 0)
        Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 3, 0)
        Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 10, 0)
        Cells(i, 8) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 6, 0)
        Cells(i, 9) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 13, 0)
        Cells(i, 11) = iDate
        Cells(i, 13) = Application.WorksheetFunction.SumIf(Range("I5:I" & i), Range("I" & i), Range("C5:C" & i))
        With Cells(i, 15)
            .Formula = "=SUMPRODUCT(--(M" & i & ">" & ws.Name & "_Bands),(M" & i & "-" & ws.Name & "_Bands)," & ws.Name & "_Diff)"
            .Value = .Value
        End With
        Cells(i, 14) = Cells(i, 15) - Application.WorksheetFunction.SumIf(Range("I5:I" & i - 1), Range("I" & i), Range("N5:N" & i - 1))
Next

Application.ScreenUpdating = True

End Sub

How do I link iDate in Calendar1 to iDate in call_data?

Best regards
Jon

 

[Lewiy]

I would probably send the calendar date to a cell somewhere on the worksheet so that you can get it back whenever you want, i.e:

Range(“A1”) = Calendar1.Value


iDate = Range(“A1”)

 

[Andrew Poulsom]

Dim iDate only once at the top of a General module. Then it will be available to all your procedures. Don't forget to remove any procedure-level declarations.

 

[Jon von der Heyden]

Hi Andrew

Thanks for reply.

I have placed Dim iDate as Date above general module as stand-alone line of code.

Then removed Dim iDate from call_data and from Calendar1_Click.

Private Sub Calendar1_Click()
    iDate = Calendar1.Value
    Unload Me
End Sub

But when I run call_data it returns zero. Yet it will return the correct date if passed to the active cell.

Any idea what I'm doing wrong?

Edit:

Don't forget to remove any procedure-level declarations.

How do I do this?

Thanks
Jon

 

[Andrew Poulsom]

In:

Sub call_data() 

Dim ws As Worksheet 
Dim lRow, fRow, Lines As Long 
Dim iDate As Date

Did you remember to remove:

Dim iDate As Date

 

[Jon von der Heyden]

Hi Andrew

Yes I did but it still returns zero value.

Sub call_data()

Dim ws As Worksheet
Dim lRow, fRow, Lines As Long

Application.ScreenUpdating = False

Set ws = ActiveSheet
lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

cmdDatabaseOpen_Click

Lines = Cells(Rows.Count, 1).End(xlUp).Row - 1

Cells(2, 1).Resize(Lines, 2).Copy ws.Cells(lRow, 2)

ActiveWorkbook.Close

fRow = lRow
lRow = fRow + Lines - 1

frmCalendar.Show

For i = fRow To lRow
    On Error Resume Next
        Cells(i, 5) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 8, 0)
        Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 3, 0)
        Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 10, 0)
        Cells(i, 8) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 6, 0)
        Cells(i, 9) = Application.WorksheetFunction.VLookup(Cells(i, 2), Range("PC_Map"), 13, 0)
        Cells(i, 11) = iDate
        Cells(i, 13) = Application.WorksheetFunction.SumIf(Range("I5:I" & i), Range("I" & i), Range("C5:C" & i))
        With Cells(i, 15)
            .Formula = "=SUMPRODUCT(--(M" & i & ">" & ws.Name & "_Bands),(M" & i & "-" & ws.Name & "_Bands)," & ws.Name & "_Diff)"
            .Value = .Value
        End With
        Cells(i, 14) = Cells(i, 15) - Application.WorksheetFunction.SumIf(Range("I5:I" & i - 1), Range("I" & i), Range("N5:N" & i - 1))
Next

Application.ScreenUpdating = True

End Sub

And Private Sub Calendar1_Click() remains as per my last post.

Thanks
Jon

 

[Jon von der Heyden]

If I change the Calendar1_Click to populate the active cell then it returns the date selected, but as long as I use iDate it returns zero, and as such all cells within range fRow through lRow are zero.

Private Sub Calendar1_Click() 
    ActiveCell = Calendar1.Value 
    Unload Me 
End Sub

 

[Andrew Poulsom]

This worked for me:

'General module
Public iDate As Date

Sub call_data()
    frmCalendar.Show
    MsgBox iDate
End Sub

'UserForm module
Private Sub Calendar1_Click()
    iDate = Calendar1.Value
    Unload Me
End Sub