none
Excel UserForm Issue RRS feed

  • Question

  • I am getting a VBA Error code 380 when I run my Userform.  I wan to different data source for my ListBox depending on which initial file is open.  WorkBookOpen function checks which file is open and depending on which file it is, GetFile1 or GetFile 2 opens the correct corresponding file where my golfer's names are.  In each of the two files where the golf's names are, the row of names is listed in the Names Manager.  The code works perfect for the case, FileNameIndicate = 0.  All the golfer's names are listed in the ListBox.  When FileNameIndicator  =1, the userform come up BUT NO sub names are listed.  I have tried several different things but still get RowSource Error Code 380.

    I would appreciate any help.  See my code below.

    Thanks

    John J. Vrana

    Option Explicit
    Public FileNameIndicator As Integer
    Public wb1 As Workbook
    Public wb2 As Workbook


    Sub ShowDialog()
    ' Macro revised 06/22/2012
    '
    '
    FileNameIndicator = 0
    Set wb1 = ActiveWorkbook
    ' Add some items to the ListBox
       
        If WorkbookOpen("Sharf Master Template.xlsm") = True Then
          FileNameIndicator = 1
        End If
        If WorkbookOpen("Katke Master Template.xlsm") = True Then
          FileNameIndicator = 1
        End If
        If FileNameIndicator = 0 Then
          GetFile1
        Else
          GetFile2
          On Error Resume Next
        End If
        wb1.Activate
        If FileNameIndicator = 0 Then
          UserForm1.ListBox1.RowSource = "Statistics!GolfersNames"
          UserForm1.Show
          Else
          UserForm1.ListBox1.RowSource = "2016 Handicaps Master!SubNames"
          UserForm1.Show
        End If
       
    End Sub
    Sunday, July 10, 2016 8:05 PM

Answers

  • John,

    When you deal with names, refer to the objects, which is much easier.

    And it is possible to modify the code later and let the user choose a file/sheet/name instead of a hard coded strings.

    Andreas.

    Option Explicit
    
    Sub ShowDialog()
      Dim Wb As Workbook
      Dim Ws As Worksheet
      Dim N As Name
     
      'Test which file is opened
      On Error Resume Next
      Set Wb = Workbooks("Sharf Master Template.xlsm")
      If Wb Is Nothing Then Set Wb = Workbooks("Katke Master Template.xlsm")
      On Error GoTo 0
     
      If Wb Is Nothing Then
        'In our file
        Set Wb = ThisWorkbook
        'In this sheet
        Set Ws = Wb.Worksheets("Statistics")
        'Is this locale name
        Set N = Ws.Names("GolfersNames")
        'Note:
        '  If the name is globale use this instead:
        'Set N = Wb.Names("GolfersNames")
      Else
        'In the other file is this sheet
        Set Ws = Wb.Worksheets("2016 Handicaps Master")
        'Is this locale name
        Set N = Ws.Names("SubNames")
      End If
     
     
      'Assign and open the form
      With UserForm1
        .ListBox1.RowSource = N.RefersTo
        .Show
      End With
    End Sub
    


    Monday, July 11, 2016 10:01 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, July 11, 2016 8:12 AM
  • John,

    When you deal with names, refer to the objects, which is much easier.

    And it is possible to modify the code later and let the user choose a file/sheet/name instead of a hard coded strings.

    Andreas.

    Option Explicit
    
    Sub ShowDialog()
      Dim Wb As Workbook
      Dim Ws As Worksheet
      Dim N As Name
     
      'Test which file is opened
      On Error Resume Next
      Set Wb = Workbooks("Sharf Master Template.xlsm")
      If Wb Is Nothing Then Set Wb = Workbooks("Katke Master Template.xlsm")
      On Error GoTo 0
     
      If Wb Is Nothing Then
        'In our file
        Set Wb = ThisWorkbook
        'In this sheet
        Set Ws = Wb.Worksheets("Statistics")
        'Is this locale name
        Set N = Ws.Names("GolfersNames")
        'Note:
        '  If the name is globale use this instead:
        'Set N = Wb.Names("GolfersNames")
      Else
        'In the other file is this sheet
        Set Ws = Wb.Worksheets("2016 Handicaps Master")
        'Is this locale name
        Set N = Ws.Names("SubNames")
      End If
     
     
      'Assign and open the form
      With UserForm1
        .ListBox1.RowSource = N.RefersTo
        .Show
      End With
    End Sub
    


    Monday, July 11, 2016 10:01 AM