Restoring Find dialogue settings

Hiya all

Is there any way to retrieve and later restore the settings on the "Find and Replace" dialogue? I'm using the Find function in my code and it overwrites the settings in the dialogue.

I don't think there is a straightforward way to do this, but there must be a workaround, there always is!

Thanks in advance
Kasia

Hiya all

Is there any way to retrieve and later restore the settings on the "Find and Replace" dialogue? I'm using the Find function in my code and it overwrites the settings in the dialogue.

I don't think there is a straightforward way to do this, but there must be a workaround, there always is!

Thanks in advance
Kasia

Hi Kasia,

You're right, there isn't a straightforward way to do this!

I think your easiest option would be just to reset the dialogue box to the default settings, as shown at the bottom of this link:
http://www.thecodenet.com/articles.php?id=18

Would that be satisfactory for you?

Colin

Thanks Colin, this seems like an easy way to reinstate the settings.

But I still would like to find a way of reading them before I use the Find funciton. Wouldn't it be possible to somehow invoke the dialogue and read off the values from the controls?

Kasia

Hello Kasia,

Possible? Yes I would think so.

Invoking the dialog box is straightforward:

Dim cb As CommandBarButton

'invoke the find and replace dialog
Set cb = Application.CommandBars.FindControl(ID:=1849)
cb.Execute


But I think you would need to use API calls to read the values from the controls. Is it really worth it?

Colin

A really convoluted and terrible idea:

Get .FindNext.Value (e.g. "abc")
Create variations of that value (e.g. "ABC", "a", "b", "c", "zabc", "abcd")
Put those variations in various places
.FindNext over those places and collect the variations that are picked up
Use complex conditional logic to infer combination of settings that gives you those variations but no others.

See, told you it was terrible. :p

Having said that - if one were interested in a single specific setting, it would be quite workable. Still very much a hack, though.

That's a good bit of thinking, Cas. :cool:

A question though.... I can't think how one could use it to determine the value in the "What" paramater? It's overwritten as soon as you enter something to search for.... :(

I think that, since the settings are reset everytime the Excel instance is closed, either idea is a lot of effort compared with the straightforward 'reset default settings' possiblility.

I can't think how one could use it to determine the value in the "What" paramater? It's overwritten as soon as you enter something to search for.... :(
Right, that's why your code needs to do all this before ever using .Find, so that the .FindNext calls inplicitly refer to the last value entered by the user.
I think that, since the settings are reset everytime the Excel instance is closed, either idea is a lot of effort compared with the straightforward 'reset default settings' possiblility.
Yeah, no doubt. :rolleyes:

Right, that's why your code needs to do all this before ever using .Find, so that the .FindNext calls inplicitly refer to the last value entered by the user.


Yes I followed that point previously.... but by using FindNext, to ever know what you are actually searching for, you would have to be lucky enough to have that value in a cell to be found - and within the confines of the other parameters? And what if the wildcard * is being used? I don't see how that can be done?

Good points. If the search doesn't yield any results in the workbook, one would have to create new sheets and fill them with string permutations, and then search that. Which limits the complexity of the value to about 5 characters even under ideal conditions (26^n vs Sheet.Cells.Count).
As to wildcards, that's not quite as bad, one should be able to get at them by stepwise refinement:

"abc" -> match
"ab" -> no match
"bc" -> no match
"adc" -> match
"addc" -> match
more test to verify "a" and "c" as opposed to "?"


-> What = "a*c"

As I said, the required conditional logic is anything but straightforward.

ETA: Just realized that things are really indistinguishable at a certain point - there's no way to tell the difference between searching for "*a*" with "match entire cell contents" in effect and "a" without. Sorry, took me a while... :)

I was hoping you had an ace up your sleeve! ;)

It was a very shrewd idea though.... :)

It's all very frustrating; somewhere in Excel, the list of previously searched for values are being stored, as evidenced by the dialog's drop-down list. I doubt it's stored elsewhere, as Colin pointed out, the list is cleared after Excel is closed.

I spent too long just now Googling the topic for clues, but nothing surrendered as yet, but I'm hopeful!

This would make a great "challenge" ;)

Brute-force proof-of-concept - finds search key assuming standard settings (case-insensitive, match parts), where key can be up to 3 chars long and may contain both letters and the "?" wildcard.
In other words, it should cover 99.99% of all searches... ;)

