Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to fake/simulate more than 256 columns
Greetings,
I'm using an Excel spreadsheet to define and maintain a data-set used by another application. So, the spreadsheet is a "UI" where users' control over sheet is limited. The data represents a "daily schedule" where the user contols how many days are defined. The right-most column represents the last day defined by the schedule. It's reasonable to expect a schedule will (need to) define more than 256 days (columns). I'm considering maintaining a data-set on another sheet, or in a disk-file, and just populate the UI according to what user wants to see. For instance, maybe up to 60 columns are used, and when the user wants to see a 61st day, the data is programmatically "scrolled" to the left such that column 60 holds day 61, and column 1 holds day 2. Of course it would be nice to employ the standard worksheet scroll-bars, but if I have to manage one, so be it. If anybody [is not completely confused by now, and] has tried anything like this, or if you have any enlightening ideas, I'd be interested in hearing from you. Thanks/Cheers! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to fake/simulate more than 256 columns
Can you transpose your rows and columns? If you're not showing it to the user,
your code could just do things "backwards" <vbg. Can you upgrade to xl2007 (with 1 million rows and 16k columns)? If no to both, then maybe you could reserve two columns to act as a key. The first column is the key for that record. The second column is just a counter of which row you're on for that record. You'll have to map your fields (column E of Row 2 for employee 12345 record). But that's pretty much as multiple sheets. You may to consider using a userform to show the info/get input from the user. You could look at John Walkenbach's enhanced data form: http://j-walk.com/ss/dataform/index.htm The source code is available for a small fee ($20 USA, IIRC). So you can modify it as much as you want. tbd wrote: Greetings, I'm using an Excel spreadsheet to define and maintain a data-set used by another application. So, the spreadsheet is a "UI" where users' control over sheet is limited. The data represents a "daily schedule" where the user contols how many days are defined. The right-most column represents the last day defined by the schedule. It's reasonable to expect a schedule will (need to) define more than 256 days (columns). I'm considering maintaining a data-set on another sheet, or in a disk-file, and just populate the UI according to what user wants to see. For instance, maybe up to 60 columns are used, and when the user wants to see a 61st day, the data is programmatically "scrolled" to the left such that column 60 holds day 61, and column 1 holds day 2. Of course it would be nice to employ the standard worksheet scroll-bars, but if I have to manage one, so be it. If anybody [is not completely confused by now, and] has tried anything like this, or if you have any enlightening ideas, I'd be interested in hearing from you. Thanks/Cheers! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to fake/simulate more than 256 columns
Thanks Dave!
I didn't know about Excel 2007 supporting 16k columns. I like this solution the best, on the other hand, I'm outta work and this gig isn't paying anything. Still, will check with "customer" to see what they're using. I've never used Transpose, but it looks very promising. I still need to talk myself into implementing the scrolling controls and data synchronization logic - but this is definately doable. I'm not sure, but I think the "keyed" approach, like transpose, also utilizes data storage on another sheet, with the keys mapping that data to cells in the UI. I hope transpose works so I can copy en mass! Excellent options, thanks! "Dave Peterson" wrote: Can you transpose your rows and columns? If you're not showing it to the user, your code could just do things "backwards" <vbg. Can you upgrade to xl2007 (with 1 million rows and 16k columns)? If no to both, then maybe you could reserve two columns to act as a key. The first column is the key for that record. The second column is just a counter of which row you're on for that record. You'll have to map your fields (column E of Row 2 for employee 12345 record). But that's pretty much as multiple sheets. You may to consider using a userform to show the info/get input from the user. You could look at John Walkenbach's enhanced data form: http://j-walk.com/ss/dataform/index.htm The source code is available for a small fee ($20 USA, IIRC). So you can modify it as much as you want. tbd wrote: Greetings, I'm using an Excel spreadsheet to define and maintain a data-set used by another application. So, the spreadsheet is a "UI" where users' control over sheet is limited. The data represents a "daily schedule" where the user contols how many days are defined. The right-most column represents the last day defined by the schedule. It's reasonable to expect a schedule will (need to) define more than 256 days (columns). I'm considering maintaining a data-set on another sheet, or in a disk-file, and just populate the UI according to what user wants to see. For instance, maybe up to 60 columns are used, and when the user wants to see a 61st day, the data is programmatically "scrolled" to the left such that column 60 holds day 61, and column 1 holds day 2. Of course it would be nice to employ the standard worksheet scroll-bars, but if I have to manage one, so be it. If anybody [is not completely confused by now, and] has tried anything like this, or if you have any enlightening ideas, I'd be interested in hearing from you. Thanks/Cheers! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to fake/simulate more than 256 columns
I didn't mean that you could transpose the data in code or by a formula. You're
still limited by the number of cells in the row. (But if that works for you, I'm taking credit <vbg.) I really meant that you'd enter your data in columns instead of rows. If you had an "input" sheet, your code could do the translation of rows to columns, though. And take a look at John's Enhanced data form. It could look very nice and professional. If you've never created a userform... Debra Dalgleish has some nice notes about userforms: http://contextures.com/xlUserForm01.html (video: http://contextures.com/xlVideos05.html#UserForm01) and http://contextures.com/xlUserForm02.html tbd wrote: Thanks Dave! I didn't know about Excel 2007 supporting 16k columns. I like this solution the best, on the other hand, I'm outta work and this gig isn't paying anything. Still, will check with "customer" to see what they're using. I've never used Transpose, but it looks very promising. I still need to talk myself into implementing the scrolling controls and data synchronization logic - but this is definately doable. I'm not sure, but I think the "keyed" approach, like transpose, also utilizes data storage on another sheet, with the keys mapping that data to cells in the UI. I hope transpose works so I can copy en mass! Excellent options, thanks! "Dave Peterson" wrote: Can you transpose your rows and columns? If you're not showing it to the user, your code could just do things "backwards" <vbg. Can you upgrade to xl2007 (with 1 million rows and 16k columns)? If no to both, then maybe you could reserve two columns to act as a key. The first column is the key for that record. The second column is just a counter of which row you're on for that record. You'll have to map your fields (column E of Row 2 for employee 12345 record). But that's pretty much as multiple sheets. You may to consider using a userform to show the info/get input from the user. You could look at John Walkenbach's enhanced data form: http://j-walk.com/ss/dataform/index.htm The source code is available for a small fee ($20 USA, IIRC). So you can modify it as much as you want. tbd wrote: Greetings, I'm using an Excel spreadsheet to define and maintain a data-set used by another application. So, the spreadsheet is a "UI" where users' control over sheet is limited. The data represents a "daily schedule" where the user contols how many days are defined. The right-most column represents the last day defined by the schedule. It's reasonable to expect a schedule will (need to) define more than 256 days (columns). I'm considering maintaining a data-set on another sheet, or in a disk-file, and just populate the UI according to what user wants to see. For instance, maybe up to 60 columns are used, and when the user wants to see a 61st day, the data is programmatically "scrolled" to the left such that column 60 holds day 61, and column 1 holds day 2. Of course it would be nice to employ the standard worksheet scroll-bars, but if I have to manage one, so be it. If anybody [is not completely confused by now, and] has tried anything like this, or if you have any enlightening ideas, I'd be interested in hearing from you. Thanks/Cheers! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to fake/simulate more than 256 columns
Hi Dave,
Thanks for the links below. I may explore the user-forms link, though, hope to avoid complexity. (Probably just fear of having to learn something new.) Thanks/Cheers! "Dave Peterson" wrote: I didn't mean that you could transpose the data in code or by a formula. You're still limited by the number of cells in the row. (But if that works for you, I'm taking credit <vbg.) I really meant that you'd enter your data in columns instead of rows. If you had an "input" sheet, your code could do the translation of rows to columns, though. And take a look at John's Enhanced data form. It could look very nice and professional. If you've never created a userform... Debra Dalgleish has some nice notes about userforms: http://contextures.com/xlUserForm01.html (video: http://contextures.com/xlVideos05.html#UserForm01) and http://contextures.com/xlUserForm02.html tbd wrote: Thanks Dave! I didn't know about Excel 2007 supporting 16k columns. I like this solution the best, on the other hand, I'm outta work and this gig isn't paying anything. Still, will check with "customer" to see what they're using. I've never used Transpose, but it looks very promising. I still need to talk myself into implementing the scrolling controls and data synchronization logic - but this is definately doable. I'm not sure, but I think the "keyed" approach, like transpose, also utilizes data storage on another sheet, with the keys mapping that data to cells in the UI. I hope transpose works so I can copy en mass! Excellent options, thanks! "Dave Peterson" wrote: Can you transpose your rows and columns? If you're not showing it to the user, your code could just do things "backwards" <vbg. Can you upgrade to xl2007 (with 1 million rows and 16k columns)? If no to both, then maybe you could reserve two columns to act as a key. The first column is the key for that record. The second column is just a counter of which row you're on for that record. You'll have to map your fields (column E of Row 2 for employee 12345 record). But that's pretty much as multiple sheets. You may to consider using a userform to show the info/get input from the user. You could look at John Walkenbach's enhanced data form: http://j-walk.com/ss/dataform/index.htm The source code is available for a small fee ($20 USA, IIRC). So you can modify it as much as you want. tbd wrote: Greetings, I'm using an Excel spreadsheet to define and maintain a data-set used by another application. So, the spreadsheet is a "UI" where users' control over sheet is limited. The data represents a "daily schedule" where the user contols how many days are defined. The right-most column represents the last day defined by the schedule. It's reasonable to expect a schedule will (need to) define more than 256 days (columns). I'm considering maintaining a data-set on another sheet, or in a disk-file, and just populate the UI according to what user wants to see. For instance, maybe up to 60 columns are used, and when the user wants to see a 61st day, the data is programmatically "scrolled" to the left such that column 60 holds day 61, and column 1 holds day 2. Of course it would be nice to employ the standard worksheet scroll-bars, but if I have to manage one, so be it. If anybody [is not completely confused by now, and] has tried anything like this, or if you have any enlightening ideas, I'd be interested in hearing from you. Thanks/Cheers! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simulate Check Box | Excel Discussion (Misc queries) | |||
simulate mouse movement | Excel Programming | |||
simulate down arrow | Excel Programming | |||
Macro to Simulate F2 | Excel Programming | |||
Simulate Click Event | Excel Programming |