Solved: Listbox with data from other sheet problem

[Cobolt]

Within the same workbook I have several sheets/pages with varios data.

In one Sheet I have a simple multiselect listbox wich sums up each colums values into separate cells.

My problem:
Everything works well as long as I have data and listbox on the same page, but when I put the listbox and the data table on separate pages (I´m still directing the listbox to the data table via the listbox properties dialog and ListFillRange ) I can still view and select any list object but summarys are no longer done in the cells that are supposed to sum up each column, they just get 0 as value.


My code:

[vba]
Private Sub ListBox1_Change()
Dim i As Integer

Range("B17") = 0
Range("C17") = 0
Range("D17") = 0
Range("E17") = 0
Range("F17") = 0
Range("G17") = 0
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
Range("B17") = Range("B17") + Cells(i + 21, 38)
Range("C17") = Range("C17") + Cells(i + 21, 40)
Range("D17") = Range("D17") + Cells(i + 21, 42)
Range("E17") = Range("E17") + Cells(i + 21, 44)
Range("F17") = Range("F17") + Cells(i + 21, 46)
Range("G17") = Range("G17") + Cells(i + 21, 48)
End If
Next i
End Sub
[/vba]

Any help would be greatly appriciated!

And please excuse my english as it's not my native language.

[Aussiebear]

Welcome Cobolt to the forum. Your english is fine. From my interpretation of what you are describing in the third paragraph is that you may not be actually referencing the correct sheets. However I am unable to reference this in the first instance since I cannot see your actual workbook.

I am also assuming that you are wanting the listbox to reflect the new values in the cells B17:G17, (and that the individual values are coming from seperate sheets). Is this correct?

Then there's the issue with the code. The code is setting the value of the cells B17:G17 to Zero

[mdmackillop]

Ausiebear is correct on all counts. To reference another sheet, you need something like
[VBA]
Private Sub ListBox1_Change()
Dim i As Integer

With Sheets("Sheet2")
.Range("B17") = 0
.Range("C17") = 0
.Range("D17") = 0
.Range("E17") = 0
.Range("F17") = 0
.Range("G17") = 0
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
.Range("B17") = .Range("B17") + .Cells(i + 21, 38)
.Range("C17") = .Range("C17") + .Cells(i + 21, 40)
.Range("D17") = .Range("D17") + .Cells(i + 21, 42)
.Range("E17") = .Range("E17") + .Cells(i + 21, 44)
.Range("F17") = .Range("F17") + .Cells(i + 21, 46)
.Range("G17") = .Range("G17") + .Cells(i + 21, 48)
End If
Next i
End With
End Sub

[/VBA]
which assumes the Range and Cell references are on the same sheet. Also setting values to Zero and including them in the calculation seems wrong.

[Cobolt]

Thanx for the nice welcome and your help so far!


I´m a bit unshure about the words for it but I´ll try to explain clearer

I have a workbook, in this there are separate pages (tabs) containing data

On a separate page (or tab) I have the datatable itself with columns.

On another separate page (within the same workbook) I have a multi select listbox and 6 cells where I want to se each selected value from each column summarized.



As long as I keep datatable, listbox and summary cells on the same page, everything steams on like clockwork, but when I try to move the data table to another page (tab?) within the workbook, then I can still view and select in the listbox, but the data from the separate following columns are not fetched and summarized as it should be, values in the 6 cells remain as 0.

The reason I put in the Range() = 0 thingy in the beginning is to make shure I get my 6 summary cells "reset" and don´t create some kind of neverending summarys. As long as listbox, summary cells and data table are on the same page (tab) the code does what I´d want it to and seems to work (not saying that it is a good thing just because of that! )

May I be doing something wrong within the ListBox property dialog?
Maby wrong way to point towards the table within the ListFillRange value? - added a screenshot from the dialog, the page I have my data table on is named "Formler".

Another idea from my side is that the code works until I reach the first "Cells()" command where the references to the row and columns MAY still aim towards the page the code itself are placed on and not the page where the data table are. (just guessing here).

[mdmackillop]

Can you post your workbook? Use Manage Attachments in the Go Advanced reply section

[Cobolt]

Here are a workbook with the problem shown!

[mdmackillop]

[VBA]With Sheets("Formler")
If Me.ListBox1.Selected(i) Then

Range("B17") = Range("B17") + .Cells(i + 21, 38)
Range("C17") = Range("C17") + .Cells(i + 21, 40)
Range("D17") = Range("D17") + .Cells(i + 21, 42)
Range("E17") = Range("E17") + .Cells(i + 21, 44)
Range("F17") = Range("F17") + .Cells(i + 21, 46)
Range("G17") = Range("G17") + .Cells(i + 21, 48)

End If
End With[/VBA]

[Cobolt]

Works great!

Thank you very much!