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
Bookmarks