CODEWITHSUNDEEP
vbaexcelribbonx
JeeP
JeeP

Reputation: 21

Get editbox value on "Return in Excel ribbon with VBA

My first question here. I'm trying to add a custom search field in Excel ribbon. My problem with the usual research : its default range is "this worksheet" whereas I'd want the whole Workbook (or even other known workbooks). So I created an editbox in the ribbon. I use "onChange" to validate my input and trigger my custom research sub. But I'd prefer it to be triggered only when I press "Enter" key on my keyboard, or give focus to another ribbon button (a button "search" wich would trigger the research sub with my editbox value, and wich would be activated when pressing Return while focus is still on editbox.

My other problem is that leaving the field also triggers the sub (onChange is activated when leaving) ; it doesn't trigger the event if editbox has not been changed ; and I can't catch the "Enter pressed" action.

Are there ways to solve what I'm trying to do ? If not, is there a way to call the native search function with "workbook" range as default range, instead of "this worksheet"?

Thank you for help.

JP

Upvotes: 1

Views: 1755

Answers (1)

JeeP
JeeP

Reputation: 21

What I did in the end (I just need to scan the first column):

  • an editbox in the ribbon wich updates a variable whose range is my module.

  • a "Go" button in the ribbon, wich launches the research of the string stored in the variable.

The code I used (for sure that's simple, but may help other beginners such as me):

Private nomPatientRecherche As String


Public Sub RecherchePatient(control As IRibbonControl)

Dim feuille As Worksheet, zone As Range, cellule As Range

For Each feuille In ThisWorkbook.Worksheets
    feuille.Activate
    Set zone = feuille.Range("A2:A" & ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
    For Each cellule In zone
        If Not cellule.Find(nomPatientRecherche) Is Nothing Then
            cellule.Activate
            If Not MsgBox("Continuer ?", vbOKCancel, "Continuer ?") = vbOK Then
                Exit Sub
            End If

        End If

    Next cellule
Next feuille

End Sub


Public Sub DefineNomPatientRecherche(control As IRibbonControl, nom As String)
    nomPatientRecherche = nom
End Sub

Thanks again @Rory for your help

Upvotes: 1

Related Questions