cycling through listboxes

SeanWise

New Member
Joined
Dec 2, 2016
Messages
17
`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:



Code:
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).


Code:
   '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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,640
In your code, you have the following...

Code:
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

New Member
Joined
Dec 2, 2016
Messages
17
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

New Member
Joined
Dec 2, 2016
Messages
17
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

New Member
Joined
Dec 2, 2016
Messages
17

ADVERTISEMENT

I just tried this:

Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,640
Since your listbox is located on a userform, you can use the Controls collection to iterate through your listboxes...

Code:
With PredictionMain.Controls("ListBox" & i)

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

Code:
PredictionMain.ListBox1.AddItem

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

SeanWise

New Member
Joined
Dec 2, 2016
Messages
17

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows
You are filling the listboxes with values not formulas, unless you rerun the code that populates the listboxes the values won't change.
 

SeanWise

New Member
Joined
Dec 2, 2016
Messages
17
Hi Norie, isn't the code being rerun every time it loops? The additem is within the for loop after all.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,640
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,524
Messages
5,659,315
Members
418,496
Latest member
WHYCHumphrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top