Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RowSource in Properties Dialog continued......
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 &"'!D2:D500" 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RowSource in Properties Dialog continued......
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) -- Regards, Tom Ogilvy "Geoff" wrote in message ... 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 &"'!D2:D500" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RowSource in Properties Dialog continued......
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" wrote: 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) -- Regards, Tom Ogilvy "Geoff" wrote in message ... 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 &"'!D2:D500" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RowSource in Properties Dialog continued......
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. Lastly, I just wanted to make the point because of the ( ! ) response in 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. ---- I went back and found your previous post and you were just given some incorrect information by Topper. If you wanted to correct that, that thread would have been the best place - then someone dredging it up in Google or something would see your correction. -- Regards, Tom Ogilvy "Geoff" wrote in message ... 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" wrote: 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) -- Regards, Tom Ogilvy "Geoff" wrote in message ... 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 &"'!D2:D500" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
RowSource in Properties Dialog continued......
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" -- Jeff "Spike" Zapinski "Tom Ogilvy" wrote: 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. Lastly, I just wanted to make the point because of the ( ! ) response in 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. ---- I went back and found your previous post and you were just given some incorrect information by Topper. If you wanted to correct that, that thread would have been the best place - then someone dredging it up in Google or something would see your correction. -- Regards, Tom Ogilvy "Geoff" wrote in message ... 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" wrote: 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) -- Regards, Tom Ogilvy "Geoff" wrote in message ... 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 &"'!D2:D500" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Properties dialog box opens instead of a workbook | Excel Discussion (Misc queries) | |||
RowSource in Property Dialog | Excel Programming | |||
RowSource in Property Dialog | Excel Programming | |||
RowSource in Property Dialog | Excel Programming | |||
Cell properties dialog | Excel Programming |