在“使用VBA返回Excel功能区”时获取editbox值

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.

我的第一个问题。我想在Excel功能区中添加一个自定义搜索字段。我对常规研究的问题是:它的默认范围是“这个工作表”，而我想要整个工作簿(甚至其他已知的工作表)。所以我在丝带中创建了一个编辑框。我使用“onChange”来验证输入和触发我的自定义研究子。但我希望触发只有当我按“输入”键键盘,或者给关注另一个丝带按钮(“搜索”按钮,将引发研究子和我editbox价值,和隔层将被激活时按返回仍关注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.

我的另一个问题是离开字段也会触发sub(离开时onChange被激活);如果editbox未被更改，则不会触发事件;我也无法捕捉到“回车按下”的动作。

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.

谢谢你的帮助。

摩根大通

1 个解决方案

#1

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.

ribbon wich中的editbox更新了一个变量，该变量的范围是我的模块。
a "Go" button in the ribbon, wich launches the research of the string stored in the variable.

在色带中有一个“Go”按钮，wich启动了对存储在变量中的字符串的研究。

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

再次感谢@Rory的帮助。

#1