Dynamically Updating ListFillRange for Combobox 1

[ctbaker]

I have a combo box on Sheet2 named nonSTIP1 which gets its pulldown choices from Sheet3 range D210

When a combo box selection is made on Sheet1, I want to change the ListFillRange value for the combo box named nonSTIP1 on Sheet2 by pulling data from Sheet3.

I have read posts on this board that seem to give the solution but I keep getting errors in Excel when I try the solutions. I made the combo boxes using the Control toolbar.

I am writing this all in a subroutine stored as a module. Any ideas? The errors I keep getting make it appear that excel doesn't recognize the nonSTIP1 named combobox.

Thanks

 

[Zathras]

See thread68-578865

 

[ctbaker]

Here is more info on the issue I am having...

I am trying the line of code

ActiveSheet.Shapes("nonSTIP1&quot.ControlFormat.ListFillRange = "'Menu Data'!$J$2:$J$34"

this code resides in SHEET2 and will eventually be activated by a selection made in SHEET1. "Menu Data" is the title of SHEET3.

I am getting the error. "The item with the specified name wasn't found."

Any ideas?
Thanks

 

[Zathras]

Not sure why you are using the Shapes collection.

Also I can't reconcile the two statements:
...gets its pulldown choices from Sheet3 range D210...
and
...ControlFormat.ListFillRange = "'Menu Data'!$J$2:$J$34"...

But anyway....
Assuming that the combo boxes you got were from the "Control Toolbox" toolbar, and the combo box on "Sheet1" has its "LinkedCell" property pointing to a cell with the range name "STIPcode"...

...And further assuming that you have a range name set up for each of the possible choices in the Sheet1 combo box (very important!!!)...

...Then this code illustrates one way to do what I think you are trying to do:

In the Sheet1 code page:
[blue]

Private Sub ComboBox1_Change()
  Call SetnonSTIP1ComboBox
End Sub

[/color]
And in a code module:
[blue]

Sub SetnonSTIP1ComboBox()
Dim i As Integer
Dim o As ComboBox
  With Sheets("Sheet2")
    For i = 1 To .OLEObjects.Count
      If TypeName(Sheets("Sheet2").OLEObjects(i).Object) = "ComboBox" Then
        Set o = Sheets("Sheet2").OLEObjects(1).Object
        If o.Name = "nonSTIP1" Then
          o.ListFillRange = [STIPcode]
          o.Value = ""
          If o.LinkedCell <> &quot;&quot; Then .Range(o.LinkedCell) = &quot;&quot;
        End If
      End If
    Next i
  End With
  Set o = Nothing
End Sub

[/color]

 

[ctbaker]

Thanks Zathras.

Your code does what I need. One last question

I want o.ListFillRange to be defined by a variable called ML. It is defined before the subroutine is called. I am having trouble passing the value of ML to the subroutine. I realize this is probably a trivial matter but I must ask it nonetheless.

Thanks!

 

[ctbaker]

Solved the problem.. declared ML as a public variable and cleaned up this last issue..

thanks again for all the help!!

 

[Zathras]

You are describing the use of parameters:
Assume the calling routine looks like this:

Sub MySub()
Dim ML As ?????????????

[green]

'<--- need something here

[/color]

:
:
   Call SetnonSTP1ComboBox( ML )
:
:
End Sub

Now, depending on how ML is defined (I wish you would have told me) you can set up the function like one of the following:

If you used

 Dim ML As Range

and ML is a Range object, then you could do this:
[blue]

Sub SetnonSTIP1ComboBox( MyListRange as Range )
:
:
  o.ListFillRange = MyListRange.Address
:

[/color]
On the other hand if you used

 Dim ML As String

and ML is something like "$J4:$J17" then you could do this:
[blue]

Sub SetnonSTIP1ComboBox( MyListRangeAddress as String )
:
:
  o.ListFillRange = MyListRangeAddress
:

[/color]