View Single Post
 
Old 05-21-2017, 09:14 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,142
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Try the following instead, which does work provided you have the bookmark in the document, the FillBM function available and the value in TextBox1 does indeed match the value in Cell B3 and there is a value in B9
Code:
Option Explicit


Sub Macro()
Const strWB As String = "C:\Path\Workbookname.xlsx"
Dim xlApp As Object
Dim xlWb As Object
Dim xlSheet As Object
Dim Result As String
Dim oFrm As UserForm2

    Set oFrm = New UserForm2
    With oFrm
    .Show
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    
    Set xlWb = xlApp.Workbooks.Open(strWB)

    xlWb.Application.Visible = False
    
    For Each xlSheet In xlWb.Sheets
        If xlSheet.Range("B3").Value = .TextBox1.Text Then
            Result = xlSheet.Range("B9").Value
            FillBM "bm3", Result
            Exit For
        End If
    Next xlSheet
    xlWb.Close
    xlApp.Quit
    End With
    Unload oFrm
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote