Problems with same macro diferent place

[jose luis]

Hi EveryBody


I just discovered the Control Toolbox, (thanks Dominic), so i'm movin
some of my previous Forms ComboBoxes to Control Comboboxes. I have thi
macro that runs fine in the Forms Combobox but fails in the Contro
Combobox embeded in the Sheet("Captura Datos"). The error i get say
"Run time error 1004:" Method "Range" of Objetc "_Worksheet" failed
Here are the declarations of the same macro in different places. Coul
you show me what i doing wrong?

Thanks a lot for your advice.

Joe


This one from the Controls Combobox


Code
-------------------
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Sheets("Proceso").Select
Range("ListaMejorOpcion").Sort Key1:=Range("P25"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("Captura Datos").Select
Range("A38").Select
Application.ScreenUpdating = True
End Su
-------------------


This one from the Forms Combobox


Code
-------------------
Sub SortMejorOpcn()
'
' SortMejorOpcn Macro
' Macro recorded 8/1/2004 by jose luis
Application.ScreenUpdating = False
Sheets("Proceso").Select
Range("ListaMejorOpcion").Sort Key1:=Range("P25"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("Captura Datos").Select
Range("A38").Select
Application.ScreenUpdating = True
End Su

 

[Dave Peterson]

I think I'd be careful with that P25 cell, too:

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
with Sheets("Proceso")
.Range("ListaMejorOpcion").Sort Key1:=.Range("P25"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with
Application.ScreenUpdating = True
End Sub

 

[jose luis]

Thanks Norie and Dave,

I realize and learn what my mistake was. Dave says be careful with the
P25 Cell, Should i put a Defined name to the cell instead?


Thanks again for your responses, They help a lot, I learn a lot....

Jose Luis

 

[Norie]

Jose

What I think Dave was referring to was that you weren't referring to
the worksheet when you referred to the P25 cell.

So Excel would use cell P25 in the currently active sheet, which may or
may not be the correct sheet.

 

[Tom Ogilvy]

Dave showed you the corrected code - what the correction should be.

Using the WITH statement, both range references were preceded by a period,
so they reference the same worksheet (Proceso)

P25 should definitely reference the same sheet where ListaMejorOpcion is
located. As Norie said, the unqualified Range("P25") refers to the sheet
containing the code. (actually, he said the active sheet, which is
incorrect. In a sheet module, it refers to the sheet containing the code -
this is a problem you may encounter over and over as you convert your code).

 

[jose luis]

Got it Norie, Thanks again.

Jose Luis

Carpe Diem!

 

[Dave Peterson]

Oops. I didn't notice the name of the sub or that it would be in that
worksheet's module.

Sorry.

 

[Tom Ogilvy]

Yours was right Dave (unless I missed something). It was just a statement
by Norie that was a little off (in my opinion) that I was referring to.

 

[Dave Peterson]

uhhhh, sometimes the sky is too red to see anything!