Solved

Set Excel Form RowSource Dynamically

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
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 …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now