| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select method of range class fails
select method of range class fails (AUGHHH!!!)
sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
This should work:
Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Copy Befo=Sheets(1) With Sheets(1) .Name = "1st Issue" .Shapes("cbReformat").Delete .Shapes("cbGenerateIssue").Delete End With Rows("5:7").Select End Sub It will only work once. If you try it again, it will halt when it tries to rename the new sheet "1st Issue", which would already exist. You could insert code to remove any existing sheet with that name first. -Glenn Ray "mark kubicki" wrote: select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
sorry... but it doesn't for me...
"Glenn Ray" wrote in message ... This should work: Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Copy Befo=Sheets(1) With Sheets(1) .Name = "1st Issue" .Shapes("cbReformat").Delete .Shapes("cbGenerateIssue").Delete End With Rows("5:7").Select End Sub It will only work once. If you try it again, it will halt when it tries to rename the new sheet "1st Issue", which would already exist. You could insert code to remove any existing sheet with that name first. -Glenn Ray "mark kubicki" wrote: select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
sorry to say, but that didn't work either...
"Don Guillett" wrote in message ... Mark Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Copy Befo=Sheets(1).Name = "1st Issue" with sheets("1st Issue") .Shapes("cbReformat").Delete .Shapes("cbGenerateIssue").Delete 'you shouldn't need to select but you don't say what is next .select .Rows("5:7").Select end with -- Don Guillett SalesAid Software "mark kubicki" wrote in message ... select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
Is the error occurring at the same line in the code (Rows("5:7").Select) ?
Is the error the same (select method of range class fails)? What if you inserted a message box before the select: MsgBox "New Sheet inserted." Rows("5:7").Select Just curious if you get an error at the message box command or the select command. The subroutine worked fine for me. -Glenn "mark kubicki" wrote: sorry... but it doesn't for me... "Glenn Ray" wrote in message ... This should work: Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Copy Befo=Sheets(1) With Sheets(1) .Name = "1st Issue" .Shapes("cbReformat").Delete .Shapes("cbGenerateIssue").Delete End With Rows("5:7").Select End Sub It will only work once. If you try it again, it will halt when it tries to rename the new sheet "1st Issue", which would already exist. You could insert code to remove any existing sheet with that name first. -Glenn Ray "mark kubicki" wrote: select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
the error is at the command (the box displays without a problem)
"Glenn Ray" wrote in message ... Is the error occurring at the same line in the code (Rows("5:7").Select) ? Is the error the same (select method of range class fails)? What if you inserted a message box before the select: MsgBox "New Sheet inserted." Rows("5:7").Select Just curious if you get an error at the message box command or the select command. The subroutine worked fine for me. -Glenn "mark kubicki" wrote: sorry... but it doesn't for me... "Glenn Ray" wrote in message ... This should work: Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Copy Befo=Sheets(1) With Sheets(1) .Name = "1st Issue" .Shapes("cbReformat").Delete .Shapes("cbGenerateIssue").Delete End With Rows("5:7").Select End Sub It will only work once. If you try it again, it will halt when it tries to rename the new sheet "1st Issue", which would already exist. You could insert code to remove any existing sheet with that name first. -Glenn Ray "mark kubicki" wrote: select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
try it this way
Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Copy Befo=Sheets(1) with activesheet .Name = "1st Issue" .Shapes("cbReformat").Delete .Shapes("cbGenerateIssue").Delete 'you shouldn't need to select but you don't say what is next .select .Rows("5:7").Select end with Don Guillett SalesAid Software "mark kubicki" wrote in message ... sorry to say, but that didn't work either... "Don Guillett" wrote in message ... Mark Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Copy Befo=Sheets(1).Name = "1st Issue" with sheets("1st Issue") .Shapes("cbReformat").Delete .Shapes("cbGenerateIssue").Delete 'you shouldn't need to select but you don't say what is next .select .Rows("5:7").Select end with -- Don Guillett SalesAid Software "mark kubicki" wrote in message ... select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
Try re-selecting the sheet...
Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Sheets("1st Issue").Select Rows("5:7").Select Selecting can be a little problematic. I avoid selecting wherever possible so I hope this works... HTH "mark kubicki" wrote: select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
"Jim Thomlinson" wrote in message ... Try re-selecting the sheet... Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Sheets("1st Issue").Select Rows("5:7").Select Selecting can be a little problematic. I avoid selecting wherever possible so I hope this works... HTH "mark kubicki" wrote: select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
what i'm needing to do is convert the formula result across the sheet to
values... maybe instead of trying to fix this mess, there's a different (more efficient) way?? "Jim Thomlinson" wrote in message ... Try re-selecting the sheet... Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Sheets("1st Issue").Select Rows("5:7").Select Selecting can be a little problematic. I avoid selecting wherever possible so I hope this works... HTH "mark kubicki" wrote: select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
|
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select method of range class fails
Private Sub cbGenerateIssue_Click()
Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Activesheet.Rows("5:7").Select -- Regards, Tom Ogilvy "mark kubicki" wrote in message ... select method of range class fails (AUGHHH!!!) sheet is unprotected sheet has a control button "cbGenerateIssue" code had been created by recording a macro (where it worked...) select fails even if control button , which calls the code, has not yet been deleted Private Sub cbGenerateIssue_Click() Sheets("Issue worksheet").Select Sheets("Issue worksheet").Copy Befo=Sheets(1) Sheets("Issue worksheet (2)").Select Sheets("Issue worksheet (2)").Name = "1st Issue" ActiveSheet.Shapes("cbReformat").Select Selection.Delete ActiveSheet.Shapes("cbGenerateIssue").Select Selection.Delete ' -------- this next line fails (or any variation of it) Rows("5:7").Select had tried some variations: ...Sheets("1st Issue).Rows("5... ...put the select into a function ... moved the calling control deletion to after select... nothing thanks in advance, mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select method of Range class failed - but why??? | Excel Programming | |||
| select method of range class failed | Excel Programming | |||
| select method of range class failed | Excel Programming | |||
| Select method of Range fails | Excel Programming | |||
| Select method of Range class failure | Excel Programming | |||
Linear Mode
