Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control User Form object properties from VBA code
Hello all.
I have just spent the last 2.5 hours trying to figure out how to do this and am at the end of my rope. Excel help is no use (big surprise!), my Excel reference book contains no code examples (gotta get me a complete Excel VBA Reference guide), and everything I have found on the Web doesn't apply to me or is too confusing for an intermediate, occasional programmer like me. The irony is that my need seems fairly simple. I have an Excel 2007 spreadsheet. One of the cells has a list box (the official property name is 'MyListBox') whose Input Range I want to change based on a user's selection elsewhere in this form. Note that the list box was added as a Form Object, NOT an ActiveX Object. The list box nor the cell it is in will be selected at the time the code needs to change the list box's properties. What would the code look like to change the current Input Range from AA1:AA3 to AB1:AB3? You see? Simple request! Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control User Form object properties from VBA code
You'll have to extrapolate in case 2007 works differently but here's some
feedback based on XL2003. I added a forms object listbox to a worksheet In the worksheet code sheet, I added: Sub testchange() ListBox1.ListFillRange = "A5:A9" End Sub And that worked to change the source range from code. Is your code behind the target worksheet, or in a module? What triggers the execution of the code? Is your source range a range on the same sheet, a different sheet, or a named range? Please post more information and your code samples, if you can't get it working. HTH, Keith "Nikko963" wrote: Hello all. I have just spent the last 2.5 hours trying to figure out how to do this and am at the end of my rope. Excel help is no use (big surprise!), my Excel reference book contains no code examples (gotta get me a complete Excel VBA Reference guide), and everything I have found on the Web doesn't apply to me or is too confusing for an intermediate, occasional programmer like me. The irony is that my need seems fairly simple. I have an Excel 2007 spreadsheet. One of the cells has a list box (the official property name is 'MyListBox') whose Input Range I want to change based on a user's selection elsewhere in this form. Note that the list box was added as a Form Object, NOT an ActiveX Object. The list box nor the cell it is in will be selected at the time the code needs to change the list box's properties. What would the code look like to change the current Input Range from AA1:AA3 to AB1:AB3? You see? Simple request! Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control User Form object properties from VBA code
Thanks, Keith.
Unfortunately, that produced an error message no matter where I entered it; I tried the worksheet code sheet ["sheet1(test)"], the workbook code sheet ("thisworkbook"), or a module. (To test, I set up a one sheet test file with a single list box.) In all cases, I received the same error: "Run-time error '424': Object Required". I believe this error is tied to the difference between 2003 and 2007. In 2003 (where I tried your code and it worked), the form controls behave exactly the way ActiveX form controls work in 2007, leading me to believe they are the same. Basic form controls in 2007 appear to be a different beast (you don't get a Properties dialog with all the details like you do with 2003/ActiveX controls, for example). So, I don't think examples from 2003 aren't going to help me, unfortunately. To answer your direct questions: my code is contained in modules. The code is executed via a linked macro/vba code contained in a module when the TRUE setting of a checkbox Form Control occurs. I want to control the population of the listbox based on the TRUE of FALSE setting of the checkbox. Finally, all data and objects are on the same sheet (just waaaay off to the right). To everyone reading this: I'm still looking for help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object name on user form | Excel Programming | |||
User form Object corruption | Excel Programming | |||
user form & control properties | Excel Programming | |||
user form properties not saving | Excel Programming | |||
Changing control properties/code through code | Excel Programming |