Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Define Range Dynamically

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Randy - 17 Sep 2007 01:22 GMT
I know this one should be easy, but I can't make it work.  I'm trying
to dynamically define a range and assign it to a combobox on a user
form as the rowsource.  Here is my code:

dim rngGrpList as Range
Set rngGrpList = Sheet1.Range("Groups",
Sheet1.Range("Groups").Offset.End(xlDown))
frmManageGroups.cboGroups.RowSource = rngGrpList

I'm getting a type mismatch error.  It seems that my second line is
capturing the contents of the cells rather than their addresses.  I've
played around with different ideas, but nothing seems to work.  Can
anybody see why this won't work as intended?

Thanks,
Randy
Tim Zych - 17 Sep 2007 02:25 GMT
Does this help?

    Sheet1.Range(Sheet1.Range("Groups")(1, 1), _
       Sheet1.Range("Groups")(1, 1).End(xlDown)).Name = "Groups"
   frmManageGroups.cboGroups.RowSource = "Groups"

>I know this one should be easy, but I can't make it work.  I'm trying
> to dynamically define a range and assign it to a combobox on a user
[quoted text clipped - 12 lines]
> Thanks,
> Randy
Randy - 17 Sep 2007 02:34 GMT
Sorry, it doesn't work.  It doesn't like the comman in the middle of
the statement.  I played with it, but couldn't make it work.

Any ideas?
Thanks.
Randy
Tim Zych - 17 Sep 2007 02:41 GMT
Did you copy/paste it in as posted? The line continuation character must
follow the comma. Otherwise, I'm out of steam for suggestions. It works for
me.

> Sorry, it doesn't work.  It doesn't like the comman in the middle of
> the statement.  I played with it, but couldn't make it work.
>
> Any ideas?
> Thanks.
> Randy
Randy - 17 Sep 2007 02:54 GMT
My bad.  i didn't have the range name in quotes.  Thanks.  It works
perfectly.

Randy
Gary Keramidas - 17 Sep 2007 02:47 GMT
didn't test anything, but try .address after the line that errors out.

Signature

Gary

>I know this one should be easy, but I can't make it work.  I'm trying
> to dynamically define a range and assign it to a combobox on a user
[quoted text clipped - 12 lines]
> Thanks,
> Randy
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.