+ Reply to Thread
Results 1 to 8 of 8

Excel 2016 User Form.show VBA error

Hybrid View

  1. #1
    Registered User
    Join Date
    09-10-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Question Excel 2016 User Form.show VBA error

    Hello Everyone,

    I'm new to this forum and hoping someone could help solving my issue.
    We have multiple macros running on a workbook and below UserForm.show (User form name= frmMultiplePrint") macro ends up with Run time error 91. Macro was created in earlier version of excel and works fine with excel 2010 but gives error in excel 2016. Thank you in advance.

    Debug points to below;
    Sub PrintPackingList()

    setfrmMultiplePrint = nothoing

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Answer = MsgBox("Do you want to print multiple units with same product types?", vbQuestion + vbYesNo, "MULTIPLE UNITS OR NOT?")
    If Answer = vbYes Then
    frmMultiplePrint.Show
    'End
    Else
    PrintSingleList (True)
    End If

    End Sub

    Screenshot 2020-09-10 174628.png

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Excel 2016 User Form.show VBA error

    What line of code is highlighted when you click Debug?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    09-10-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Re: Excel 2016 User Form.show VBA error

    Hi Norie,

    frmMultiplePrint.Show is highlighted. Thank you!

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Excel 2016 User Form.show VBA error

    The actual error must be in the userform code then.

    What happens if you open the userform in design mode and then press F5?

  5. #5
    Registered User
    Join Date
    09-10-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Re: Excel 2016 User Form.show VBA error

    Ends up with same error. below is the code. This only happens with newer excel versions like 2016. it works perfectly with 2010.

    Private Sub UserForm_Initialize()
    
     Dim objFSO    As Object
     Dim objFolder As Object
     Dim objFile   As Object
     Dim strPath   As String
     Dim FileName As String
     Dim NoDupes As New Collection
     
    customerName = Range("C2").Value
    projectName = Range("C3").Value
    
     Application.ScreenUpdating = False
     
    FileName = customerName + " - " + projectName
    
     strPath = "Z:\Projects\Records"
    'Use Microsoft Scripting runtime.
     Set objFSO = CreateObject("Scripting.FileSystemObject")
     Set objFolder = objFSO.GetFolder(strPath)
     Dim searchStarted As Boolean
     
     searchStarted = False
     
       'Check type of file in the folder and open file.
         For Each objFile In objFolder.Files
            'FileNotFound = True
            'MsgBox objFile.Name
            Post = InStr(objFile.Name, FileName)
            If Post <> 0 Then
               searchStarted = True
               testString = Right(objFile.Name, Len(objFile.Name) - InStr(objFile.Name, "Lot") - 3)
               'Workbooks(FileName).Close savechanges:=True
               NoDupes.Add Left(testString, InStr(testString, ".x") - 1)
            Else
                If searchStarted = True Then Exit For
            End If
         Next 'objFile
    
        For i = 1 To NoDupes.count - 1
            For j = i + 1 To NoDupes.count
                If NoDupes(i) > NoDupes(j) Then
                    Swap1 = NoDupes(i)
                    Swap2 = NoDupes(j)
                    NoDupes.Add Swap1, Before:=j
                    NoDupes.Add Swap2, Before:=i
                    NoDupes.Remove i + 1
                    NoDupes.Remove j + 1
                End If
            Next j
        Next i
        
    '   Add the sorted, non-duplicated items to a ListBox
        For Each Item In NoDupes
            ListBox1.AddItem Item
        Next Item
        ListBox1.ListIndex = 0
    
     ActiveWorkbook.Close savechanges:=False
     
     Application.ScreenUpdating = True
    End Sub

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Excel 2016 User Form.show VBA error

    Which line is highlighted when you click Debug when you've started the code with F5?

  7. #7
    Registered User
    Join Date
    09-10-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Re: Excel 2016 User Form.show VBA error

    It does not give me Debug option. Please see screenshot of error.
    Attachment 694859

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Excel 2016 User Form.show VBA error

    Try this.

    1 Goto Tools>Options... in the VBA editor.

    2 On the general tab select either Break on All Errors or Break in Class Module.

    3 Try running the code again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel 2016 - User form Not Adding New Row and Unique ID
    By Kpowzz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2020, 07:01 AM
  2. Excel 2016 - Screen goes white before user form loads
    By KevBotes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2018, 04:34 AM
  3. EXcel User Form Date Picker 2016
    By jchillet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2017, 06:59 AM
  4. [SOLVED] Help please: User form not updating on Windows 7 Excel 2016
    By Andrew Entee in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-08-2017, 02:42 PM
  5. Microsoft Visual Basic error message when trying to print Excel user form
    By cpieramico in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-13-2015, 04:22 PM
  6. [SOLVED] Excel vba user form- open directly to user form not worksheet
    By PANTECH in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 04-24-2013, 05:07 PM
  7. [SOLVED] Excel user form- If/Then statement outcome to show on user form
    By PANTECH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2013, 09:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1