Simple Find Macro/Input box...

[Immatoity]

Hi all

I have searched and tried to use an article in the knowledge base but I get a "Compile Error - Automation result not supported in visual basic"...

article I used was http://vbaexpress.com/kb/getarticle.php?kb_id=344

anyway..have been asked if there is a simple way to produce an input box , that is permanently on the sheet for users to enter text/numerical strings to "find" data, rather than having to press Ctrl+F repeatedly....(the users are not excel proficient in any remote sense)

I wasn't sure...so any help would be appreciated..

PS Users will be using Excel 2000 or 97 (dependent on location)

[Killian]

If you want the input available all the time, then you'd need to use either a worksheet control or a Custom toolbar with an edit control for the input.
An InputBox needs to submit a response and close before you can use the worksheet and likewise, userforms have to be modal in 97 (modal meaning the have the app focus while they are shown). I would say a toolbar would be the way to go

[Amanda1]

Hi

Brilliant - I didn't know this option existed & I had no idea how to do what you suggested, so I've just been playing around since I read this thread and found out. You have both just solved what was about to become another headache for me.

Thanks - enjoy the rest of your day.

Take care

[Immatoity]

hi...cheers for that...but I have no idea how to build a custom toolbar... I can get a blank one sitting at the top of my s/sheet no probs...but don't know where to go next?

[Killian]

The trick here is to build the toolbar with code when you open the workbook and delete it when you close it.
If you want it available all the time, save the file with the code as an AddIn and put it in your XLSTART folder (there's some more info on AddIns in this thread)

[VBA]Sub BuildToolBar()
'call from WorkBook_Open

Dim TBar As CommandBar
Dim btnNew As CommandBarControl

'delete any existing instances of the toolbar
DeleteToolbar

' Add a new toolbar
Set TBar = CommandBars.Add(Name:="Worksheet Find")
TBar.Visible = True

' Add text control
Set btnNew = TBar.Controls.Add(Type:=msoControlEdit)
With btnNew
.Caption = "Find..."
.TooltipText = "Enter search string..."
.Style = msoComboLabel
.OnAction = "FindText"
End With

Set TBar = Nothing
Set btnNew = Nothing

End Sub

Sub DeleteToolbar()
'call from Workbook_BeforeClose

Dim cb As CommandBar

For Each cb In CommandBars
If cb.Name = "Worksheet Find" Then
cb.Delete
End If
Next cb

End Sub[/VBA] The OnAction property defines the name of the routine to run ("FindText"). In this you'll have to get hold of the edit control again and use find on the text property[VBA]Sub FindText()

Dim ctrl
Dim c As Range

Set ctrl = Application.CommandBars("Worksheet Find").FindControl(msoControlEdit)
Set c = ActiveSheet.Cells.Find(What:=ctrl.Text, LookIn:=xlValues)
If Not c Is Nothing Then c.Activate

End Sub[/VBA]