CODEWITHSUNDEEP
vbaexcel
shree s
shree s

Reputation: 35

Open multiple excel files with user form

Write a code in vba that, Calling user form of one excel file to all the other 10 excel files without having any reference in those 10 excel files.

It is displaying the output in current excel file but not in the destination files and shows the error as Userform is already shown and showing form modally is not possible

Private Sub Workbook_OnClick()
    Dim mypath As String
    Dim file As Workbook
    Dim wb As Workbook
    Dim pat As String
    Application.ScreenUpdating = False
    ChDrive "C:"
    ChDir "C:\Users\Administrator\Desktop\John"
    'john is a folder on the desktop
    mypath = Range("B1").Value
    'mypath has the same value as chDir
    file = Dir(mypath & "\" & "*.xlsx")
    Set wb = Application.Workbooks.Open(file)
    If (wb.Click) Then
        Application.Visible = False
        userform1.Show
    End If
End Sub

chDir is mentioned because the default directory shown with the dir() function was C:\Users\Administrator\Documents\ but the folder saved in desktop and that is C:\Users\Administrators\Desktop\John

Sir, It is displaying the run time error - 91 that is "Object variable or with block variable is not set" and highlighting the line "file = Dir(mypath & "\" & "*.xlsx")"

Upvotes: 1

Views: 1590

Answers (1)

R3uK
R3uK

Reputation: 14537

Private Sub Workbook_OnClick()
    Dim mypath As String
    Dim file As String
    Dim wb As Workbook
    Dim pat As String
    Application.ScreenUpdating = False
    ChDrive "C:"
    ChDir "C:\Users\Administrator\Desktop\John"
    'john is a folder on the desktop
    mypath = Range("B1").Value
    'mypath has the same value as chDir
    file = Dir(mypath & "\" & "*.xlsx")
    Do While file <> ""
        Set wb = Application.Workbooks.Open(file)
        If Not IsEmpty(wb) Then
            Application.Visible = False
            userform1.Show
        End If
        wb.Close
        file = Dir()
    Loop
End Sub

Upvotes: 0

Related Questions