Solved: Precedure that works on one sheet and does not on another one

[Kaizer]

Guys, I have an issue with the procedure that opens the form and fills the listbox with the numbers. The problem is that it works just fine if I ran it on one sheet but fails when I use the same procedure on another sheet. Can you have a look at the attached file and help me with that, please? PS. To ran the procedure just use the cmdButtons on each sheet.

[lucas]

Hey Kaizer, you forgot your attachment. Is your question that you would like it to run on any sheet that is active or only on one specific sheet?

[Kaizer]

Strange, there were two posts: one with the attachment and another one w/o. Now it tells me that upload is in progress.

Anyway, the idea is to run the procedure on one specific sheet.
The problem is that when I open the form the on sheet("Front") I have only one column filled in the list box. When I open the form on sheet("Stock") I have both columns filled. The second column is filled with the numbers.
Here is the code
Private Sub UserForm_Initialize()
'With this we determine the selection on the Fornt page i.e. BU/BM
Dim i As Integer
If Sheets("Front").BU_DT = True And Sheets("Front").BM_Tx = True Then i = 1
If Sheets("Front").BU_DT = True And Sheets("Front").BM_Rx = True Then i = 2
If Sheets("Front").BU_DT = True And Sheets("Front").BM_Both = True Then i = 3
If Sheets("Front").BU_MOB = True And Sheets("Front").BM_Tx = True Then i = 4
If Sheets("Front").BU_MOB = True And Sheets("Front").BM_Rx = True Then i = 5
If Sheets("Front").BU_MOB = True And Sheets("Front").BM_Both = True Then i = 6
If Sheets("Front").BU_Both = True And Sheets("Front").BM_Tx = True Then i = 7
If Sheets("Front").BU_Both = True And Sheets("Front").BM_Rx = True Then i = 8
If Sheets("Front").BU_Both = True And Sheets("Front").BM_Both = True Then i = 9
'''''''''''''''''''
'MsgBox i
'''''''''''''''''''
'Set up Ranges'
Dim Region As Range, Series As Range, Topseller As Range, Brand As Range, Amount As Range
Dim Cel As Range
Set Region = Sheets("Stock").Range("Stock_Region")
Set Series = Sheets("Stock").Range("Stock_Series")
Set Topseller = Sheets("Stock").Range("Stock_Topseller")
Set Brand = Sheets("Stock").Range("Stock_Brand")
Set Amount = Sheets("Stock").Range("Stock_Amnt")
Dim d, a
Dim Arr()
Set d = CreateObject("Scripting.Dictionary")
'''''''''''''''''''
Select Case i
Case Is = 1 'DT - Tx
For Each Cel In Region
On Error Resume Next
If Cel.Offset(0, 1) = "DT" And Cel.Offset(0, 3) = "Yes" Then
d.Add Cel.Text, Cel.Text
End If
'MsgBox Cel
Next Cel
a = d.items
ReDim Arr(d.Count - 1, 1)
For j = 0 To d.Count - 1
Arr(j, 0) = a(j)
Arr(j, 1) = Evaluate("SUMPRODUCT((" & Region.Address & "=""" & a(j) & """ )*" & Amount.Address & ")")
Next j
lstPreview.List = Arr
End Select
End Sub

[CBrine]

Kaizer,
I'm not entirely sure what you are doing, but a quick review of your code shows you use


On Error Resume Next


I can't see any reason why you would want to use this, and many reasons why you shouldn't. Are you using it to suppress a specific error condition? Maybe a division by zero or something, if so, there are better ways(Let me know and I will post a better error handling routine for you). I would try removing this, and running your code from your front sheet and then try running it from a different sheet, see if any new errors appear.

HTH
Cal

[Kaizer]

CBrine,

I create an object that I want to be filled with the unique values. On Error Resume Next is the way to avoid an error message when duplicate gets in the way.

[CBrine]

Kaizer,
I kinda figured that. The only problem is that the on error resume next suppresses ALL errors, not just the one error you are looking for. What is the error code for the error you want to suppress. I will revise your code to ignore only that specific error.
I think the error suppression may be suppressing a critical error that only occurs when you run the code from a different worksheet.

Cal

[Kaizer]

CBine,

For some reason I couldn't upload the file from work. Hope it will work this time.

Thank you for your help.

[CBrine]

I was able to get your userform up on both sheets? Not exactly sure what you problem is? Can you give an exact example of what you are doing that is not working? Step by Step?

I would also change your form_initialize code to this.


Private Sub UserForm_Initialize()
'With this we determine the selection on the Fornt page i.e. BU/BM
On Error GoTo ErrHandler
Dim i As Integer
If Sheets("Front").BU_DT = True And Sheets("Front").BM_Tx = True Then i = 1
If Sheets("Front").BU_DT = True And Sheets("Front").BM_Rx = True Then i = 2
If Sheets("Front").BU_DT = True And Sheets("Front").BM_Both = True Then i = 3
If Sheets("Front").BU_MOB = True And Sheets("Front").BM_Tx = True Then i = 4
If Sheets("Front").BU_MOB = True And Sheets("Front").BM_Rx = True Then i = 5
If Sheets("Front").BU_MOB = True And Sheets("Front").BM_Both = True Then i = 6
If Sheets("Front").BU_Both = True And Sheets("Front").BM_Tx = True Then i = 7
If Sheets("Front").BU_Both = True And Sheets("Front").BM_Rx = True Then i = 8
If Sheets("Front").BU_Both = True And Sheets("Front").BM_Both = True Then i = 9
'''''''''''''''''''
'MsgBox i
'''''''''''''''''''
'Set up Ranges'
Dim Region As Range, Series As Range, Topseller As Range, Brand As Range, Amount As Range
Dim Cel As Range
Set Region = Sheets("Stock").Range("Stock_Region")
Set Series = Sheets("Stock").Range("Stock_Series")
Set Topseller = Sheets("Stock").Range("Stock_Topseller")
Set Brand = Sheets("Stock").Range("Stock_Brand")
Set Amount = Sheets("Stock").Range("Stock_Amnt")
Dim d, a
Dim Arr()
Set d = CreateObject("Scripting.Dictionary")
'''''''''''''''''''
Select Case i
Case Is = 1 'DT - Tx
For Each Cel In Region
'On Error Resume Next
If Cel.Offset(0, 1) = "DT" And Cel.Offset(0, 3) = "Yes" Then
d.Add Cel.Text, Cel.Text
End If
'MsgBox Cel
Next Cel
a = d.items
ReDim Arr(d.Count - 1, 1)
For j = 0 To d.Count - 1
Arr(j, 0) = a(j)
Arr(j, 1) = Evaluate("SUMPRODUCT((" & Region.Address & "=""" & a(j) & """ )*" & Amount.Address & ")")
Next j
lstPreview.List = Arr
End Select
Exit Sub
ErrHandler:
Select Case Err.Number
Case 457
Resume Next
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
End Sub



HTH
Cal

[mdmackillop]

d.add requires the Error statement.

For Each Cel In Region
If Cel.Offset(0, 1) = "DT" And Cel.Offset(0, 3) = "Yes" Then
On Error Resume Next
d.Add Cel.Text, Cel.Text
End If
Next Cel
On Error GoTo 0

[CBrine]

MD,
My error checking will take care of that.
Case 457
Resume Next

Cal

[mdmackillop]

OK. I didn't read that far! It seems a bit sledgehammer to me though, when you know an error will occur to involve an extra loop.

[Kaizer]

CBrine,

I attach the pictre to show not working exmple. In this example only one column is filled in the listbox (regions).

The working example has two columns filled in the listbox (regions and amounts).

[CBrine]

OK. I didn't read that far! It seems a bit sledgehammer to me though, when you know an error will occur to involve an extra loop
The orginal was the sledge hammer, mine's more of one of those rubber mallets for punching dents out of your car:-)
I'm assuming that the
on error goto 0
disables the error checking? I've seen it before, but never actually used it. If that's the case, then yours makes more sense. Didn't even realize you could do that. Does that cause the debug options error message to appear on a message again?

Kaizer,
Figured out the problem.
Change your evaluate to this.

Arr(j, 1) = Evaluate("SUMPRODUCT((Stock!" & Region.Address & "=""" & a(j) & """ )*Stock!" & Amount.Address & ")")

When you run the code with the "front" sheet activated, it tries to run the sumproduct on that sheet. It won't work without the sheet reference in the formula.

HTH
Cal

[Kaizer]

Yes, that's what I wanted. Thank you CBrian.