Input box with selected text from cell

[Sumeluar]

Hello excel experts, I need your help with the following:
I have a range named "Database" with about 5000 entries and growing, on the same worksheet I have a small calendar that populates tasks according to the current month and date from the database, this calendar places items for each date with multiple lines of text. With a date containing multiple lines of text, I would like to have an input box that asks me to select the text string needed (not the cell but a portion of the text within the cell), then populate that box and search that text string on the database starting on column $D$63 and down, no other columns shall be searched.

As always, I appreciate your assistance.

 

[AlphaFrog]

I would like to have an input box that asks me to select the text string needed (not the cell but a portion of the text within the cell),

The problem with this part of your request is that a macro cannot go into cell edit mode, and pause while the user selects part of the text in the cell.

 

[Sumeluar]

The problem with this part of your request is that a macro cannot go into cell edit mode, and pause while the user selects part of the text in the cell.

Thanks for the reply.

 

[Sumeluar]

Greetings!
Upon hours and hours of search last night I found several macros that I've combined and came up with the below, which selects the cell and finds the cell contents on the Database with no issue however it does not allow me to select only a portion of the text from the selected cell.

Sub SearchFromCellSelection()
'/ Testing in progress
Dim rng As Range
Set rng = Range("$D$63:$D$50000")
On Error Resume Next
Set x = Application.InputBox(Title:="Pick a cell from the ''Description'' column", Prompt:="Select Cell to search", Type:=8)
Set found = rng.Find(what:=x)
If found Is Nothing Then
MsgBox x & " was not found in this worksheet"
Exit Sub
Else
Application.Goto reference:=found, Scroll:=True
End If
'/
If found <= "" Then
MsgBox x & "You did not specify what to look for!" & Chr(13) & Chr(13) & "Try typing something!", vbCritical, "Clown"
Exit Sub
Else
Application.Goto reference:=found, Scroll:=True
found.EntireRow.Select
End If
'/
Response = MsgBox("Do you want to continue searching?", vbYesNo)
If Response = vbYes Then
Do
Set tempcell = rng.FindNext(After:=found)
If found.Row >= tempcell.Row And found.Column >= tempcell.Column Then
MsgBox " '' " & x & " '' " & " Not found again"
Exit Do
End If
Set found = tempcell
Application.Goto reference:=found, Scroll:=True
found.EntireRow.Select
Response = MsgBox("Would you like to continue searching?", vbYesNo)
If Response = vbNo Then Exit Do
Loop
End If
End Sub

I hope someone can assist me on achieving my goal.

Regards, Sumeluar

 

[FormR]

Hi, an added step, but you could possibly populate a textbox on a userform with the entirety of the cells text, select the specific portion of the text you want to search for from within the textbox and initiate the search from a button on the userform making use of the textbox's SelText property.

 

[Akuini]

@Sumeluar
Try this:
To get a portion of a cell value you need to copy it:
Select the text > Ctrl+C (copy the text) > press Enter > run this code :

Sub Sumeluar_1()
'You need to add a reference to “Microsoft Forms 2.0 Object Library” (to work with clipboard)
Dim obj As New DataObject
Dim c As Range
Dim tx As String
Dim Response

obj.GetFromClipboard
tx = obj.GetText
Dim sAddress As String
With Range("$D$63:$D$50000")
    Set c = .Find(What:=tx, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        sAddress = c.Address
        Do
           Application.Goto reference:=c, Scroll:=True
           c.EntireRow.Select
           Response = MsgBox("Keyword: " & tx & vbLf & "Do you want to continue searching?", vbYesNo)
           If Response = vbNo Then Exit Do
           Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> sAddress
    Else
        MsgBox "Can't find " & "'" & tx & "'"
    End If
End With

End Sub

Note: You need to add a reference to “Microsoft Forms 2.0 Object Library” (to work with clipboard)

 

[Sumeluar]

Hi, an added step, but you could possibly populate a textbox on a userform with the entirety of the cells text, select the specific portion of the text you want to search for from within the textbox and initiate the search from a button on the userform making use of the textbox's SelText property.

Thanks for the reply.

 

[Sumeluar]

@Sumeluar
Try this:
To get a portion of a cell value you need to copy it:
Select the text > Ctrl+C (copy the text) > press Enter > run this code :

Sub Sumeluar_1()
'You need to add a reference to “Microsoft Forms 2.0 Object Library” (to work with clipboard)
Dim obj As New DataObject
Dim c As Range
Dim tx As String
Dim Response

obj.GetFromClipboard
tx = obj.GetText
Dim sAddress As String
With Range("$D$63:$D$50000")
    Set c = .Find(What:=tx, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        sAddress = c.Address
        Do
           Application.Goto reference:=c, Scroll:=True
           c.EntireRow.Select
           Response = MsgBox("Keyword: " & tx & vbLf & "Do you want to continue searching?", vbYesNo)
           If Response = vbNo Then Exit Do
           Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> sAddress
    Else
        MsgBox "Can't find " & "'" & tx & "'"
    End If
End With

End Sub

Note: You need to add a reference to “Microsoft Forms 2.0 Object Library” (to work with clipboard)

Akuini - Thank you for the reply, that is what I had envisioned. My problem is solved.

Regards!
Sumeluar

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.