Problems with same macro diferent place

Hi EveryBody


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

Thanks a lot for your advice.

Joe


This one from the Controls Combobox

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 Sub

This one from the Forms Combobox

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 Sub

Don't use select.

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

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



Norie wrote:
>
> Don't use select.
>
> Code:
> --------------------
>
> Private Sub ComboBox1_Change()
> Application.ScreenUpdating = False
> Sheets("Proceso").Range("ListaMejorOpcion").Sort Key1:=Range("P25"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> Application.ScreenUpdating = True
> End Sub
> --------------------
>
> --
> Norie
> ------------------------------------------------------------------------
> Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362
> View this thread: http://www.excelforum.com/showthread...hreadid=374156

--

Dave Peterson

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

Originally Posted by 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



Norie wrote:
>
> Don't use select.
>
> Code:
> --------------------
>
> Private Sub ComboBox1_Change()
> Application.ScreenUpdating = False
> Sheets("Proceso").Range("ListaMejorOpcion").Sort Key1:=Range("P25"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> Application.ScreenUpdating = True
> End Sub
> --------------------
>
> --
> Norie
> ------------------------------------------------------------------------
> Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362
> View this thread: http://www.excelforum.com/showthread...hreadid=374156

--

Dave Peterson

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.

Got it Norie, Thanks again.

Jose Luis

Carpe Diem!

Originally Posted by 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.

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

--
Regards,
Tom Ogilvy



"jose luis" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
> Dave Peterson Wrote:
> > 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
> >
> >
> >
> > Norie wrote:
> > >
> > > Don't use select.
> > >
> > > Code:
> > > --------------------
> > >
> > > Private Sub ComboBox1_Change()
> > > Application.ScreenUpdating = False
> > > Sheets("Proceso").Range("ListaMejorOpcion").Sort
> > Key1:=Range("P25"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1,
> > MatchCase:=False, Orientation:=xlTopToBottom
> > > Application.ScreenUpdating = True
> > > End Sub
> > > --------------------
> > >
> > > --
> > > Norie
> > >
> > ------------------------------------------------------------------------
> > > Norie's Profile:
> > http://www.excelforum.com/member.php...o&userid=19362
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=374156
> >
> > --
> >
> > Dave Peterson
>
>
> --
> jose luis
> ------------------------------------------------------------------------
> jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
> View this thread: http://www.excelforum.com/showthread...hreadid=374156
>

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

Sorry.

Tom Ogilvy wrote:
>
> 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).
>
> --
> Regards,
> Tom Ogilvy
>
> "jose luis" <[email protected]> wrote
> in message news:[email protected]...
> >
> > 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
> >
> > Dave Peterson Wrote:
> > > 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
> > >
> > >
> > >
> > > Norie wrote:
> > > >
> > > > Don't use select.
> > > >
> > > > Code:
> > > > --------------------
> > > >
> > > > Private Sub ComboBox1_Change()
> > > > Application.ScreenUpdating = False
> > > > Sheets("Proceso").Range("ListaMejorOpcion").Sort
> > > Key1:=Range("P25"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1,
> > > MatchCase:=False, Orientation:=xlTopToBottom
> > > > Application.ScreenUpdating = True
> > > > End Sub
> > > > --------------------
> > > >
> > > > --
> > > > Norie
> > > >
> > > ------------------------------------------------------------------------
> > > > Norie's Profile:
> > > http://www.excelforum.com/member.php...o&userid=19362
> > > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=374156
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >
> > --
> > jose luis
> > ------------------------------------------------------------------------
> > jose luis's Profile:
> http://www.excelforum.com/member.php...o&userid=13312
> > View this thread: http://www.excelforum.com/showthread...hreadid=374156
> >

--

Dave Peterson