-
Jan 20th, 2021, 02:50 AM
#1
Thread Starter
Lively Member
[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()
Last edited by ZviT; Jan 20th, 2021 at 03:11 AM.
-
Jan 20th, 2021, 03:08 AM
#2
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.
-
Jan 20th, 2021, 03:12 AM
#3
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?
-
Jan 20th, 2021, 03:12 AM
#4
Thread Starter
Lively Member
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>
-
Jan 20th, 2021, 03:16 AM
#5
Thread Starter
Lively Member
Re: Error on filling ComboBox from Excel named range.
Originally Posted by jmcilhinney
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.
-
Jan 20th, 2021, 03:33 AM
#6
Thread Starter
Lively Member
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.
-
Jan 20th, 2021, 03:39 AM
#7
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|