Control User Form object properties from VBA code

[Nikko963]

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.

 

[ker_01]

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]

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!

 

[Sam Punalur]

You have to use the Shapes object and its ControlFormat method to manipulate the input range. Copy the

Option Explicit
Private Sub ChangeInputRange()

Call InputRangeModifier(ThisWorkbook.ActiveSheet, "MyListBox", "A1:A10")

End Sub

Public Sub InputRangeModifier(wksSheet As Worksheet, strShapeControlName As String, strInputRange As String)

wksSheet.Shapes(strShapeControlName).ControlFormat.ListFillRange = strInputRange

End Sub

 

[andreco]

Sam: Can you please explain why the Shapes methos has to be used in this case? Perhaps it would help clear the confusion i - and maybe many - have in regards to the different types of controls (Form, ActiveX, Objetcs, etc).