Double click to open dialog box, enter date then search Column A

[Ironman]

Hi

The below code opens the general search box when I double click a defined cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 And Target.Row = 11 Then
Application.Dialogs(xlDialogFormulaFind).Show
End If
End Sub

I want to make this more specific so I just get a dialog box to enter d/mm/yyyy and then search Column A (although the above code does the job, it's not specific to Column A so I don't know if it's necessary to specify Column A with what I want to do now?).

Help appreciated, many thanks.

 

[dmt32]

Have a look at this thread here: Value find Dialog Box Macro

which may help you or you could use the Range.Find method

Set foundcell = Columns(1).Find(search, LookIn:=xlFormulas, lookat:=xlWhole)

Dave

 

[Ironman]

Many thanks Dave, that was an interesting alternative. However, I would have thought there must be a way of creating an input box with dd mm yyyy that I can complete and search?

 

[dmt32]

Hi,
Dates using Range.Find can be a little tricky but try following & see if does what you want

Sub IronMan()
    Dim foundcell   As Range
    Dim search      As Variant
    
    Do
    search = InputBox("Enter Search Date", "Search Date")
    'cancel pressed
    If StrPtr(search) = 0 Then Exit Sub
    Loop Until IsDate(search)
    
    search = DateValue(search)
    
    Set foundcell = Columns(1).Find(search, LookIn:=xlFormulas, lookat:=xlWhole)
    If Not foundcell Is Nothing Then
        'found
        MsgBox foundcell.Address
    Else
        'not found
        MsgBox search & Chr(10) & "Not Found"
    End If
End Sub

Note: Range is unqualified & assumes search sheet is the activesheet.

Dave

 

[Ironman]

Thanks again Dave - that's very good! I was wondering if there was a way to make it slicker by having a 3 box format that I can input dd mm yyyy into rather than free text and having to enter / / ?

 

[dmt32]

Standard InputBox only has one text entry field to have dd mm yyyy fields you would need to create a UserForm

However, you can just enter dd mm yyyy without / (back slash) in the InputBox solution I provided & it should still work for you.

Dave

 

[Ironman]

Ahh I hadn't realised that, that'll be fine - many thanks once again Dave.

 

[dmt32]

Ahh I hadn't realised that, that'll be fine - many thanks once again Dave.

welcome glad solution does what you want

appreciate the feedback

Dave