replace activex texbox with anything else

[mysticmario]

Hi, I have 365 file which cannot use activeX controls due to co-authoring issue. I have this piece of code where i used activex textbox to store value, which was then used in the subroutine to acheive search functionality. However now i can't seem to figure out how to replace searchbar object(textbox) with for example range("B2")

Private Sub searchbar_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        Call search_Click
        searchbar.Activate
        
    End If
End Sub

Sub search_Click()
Dim rngFound As Range
With ActiveSheet.Cells
    Set rngFound = .Find(searchbar.Value, After:=ActiveCell _
        , LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rngFound Is Nothing Then
        rngFound.Select
     
    Else
    MsgBox prompt:="Not found"
    End If
End With
End Sub

any idea how to replace searchbar_KeyDown object with range to maintain the saem functionality?

 

[mysticmario]

I used named range but this doesn't seem to work.
I miss my activeX controls so much, everything was easier with them...

 

[mysticmario]

For future readers.
My solution/workaround to this problem was to create a userform which work fine with co-authoring, and made all the objects there.
and then refference them in code:

Private Sub searchbarwindow_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        Call szukaj_Click
        'wyszukiwarka.searchbarwindow.Activate
        
    End If
End Sub

Private Sub searchBTN_Click()
Dim rngFound As Range
With ActiveSheet.Cells
    Set rngFound = .Find(wyszukiwarka.searchbarwindow.Value, After:=ActiveCell _
        , LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rngFound Is Nothing Then
        rngFound.Select
     
    Else
    MsgBox prompt:="Not found"
    End If
End With
End Sub

 

[mysticmario]

Hi, I'm having a bit of monologue here but maybe someone may help.
I tried to be clever with userform solution but unfortunately it's bad for my application.
Userform works great and all, however at some point I save all the data to new excel file for further procedures, and that is where my uservform idea fails.
Since when i copy sheet and data to new file usign VBa, unfortunately I do not have my UserForm in this file, therefore the code breaks.
I really need to have somethign to trigger KeyDown event.\
Is there a way to make a keyDown event on a cell?
Normally it just goes to cell below as intended in excel