Needs a sheet codenamed sheetFind. You can change nMaxLen to 4 or 5, but it gets painfully slow after 3.

Option Explicit

Private Const nMaxLen As Long = 3
Private Const nMaxCol As Long = 2 ^ 8
Private Const nMaxRow As Long = ((27 ^ nMaxLen) \ nMaxCol) + 1

Private strSheet(1 To nMaxRow, 1 To nMaxCol) As String

Public Sub reconstructKey()

Call fillSheet

'simulate manual search
Call sheetFind.Cells.Find("a?z")

Dim allMatches As Collection
Set allMatches = findAll

Debug.Print buildKey(allMatches, minLen(allMatches))

End Sub

Private Function buildKey(whichMatches As Collection, whichLen As Long) As String

Dim strChars() As String: ReDim strChars(1 To whichLen)

Dim nChar As Long, strChar As String
For nChar = 1 To whichLen
strChars(nChar) = "_"
Next nChar

Dim varMatch As Variant
For Each varMatch In whichMatches
If VBA.Len(varMatch) = whichLen Then
For nChar = 1 To whichLen
strChar = VBA.Mid$(varMatch, nChar, 1)
Select Case strChars(nChar)
Case "_": strChars(nChar) = strChar
Case strChar: 'no change
Case Else: strChars(nChar) = "?"
End Select
Next nChar
End If
Next

buildKey = VBA.Join(strChars, "")

End Function

Private Function minLen(whichMatches As Collection) As Long

Dim nMinLen As Long: nMinLen = nMaxLen
Dim varMatch As Variant
For Each varMatch In whichMatches
If VBA.Len(varMatch) < nMinLen Then nMinLen = VBA.Len(varMatch)
Next
minLen = nMinLen

End Function

Private Function findAll() As Collection

Dim allMatches As Collection: Set allMatches = New Collection

Dim firstMatch As Range, thisMatch As Range
With Range(sheetFind.Rows(1), sheetFind.Rows(nMaxRow))
Set firstMatch = .FindNext
Set thisMatch = firstMatch
Do
Set thisMatch = .FindNext(thisMatch)
Call allMatches.Add(thisMatch.Value)
Loop Until thisMatch.Address = firstMatch.Address
End With

Set findAll = allMatches

End Function

Private Sub fillSheet()

Dim nTotal As Long: nTotal = 0

Dim nLen As Long
For nLen = 1 To nMaxLen
nTotal = buildText("", nLen, nTotal)
Next nLen

Dim oldXLC As XlCalculation: oldXLC = Application.Calculation
Application.Calculation = xlCalculationManual
With Range(sheetFind.Rows(1), sheetFind.Rows(nMaxRow))
.Clear
.Value = strSheet
End With
Application.Calculation = oldXLC

End Sub

Private Function buildText(whichText As String, whichLen As Long, whichTotal As Long) As Long

Dim nTotal As Long: nTotal = whichTotal

Dim doPlace As Boolean
If VBA.Len(whichText) = (whichLen - 1) Then doPlace = True _
Else doPlace = False

Dim nChar As Long, strText As String
For nChar = VBA.Asc("a") To VBA.Asc("z")
strText = whichText & VBA.Chr$(nChar)
If doPlace Then nTotal = placeText(strText, nTotal) _
Else nTotal = buildText(strText, whichLen, nTotal)
Next nChar

buildText = nTotal

End Function

Private Function placeText(whichText As String, whichTotal As Long) As Long
strSheet(((whichTotal \ nMaxCol) + 1), ((whichTotal Mod nMaxCol) + 1)) = whichText
placeText = whichTotal + 1
End Function

It's all very frustrating; somewhere in Excel, the list of previously searched for values are being stored, as evidenced by the dialog's drop-down list. I doubt it's stored elsewhere, as Colin pointed out, the list is cleared after Excel is closed.

I spent too long just now Googling the topic for clues, but nothing surrendered as yet, but I'm hopeful!

This would make a great "challenge" ;)

Indeed it would! :)

My googling didn't turn up any new information - just a number of threads in various forums containing discussions very similar to this one, eg:
http://www.ozgrid.com/forum/showthread.php?t=90763

I had a good look through the object browser and I can't see any available application object properties which look like possible contenders. :(


In terms of the API approach, I used the recursive procedure by Mark here to get some handle/class/caption information from the find+replace dialog window:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=52

This is what it gave:

