Set Excel Form RowSource Dynamically

Posted on 2011-02-18
Medium Priority
Last Modified: 2012-05-11
I have an Excel form and depending on which category the user selects, I want the rowsource properly to the combob box to change.  However, this code is run, I get a datatype mismatch.  I want to use range names rather than cell references as the ranges grow and contract according to data needed.
Private Sub optActiveMiscel_Click()
'Range name for this data is ss_ACTIVEMISC
'Data header is ACTIVE MISCELLANEOUS in column H
    Me.lblProfileName.Caption = "Profile Name"
    Me.lblURLSectorSummary.Caption = "Detail URL"
    Me.txtURLSectorSummary.Visible = True
    Me.lblURLSectorDetail.Visible = False
    Me.txtURLSectorDetail.Visible = False
    Me.lblURLRatingSummary.Visible = False
    Me.txtURLRatingSummary.Visible = False
    Me.lblURLRatingDetail.Visible = False
    Me.txtURLRatingDetail.Visible = False
    Me.cboPortCode.RowSource = ThisWorkbook.Worksheets("Settings").Range("cd_ActivePortfolios")
End Sub

Open in new window

Question by:ssmith94015
LVL 85

Accepted Solution

Rory Archibald earned 1000 total points
ID: 34927329
RowSource is a String, but I'd suggest you use List:
Me.cboPortCode.List = ThisWorkbook.Worksheets("Settings").Range("cd_ActivePortfolios").Value

Open in new window

LVL 24

Assisted Solution

StephenJR earned 1000 total points
ID: 34927330
Does this work?

Me.cboPortCode.RowSource = "cd_ActivePortfolios"

Author Closing Comment

ID: 34927431
They both worked.  SstephenJR, what is curious is I tried that code version first and it returned an error.  I must have had a typo that I did not see.  Since this workbook is opened with several others, I am going to use the ThisWorkbook approach for the bond project, but I can use the shorter version in a separate project that only uses one workbook so am splitting points as they both did what I want but for different uses.  BTW, I am just starting this project and as I normally program in ACCESS, Excel forms are a little strange to me so will be posting more questions on this project as I go along - so be on the lookout!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question