RowSource in Properties Dialog continued......

[Geoff]

Toppers - Thank you for your comments and time. (For those new to the thread
see Rowsource in Properties Dialog 25 March.)

Exclamation marks aside, I have resolved the issue and in the interest of
courtesy and clarification I would make 2 constructive points:

1. Because
setting Sheet3!C2:C5 in the cboDriver RowSource Property Dialog OR
placing cboDriver.RowSource = "Sheet3!C2:C5" in the initialise event

resulted in Runtime Error 380 - Invalid Property Value, there had to be an
incorrect reference somewhere in the syntax. Enlightenment was provided by 2
posts:

The first by Dave Hawley Jul 8 2002 - in answer to a similar question he
offered and I quote:
ComboBox8.Rowsource="'" & sheet8.Name &"'!D2500"

The second included a dynamic range and was provided courtesy of Bob
Phillips Mar 23 2004 and I quote:
Me.ComboBox1.RowSource = "Sheet1!A4:A" &
Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

What was missing therefore was a correct reference to the worksheet and
translating I found these worked:

setting DBase!C2:C5 in the cbodriver RowSource Property Dialog OR

placing in the initialise event either:
cboDriver.RowSource = "'" & Sheet3.Name & "'!C2:C5"" OR

cboDriver.RowSource = "DBase!C2:C" &
Worksheets("DBase").Cells(Rows.Count, "C").End(xlUp).Row

2. Using these solutions I experimented with RowSource and was able to
determine that code placed in the initialise event DID supercede property
values set in the Dialog Box. This continues to support my earlier
contention of same.

Again, thank you for your time and comments in a group which I have always
found helpful.

Geoff

 

[Tom Ogilvy]

If you have a Sheet with a Tab name of DBase (and you know that) and it has
a code name of Sheet3, then you are correct that using sheet3 in the
rowsource would be incorrect.

Otherwise, if there is no space in your sheet name (tab name), there is no
need for single quotes


cboDriver.RowSource = "DBase!C2:C5"
in the intialize event would work fine.

hand entering

Dbase!C2:C5 manually would work fine.

The rest seems like the long way around the block (if you know the last row
you want to use)

 

[Geoff]

Tom
Thanks for the comments.
The ranges referred to in the examples are on a wsheet which the user
maintains - they were illustrative. In reality they will be amended from
time to time by the user and have therefore to be considered dynamic.

Re the tab names, I guess I'm being cautious in case the user for whatever
reason decides to change the tab name.

Lastly, I just wanted to make the point because of the ( ! ) response in the
previous post (maybe I'm just getting crusty) that just using Sheet3 was not
sufficient in that case and that code written in the initialise event DOES
overwrite property values inserted using the Property Dialog - at least for
those properties I have changed from their default so far.

Geoff

 

[Tom Ogilvy]

code written in the initialise event DOES

overwrite property values inserted using the Property Dialog

Well sure, every property has some default setting whether you set it or
not. So it would have to overide the current setting (whether default or
previously set by the user) or it would have no effect.

the
previous post (

I have no idea what happended in the previous post. If you wanted to make a
point about that, it would be best to stay in the same thread rather than
start a new thread.

 

[Spike]

Tom,

Is there anyway to have a rowsource reference an excel document that the
form is not running in?

The first line works, the second line does not.
lstPhotoList.RowSource = "'[hrmph.xls]Picture Inserter Options'!$B$2:$B$6"
lstPhotoList.RowSource = "'[pic.xls]Picture Inserter Options'!$B$2:$B$6"