12518008 EDTBX £aa
10617496 EDTBX N/A
5112478 XLTFRCLASS N/A 3867098 SysListView32 N/A 9568710 SysHeader32 N/A
4719238 XLTFSTATCLASS N/A 2493646 msctls_statusbar32 0 cell(s) found

Ooh... we're going API on it now are we? :cool:

Not sure what Mark's code did, but Spy++ shows the 'Find and Replace' window classname to be "bosa_sdm_XL9", but Google shows this is probably a generic window superclass. However we can build a 'Office.CommandBarButton' event sink to launch a timer proc which captures the window the millisecond it appears.

After that, just find its combobox child window (classname "EDTBX") and let 'SendMessage' work its magic:
http://vbnet.mvps.org/index.html?code/listapi/listcopy.htm

Should be quite straight-forward, but a bit of a hack and I can't help thinking we've missed a trick. Could the values in the dialog drop-down be hidden somewhere in the registry?...

Race you to code the above ;)

I am very much an API n00b... so you'll win hands down!!

The information I retrieved using Mark's procedure was returned by feeding in the handle of the find+replace dialog window which I determined using the FindWindow API function, as in:


Public Sub GetWindows()
Dim cb As CommandBarButton
Dim hWnd As Long

x = 0
winOutputType.winHandle = 0
winOutputType.winClass = 1
winOutputType.winTitle = 2
winOutputType.winHandleClass = 3
winOutputType.winHandleTitle = 4
winOutputType.winHandleClassTitle = 5


'invoke the find and replace dialog
Set cb = Application.CommandBars.FindControl(ID:=1849)
cb.Execute
Set cb = Nothing

hWnd = FindWindow(vbNullString, "Find and Replace")

GetWinInfo hWnd, 0, winOutputType.winHandleClassTitle
End Sub

My attention was drawn to the SysListView Classname? So I don't think that this is quite as straightforward as one might hope?

Could the values in the dialog drop-down be hidden somewhere in the registry?
I think that's unlikely, the point of putting things into the registry is to make them persistent, but as you've said these values aren't restored when the app is re-launched.

Ok, you got me - I just couldn't articulate where Excel might actually be storing the list ;)

However, I've attached a fun toy for everyone to play with :)

I should say I refer to it as a "toy" because it doesn't capture the previously serached-for text if the Find/Replace dialog was invoked using the "CTRL+F" hotkey. Otherwise, it's fine!

Realised I'd left some redundant stuff in there! Dealt with now :)

Very nice work Timbo! :cool:

A simple thought.... to handle the CTRL+F, how about including this?

ThisWorkbook Class Module

Private Sub Workbook_Open()
Application.OnKey "^f", "LaunchFindReplace"
Application.OnKey "^F", "LaunchFindReplace"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^f"
Application.OnKey "^F"
End Sub

basFindReplaceHistory Module

Sub LaunchFindReplace()
Application.CommandBars.FindControl(ID:=1849).Execute
End Sub

Thanks! I suppose the thing to do really is to throw it all into an Addin, then it can all be intialized at startup, including the hotkey overrides you've shown there :)

Not sure what Mark's code did, but Spy++ shows the 'Find and Replace' window classname to be "bosa_sdm_XL9", but Google shows this is probably a generic window superclass. However we can build a 'Office.CommandBarButton' event sink to launch a timer proc which captures the window the millisecond it appears.

After that, just find its combobox child window (classname "EDTBX") and let 'SendMessage' work its magic:
http://vbnet.mvps.org/index.html?code/listapi/listcopy.htm


Hi Timbo,

Being a bit of an API n00b, the part of this challenge that stumped me was the sequence of observations and logic you used here. Using the code I posted, I was unable to see the combobox child window: can you explain what I was seeing (what is actually happening) and how you deducted this workaround in order to retrieve the information that we actually wanted?

Many thanks,
Colin

Until I have time to run Mark's code myself, I can't be sure what was being picked up, but I can tell you that the "bosa_sdm_XL9" window class is a generic Windows dialog, and there were several open while I was trying to identify the 'Find and Replace' dialog. In the end I discovered I was looking in the wrong place as it was a child of the Windows desktop (the parent of all windows) and not "XLMAIN", suggesting it runs in another thread altogether.

If you can get your hands on SPY++ or similar this will all probably make more sense :)
http://www.dailysofts.com/program/709/15840/WinID.html