Word UserForm search in Excel

jhancock1994 · #1 05-21-2017, 06:42 PM

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.

Code:

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel Userform to Word Template Bookmarks	JCrinage	Excel Programming	1	11-02-2016 07:03 PM
Populate Combobox from Excel into a Word UserForm	ferguson4848	Word VBA	3	10-28-2016 09:05 PM
UserForm Search, delete issues	johndough	Excel Programming	3	05-11-2014 11:44 AM
UserForm Search with multiple TextBoxes	johndough	Excel Programming	0	05-10-2014 12:11 PM
send a string from Excel UserForm to Word	saltlakebuffalo	Excel Programming	1	02-10-2014 11:01 PM