Results 1 to 7 of 7

Thread: [RESOLVED] Error on filling ComboBox from Excel named range.

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2006
    Posts
    82

    Resolved [RESOLVED] Error on filling ComboBox from Excel named range.

    I'm trying to fill a Combobox with values from a named range in excel and can't figure out why I'm getting an error in the For loop. Here's my code:


    Code:
       Public Sub FillAlbumStatusCombo(ByVal Excel_VSA_NEW As Class_Excel_Files)
    
            ComboBox_VsaStatus.Items.Clear()
    
            Excel_VSA_NEW.OWS = Excel_VSA_NEW.OWBa.Worksheets("INTERNAL")
    
            For Each cell As Excel.Range In Excel_VSA_NEW.OWS.Range("rng_VsaStatus_main").Cells
                ComboBox_VsaStatus.Items.Add(cell.Value.ToString)
            Next
    
        End Sub
    I also tried selecting the range which also gives an error.

    Code:
    Excel_VSA_NEW.OWS.Range("rng_VsaStatus_main").Select()
    Name:  devenv_xDVnDFL4Le.jpg
Views: 280
Size:  10.8 KB
    Last edited by ZviT; Jan 20th, 2021 at 03:11 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Error on filling ComboBox from Excel named range.

    Error messages are text and should be posted as text. We shouldn't have to resize an image and try to read the blurry screenshot when, if I'm not mistaken, the Exception Assistant window even has a Copy link on it.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Error on filling ComboBox from Excel named range.

    When any exception that you're not sure about is thrown on a compound statement, the very first thing to do is to break up the statement into parts and see exactly where the exception is thrown. That would mean getting the Range on one line, then getting the Cells from that on the next, then doing the For Each loop.

    I don't really do Excel Automation but it seems strange to me that you would loop through the Cells and get Range objects. Are you sure that's correct?

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Sep 2006
    Posts
    82

    Re: Error on filling ComboBox from Excel named range.

    The error:

    System.Runtime.InteropServices.COMException
    HResult=0x800A03EC
    Message=Exception from HRESULT: 0x800A03EC
    Source=<Cannot evaluate the exception source>
    StackTrace:
    <Cannot evaluate the exception stack trace>

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2006
    Posts
    82

    Re: Error on filling ComboBox from Excel named range.

    Quote Originally Posted by jmcilhinney View Post
    When any exception that you're not sure about is thrown on a compound statement, the very first thing to do is to break up the statement into parts and see exactly where the exception is thrown. That would mean getting the Range on one line, then getting the Cells from that on the next, then doing the For Each loop.

    I don't really do Excel Automation but it seems strange to me that you would loop through the Cells and get Range objects. Are you sure that's correct?
    All other actions I do on a named range while using column and row works fine. So this is like your "break up" suggestion. I am now looping because I want to fill a combo box with all the values of the range (which I haven't done before)

    So, for example, this works fine and I'm able to SET a single value to a cell within the named range:

    Code:
                        With Excel_VSA_NEW.OWS
                            .Cells(RowUsed, .Range("rng_VsaStatus_main").Column) = AlbumStatus
                        End With
    But if I try to break up what you said, this for example, throws the same error:

    Code:
    MsgBox(Excel_VSA_NEW.OWS.Range("rng_VsaStatus_main").Cells(1, 1).ToString)
    Last edited by ZviT; Jan 20th, 2021 at 03:22 AM.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Sep 2006
    Posts
    82

    Re: Error on filling ComboBox from Excel named range.

    UPDATE:

    If I gave an absolute range address - it works. So, this works and fills the combo box fine:

    Code:
        Public Sub FillAlbumStatusCombo(ByVal Excel_VSA_NEW As Class_Excel_Files)
    
            ComboBox_VsaStatus.Items.Clear()
    
            Excel_VSA_NEW.OWS = Excel_VSA_NEW.OWBa.Worksheets("INTERNAL")
    
            For Each cell As Excel.Range In Excel_VSA_NEW.OWS.Range("F2:F11")
                ComboBox_VsaStatus.Items.Add(cell.Value.ToString)
            Next
    
        End Sub
    So I just need to figure out why it's throwing an error when I use the named range "rng_VsaStatus_main" instead of the absolute address.

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Sep 2006
    Posts
    82

    Re: Error on filling ComboBox from Excel named range.

    SOLVED:

    Sorry guys, stupid mistake.

    I was using the wrong named range. I was using rng_VsaStatus_main which is in the MAIN worksheet.
    Changed it to rng_VsaStatus_internal (from the INTERNAL worksheet) and it works fine.

    I just wish that the error message would have mentioned something like "no such range found" or something more clear.

    Thank you for trying to help.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width