Problems with same macro diferent place
 

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

--
jose lui
-----------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=37415

Problems with same macro diferent place
 

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

Problems with same macro diferent place
 

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

Problems with same macro diferent place
 

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.


--
Norie
------------------------------------------------------------------------
Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362
View this thread: http://www.excelforum.com/showthread...hreadid=374156

Problems with same macro diferent place
 

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" wrote
in message ...

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

Problems with same macro diferent place
 

Got it Norie, Thanks again.

Jose Luis

Carpe Diem!

Norie Wrote:
Jose

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

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

--
jose lui
-----------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=37415

Problems with same macro diferent place
 

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" wrote
in message ...

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

Problems with same macro diferent place
 

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" wrote in message
...
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"
wrote
in message
...

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

Problems with same macro diferent place
 

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" wrote in message
...
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"
wrote
in message
...

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