Word UserForm search in Excel

[jhancock1994]

Essentially the macro should take the user input, search all sheets in the relevant excel file and if there's a match copy a value to a bookmark.



However the macro runs without copying over the data. If the UserForm2.Textbox1.Text line is replaced with the input, e.g. "January" - i.e. skipping the UserForm entry step - it works fine.

Sub Macro()

Dim xlApp As New Excel.Application
Dim xlWb As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim Result As String

Set xlWb = xlApp.Workbooks.Open("File Name.xlsx")

xlWb.Application.Visible = False
xlWb.Application.WindowState = xlMinimized

For Each xlSheet In xlWb.Sheets
    If xlSheet.Range("B3").Value = UserForm2.TextBox1.Text Then
        Result = xlSheet.Range("B9").Value
        FillBM "bm3", Result
    Exit For
     End If
     
Next xlSheet
   
    xlWb.Close
    xlApp.Quit
  
End Sub

Any ideas how to fix this?

Cheers