A macro to bring up the find menu 1

[Epsilon101]

Hi,

Does anyone know how you could get a button with a macro attached, where the macro brings up the built in Find menu in Edit-> find or Ctrl + F, and maybe even have only the find tab presented and not the replace tab??

I tried recording a macro but it didnt give a way to open up the find menu just code for finding something using Cells.Find.

This is just because some of the people using it wont know where to look, and because im giving little access it would be better to have a button that brings up the find menu.

---------------------------------------

Neil

 

[cLFlaVA]

Why not create a form with just a text box and a button, this way there's no way a user can replace the values anyway...

Just a thought...

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[Epsilon101]

I was thinking i would need to do that for getting rid of the replace part of it, for now just wanted to know if there is a macro that i can attach to a button that brings up the Ctrl + F menu.

---------------------------------------

Neil

 

[cLFlaVA]

Here ya go:

Private Sub CommandButton1_Click()
    Range("A1").Select
    Application.SendKeys "^f"
End Sub

Where CommandButton1 is the name of the button. I had to use the .Select method of the cell A1, because when the button has focus (clicked) the Find menu won't come up.

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[Epsilon101]

Ah thanks for that one,
I have also done a user form now, almost finished it just havent a little trouble with something.

The close X button at top right of the user form, i want to remove, i have added a close button.

The close button does userform.hide, i also want to make the textbox.value = "" so that it clears on close.

Any ideas??

---------------------------------------

Neil

 

[Epsilon101]

Doh just noticed something else too, if the text you want to find isnt on the sheet, excel has an error message, my form doesnt. I tried recording the process but the code isnt any different when the error occurs.. nutz.

---------------------------------------

Neil

 

[cLFlaVA]

In your close button code, just add the code:
TextBoxName.Value = ""

AFAIK, there's no way to remove the close button. Think about the problems that would cause, if you created a modal form with no close button and no command button to close it.

Best way is just to leave your command button there. Add this to the code as well:

[tt]Private Sub UserForm_Terminate()
TextBoxName.Value = ""
End Sub[/tt]

So the textbox will clear when the user hits the "X".

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[Epsilon101]

Thanks for that cLFlaVA

for the other bit this is what i have at the moment

Private Sub CmdFindNext_Click()
Cells.Find(What:=(TxtFind.Value), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

---------------------------------------

Neil

 

[cLFlaVA]

Try this:

Private Sub CmdFindNext_Click()
    Dim theCell As Range
    Set theCell = Cells.Find(What:=(TxtFind.Value), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    If theCell Is Nothing Then
        MsgBox "Text not found"
    Else
        theCell.Select
    End If
End Sub

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[Epsilon101]

Thanks a lot, great stuff

---------------------------------------

Neil