+ Reply to Thread
Results 1 to 3 of 3

how to force a date field to be entered so my pop up calendar appears to pick date

Hybrid View

  1. #1
    Registered User
    Join Date
    06-11-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2007
    Posts
    55

    Question how to force a date field to be entered so my pop up calendar appears to pick date

    I have a field on my spreadsheet where I have a pop-up calendar linked to the field. Please see attached file for spreadsheet.

    If I click in that field then the pop-up calendar appears. If I click in another field then the calendar pop-up window closes.

    Here is the logic I use to make this happen:

    Private Sub Calendar1_Click()
        'Turn off screen updating (do not want to show screen moving back and forth)
        Application.ScreenUpdating = False
    
        'ActiveCell.Value = CDbl(Calendar1.Value)
        'ActiveCell.Value = CDate(Calendar1.Value,"mmm-dd-yyyy")
        ActiveCell.Formula = CDbl(Calendar1.Value)
        'ActiveCell.NumberFormat = "mmm-dd-yyyy"
        
        'Turn on screen updating
        Application.ScreenUpdating = True
    
        ActiveCell.Select
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
       Dim cc As Range
       Set cc = Range("Invoice_Date")
    
        If (Target.Row = cc.Row And Target.Column = cc.Column) Then
            'If Calendar1.Visible = False Then
                'Pop up calendar
                'Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
                'Calendar1.Top = Target.Top + Target.Height
                Calendar1.Visible = True
                ' select Today's date in the Calendar
                'Calendar1.Value = Date
            'End If
        Else
            If Calendar1.Visible Then Calendar1.Visible = False
        End If
    End Sub
    The problem I have is that when I execute my macro logic to clear my Invoice worksheet and then select and activate the date field my pop up window does not appear.

    How do I get the calendar pop-up window to appear after I clear the window.

    Here is the logic below that I use to activate the date field after I clear the Invoice worksheet.

    Private Sub Clear_Invoice()
        
        'Turn off screen updating (do not want to show screen moving back and forth)
        Application.ScreenUpdating = False
        
        'Turn off worksheet protection. (No password required)
        Call UnProtectAllSheets
        
        ' Select the Target Sheet
        TempWork_Sheet = "Invoice"
        Sheets(TempWork_Sheet).Select
        Sheets(TempWork_Sheet).Activate
        
        ' Set Detail Line pointers.
        line1 = Range("Invoice_Line1").Row()
        line5 = Range("Invoice_Line1").Row() + 4
        line6 = Range("Invoice_Line1").Row() + 5
        linelast = Range("Invoice_Footer").Row() - 1
        
        ' Delete extra detail lines if greater than 5 detail lines.
        If linelast > line5 Then
            For RowToDelete = linelast To line6 Step -1
              Range("A" & RowToDelete).EntireRow.Delete (xlShiftUp)
            Next RowToDelete
            
            linelast = line5
      End If
      
      ' Clear data from detail lines
        For i = line1 To linelast
            linecurrent = Range("G" & i).Row()
            Range("G" & linecurrent).Value = ""
            Range("H" & linecurrent).Value = 0
            c = Range("G" & i).Column()
            test = Worksheets("Invoice").Cells(linecurrent, c).Interior.ColorIndex
            Worksheets("Invoice").Cells(linecurrent, c).Interior.ColorIndex = xlNone
        Next i
        
        ' Reset Remaining fields
        Range("Invoice_Number").Value = ""
        Range("Invoice_GL_Status").Value = "process"
        Range("Invoice_Type").Value = "Invoice"
        Range("Invoice_Vendor_Number").Value = ""
        Range("Invoice_Cost_Center").Value = ""
        Call UnProtectAllSheets
        Range("Invoice_Gross_Amount").Value = 0
        Range("Invoice_ShortDescr").Value = ""
        Range("HST05_100Reb").Value = 0
        Range("HST05_67Reb").Value = 0
        Range("HST08_100Reb").Value = 0
        Range("HST08_78Reb").Value = 0
        
        Range("Invoice_ToBeModified").Value = ""
        Range("Invoice_ToBeModified_CostCenter").Value = ""
        Range("Invoice_ToBeModified_GLStatus").Value = ""
        Range("Invoice_ToBeModified_DataRow").Value = ""
        
        Range("Invoice_Date").Select
        Range("Invoice_Date").Activate
        Range("Invoice_Date").Formula = "=Today()"
    
        'Reactivate the protection. (No password required)
        Call ProtectAllSheets
        
        'Turn on screen updating
        Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by cmwilbur; 11-29-2010 at 10:20 AM.
    cmwilbur

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,932

    Re: how to force a date field to be entered so my pop up calendar appears to pick dat

    Why not just insert this line, as shown. Works for me.

        Range("Invoice_Date").Formula = "=Today()"
        Worksheets("Invoice").Calendar1.Visible = True ' add this line
     
        'Reactivate the protection. (No password required)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-11-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2007
    Posts
    55

    Re: how to force a date field to be entered so my pop up calendar appears to pick dat

    Thank-you very much. This works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1