cycling through listboxes

[SeanWise]

`enter code here`Ok, so basically what this code is supposed to do is;


-start a loop**
-look up values and store them as variables
-put all variables into the 1st listbox
-loop back and
-get new variables and...
-...put in the 2nd listbox
-loop 4 times until all 4 listboxes are complete.


For simplicity I have named the listboxes ListBox1, Listbox2, ListBox3 & Listbox4.


In the With Statement, the Code gives me a 1004 error: unable to get the OLEObjects property out of the worksheet class, which means it can't find it, I guess.


Can anyone see what I'm missing?


Note:

With ActiveSheet.OLEObject("ListBox" & i).Value

also doesn't work. Is OLEObject only for ActiveX? How would I do this if I'm using form controls (if that's the problem).

   'current year predictions
    Dim ForecastYear As Double
    ForecastYear = Year(Now) + 0.1


    Dim i As Integer
    Dim Forecast As Double
    Dim wForecast As Double
    Dim wDemand As Double


    For i = 1 To 4 'loop information to place into listboxes for current year's quarterly predictions
    
            'lookup info to place into 1st row of listbox
            On Error Resume Next
                Forecast = Application.VLookup(ForecastYear, Sheets(ProdCode).Range("A1:S5000"), 10)
            On Error GoTo 0
                If IsError(Forecast) Then
                    MsgBox "couldn't find '" & ForecastYear & "' in Sheets '" & ProdCode & "'"
                    Exit Sub
                End If
            Forecast = Round(Forecast, 2)
        
            'lookup info to place into 2nd row of listbox
            On Error Resume Next
                wForecast = Application.VLookup(ForecastYear, Sheets(ProdCode).Range("A9:o5000"), 15)
            On Error GoTo 0
            
            wForecast = Round(wForecast, 2)


            'lookup info to place into 3rd row of listbox
            On Error Resume Next
                wDemand = Application.VLookup(ForecastYear, Sheets(ProdCode).Range("A9:m5000"), 13)
            On Error GoTo 0
           
            wDemand = Round(wDemand, 2)
 


                ForecastYear = ForecastYear + 0.1 'add, so that next loop finds next quarterly value
        
    '-----------------------------------------------------------------------------
                With ActiveSheet.OLEObjects("ListBox" & i).Object  'add current quarterly info to the current listbox
                    .AddItem ""
                    .AddItem Forecast
                    .AddItem ""
                    .AddItem ""
                    .AddItem wForecast
                    .AddItem ""
                    .AddItem ""
                    .AddItem wDemand
                End With
                
         Next i 'go to next listbox loop

Cheers Anyone!

 

[Domenic]

In your code, you have the following...

With ActiveSheet.OLEObjects("ListBox" & i).Object

The syntax is correct. However, it refers to the active worksheet. So you'll need to make sure that the sheet containing your listboxes is the active sheet when running the macro. Is this the case?

 

[SeanWise]

I'm a bit confused about this point. The listboxes are on a userform I have created. The worksheet that contains the data is active, yes, but there are no controls on it, just data, which is read and placed in the userform.

 

[SeanWise]

I'm pretty sure I'm using form controls, not activeX too. Seems there's no option to use ActiveX in a userform, only in worksheets?

 

[SeanWise]

I just tried this:

With PredictionMain.ListBox("ListBox" & i) 'add current quarterly info to the current listbox"
                .AddItem ""
                .AddItem Forecast

but it gives me the error "Method or data member not found."

PredictionMain is the name of the Userform.

 

[Domenic]

Since your listbox is located on a userform, you can use the Controls collection to iterate through your listboxes...

With PredictionMain.Controls("ListBox" & i)

For your future reference, though, one can refer to a listbox by code name as well...

PredictionMain.ListBox1.AddItem

Also, note that objects placed on a userform are in fact ActiveX controls.

 

[SeanWise]

Thanks Domenic, I'm not getting an error now.

However, the vlookup is not updating. The range value (ForecastYear) is updating as the code loops from 2016.1 --> 2016.2-->2016.3 --> 2016.4 but the other variables (Forecast, wForecast and wDemand) just keep the data from 2016.1.

So I have 4 columns of the same data.

After researching google, I thought it might be autocalculate is off, but I can't see it under tools/options/ where other ppl were directed. Autocalculate is enabled in the worksheet itself.

Do you have any ideas?

Thanks again.

 

[Norie]

You are filling the listboxes with values not formulas, unless you rerun the code that populates the listboxes the values won't change.

 

[SeanWise]

Hi Norie, isn't the code being rerun every time it loops? The additem is within the for loop after all.

 

[Domenic]

Maybe the lookup formulas aren't finding a match and as a result return an error. Hence the listboxes get updated with NULL values, which aren't visible. Is this the case?