How to use cel l(#,#) format when worksheet isn't active

[ilya2004]

Hello everyone.

I have a macro that fills a list box box based on the data which is in another combo box. The code is as follows:

Dim MyCell  Range
Dim wUD As Worksheet
Set wUD = sheets("User Data")
For Each MyCell In wUD.Range("z2:z" & Range("A65536").End(xlUp).Row)
         If Not MyCell = "" Then
         With iPeerGroup
            .AddItem (MyCell.Offset(0, -25).Value)
            .List(.ListCount - 1, 1) = MyCell.Offset(0, -24).Value
            .List(.ListCount - 1, 2) = Format(MyCell, "#,#")
                 End With
         End If
Next

This works fine, but only if the User Data sheet is active. I would like to to work regardless. Any ideas?
-Ilya

 

[sous2817]

Looks like you have an unqualified range. Not tested but try:

Dim MyCell  Range
Dim wUD As Worksheet
Set wUD = sheets("User Data")
For Each MyCell In wUD.Range("z2:z" & wUD.Range("A65536").End(xlUp).Row)
         If Not MyCell = "" Then
         With iPeerGroup
            .AddItem (MyCell.Offset(0, -25).Value)
            .List(.ListCount - 1, 1) = MyCell.Offset(0, -24).Value
            .List(.ListCount - 1, 2) = Format(MyCell, "#,#")
                 End With
         End If
Next

 

[ilya2004]

I'm sorry, but I can't see what you changed?

 

[ilya2004]

oh, just saw it, I'll try the code

 

[sous2817]

Dim MyCell  Range
Dim wUD As Worksheet
Set wUD = sheets("User Data")
For Each MyCell In wUD.Range("z2:z" & [B][U][I][COLOR="Red"]wUD.[/COLOR][/I][/U][/B]Range("A65536").End(xlUp).Row)
         If Not MyCell = "" Then
         With iPeerGroup
            .AddItem (MyCell.Offset(0, -25).Value)
            .List(.ListCount - 1, 1) = MyCell.Offset(0, -24).Value
            .List(.ListCount - 1, 2) = Format(MyCell, "#,#")
                 End With
         End If
Next

 

[ilya2004]

that did it, thanks!

 

[sous2817]

You're quite welcome. Good luck with the rest of your project!