| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Place a button on a worksheet and assign a macro to it? | New Users to Excel | |||
| Formula/Macro to place data on the next blank row | Excel Discussion (Misc queries) | |||
| Place an IF Statement into a Macro 2007 and 2003 | Excel Discussion (Misc queries) | |||
| Problems with same macro diferent place | Excel Programming | |||
| Place code in a new workbook with a macro. | Excel Programming | |||
Linear Mode
