Problems with same macro diferent place

[jose luis]

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

Please Login or Register  to view this content.

This one from the Forms Combobox

Please Login or Register  to view this content.

[Norie]

Don't use select.

Please Login or Register  to view this content.

[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 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

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

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

[jose luis]

Got it Norie, Thanks again.

Jose Luis

Carpe Diem!

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

--
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]

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

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

--
Regards,
Tom Ogilvy



"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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

[Dave Peterson]

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

Tom Ogilvy wrote:
>
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > 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

--

Dave Peterson