Solved

Set Excel Form RowSource Dynamically

Posted on 2011-02-18
3
348 Views
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

0
Comment
Question by:ssmith94015
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 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

0
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 250 total points
ID: 34927330
Does this work?

Me.cboPortCode.RowSource = "cd_ActivePortfolios"
0
 

Author Closing Comment

by:ssmith94015
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!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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