Option Explicit

Private m_blnInit    As Boolean

Private m_strTitle   As String
Private m_dtmDate    As Date

Private m_blnCancel  As Boolean

Public Sub Init()
  Dim i As Integer

  With Me
    .Caption = m_strTitle
    .cmdCancel.Cancel = True
    .cmdOK.Default = True
  End With

  With Me.cboDay
    .Style = fmStyleDropDownList
    .Clear
    For i = 1 To 31
      .AddItem CStr(i)
    Next
    .Text = CStr(VBA.Day(Date))
  End With

  With Me.cboMonth
    .Style = fmStyleDropDownList
    .Clear
    For i = 1 To 12
      .AddItem Format$("1900- " & CStr(i) & "-01", "mmmm")
    Next
    .ListIndex = VBA.Month(Date) - 1
  End With

  With Me.cboYear
    .Style = fmStyleDropDownList
    For i = 1900 To 9999
      .AddItem CStr(i)
    Next
    .Text = CStr(VBA.Year(Date))
  End With

  m_blnInit = True
End Sub

Public Property Let gTitle(ByVal strTitle As String)
  m_strTitle = strTitle
End Property

Public Property Get gDate() As Date
  gDate = m_dtmDate
End Property

Public Property Get gCancel() As Boolean
  gCancel = m_blnCancel
End Property

Private Sub UserForm_Activate()
  If Not m_blnInit Then Unload Me
End Sub

Private Sub cboDay_Change()
  Call CheckDate
End Sub

Private Sub cboMonth_Change()
  Call CheckDate
End Sub

Private Sub cboYear_Change()
  Call CheckDate
End Sub

Private Sub cmdOK_Click()
  Me.Hide
End Sub

Private Sub cmdCancel_Click()
  m_blnCancel = True
  Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
            CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    m_blnCancel = True
    Me.Hide
  End If
End Sub

Private Sub CheckDate()
  Dim strDate   As String
  Dim dtmDate   As Date
  Dim blnIsDate As Boolean

  Dim strMsg    As String

  strDate = Me.cboYear.Text & "-" & _
            CStr(Me.cboMonth.ListIndex + 1) & "-" & _
            Me.cboDay.Text

  If IsDate(strDate) Then
    m_dtmDate = CDate(strDate)
    blnIsDate = True
  End If

  If blnIsDate Then
    Me.lblMsg.Caption = Format$(m_dtmDate, "Long Date")
  Else
    Me.lblMsg.Caption = "Ungültiges Datum!"
  End If

  Me.cmdOK.Enabled = blnIsDate
End Sub

Option Explicit

Public Sub FindDateInWorksheet_1()
  Const cMsgTitle As String = _
        "Datum suchen mit der Find-Methode (1. Zelle)"

  Dim objInputForm  As Object
  Dim blnCancel     As Boolean

  Dim dtmDate       As Date
  Dim strMsg        As String

  Set objInputForm = New frmDateInputCBO

  With objInputForm
    .gTitle = cMsgTitle
    .Init
    .Show
    DoEvents
    blnCancel = .gCancel
    If Not blnCancel Then
      dtmDate = .gDate
    End If
  End With

  Unload objInputForm
  Set objInputForm = Nothing

  If Not blnCancel Then
    Dim rngToSearch As Range
    Dim rngCell     As Range

    Set rngToSearch = ActiveWorkbook.Worksheets(1).UsedRange
    With rngToSearch
      Set rngCell = .Find(What:=dtmDate, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, MatchCase:=False)

      If Not rngCell Is Nothing Then
        rngToSearch.Parent.Activate
        rngCell.Activate
      Else
        strMsg = "Das Datum '" & _
              Format$(dtmDate, "dd. mmmm yyyy") & _
              "' wurde im Tabellenblatt '" & _
              rngToSearch.Parent.Name & "' nicht gefunden!"
      End If
    End With

    If Len(strMsg) > 0 Then
      MsgBox strMsg, vbOKOnly + vbInformation, cMsgTitle
    End If
  End If
End Sub