Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about private subs
I have worksheet that I want to trigger a macro when a user selects a value
from a pivot table. For the most part, the macro works fine except I also want to delete a range of rows within a different tab in the workbook, but in the macro when I switch to the other tab and set the range to delete, I get a "Run-time error 1004 - Application defined or object defined error". Am I not able to access another tab from private sub? Any help would be greatly appreciated. Thanks, Mary-Lou |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about private subs
I have seen this occur when you select another tab and address the range only
without the worksheet reference using code in a macro which has been called by an event and is in one of the Sheets areas. (I think it is because Excel is confused about which worksheet it is addressing.) After selecting the worksheet one would think that you should be able to address a range simply as Range("A1:A30"). However, try Sheets("Sheet1").Range("A1:A30") or ActiveSheet.Range("A1:A30"). I found that this fixes the problem. Regards, OssieMac "MLK" wrote: I have worksheet that I want to trigger a macro when a user selects a value from a pivot table. For the most part, the macro works fine except I also want to delete a range of rows within a different tab in the workbook, but in the macro when I switch to the other tab and set the range to delete, I get a "Run-time error 1004 - Application defined or object defined error". Am I not able to access another tab from private sub? Any help would be greatly appreciated. Thanks, Mary-Lou |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about private subs
Thanks, will give that a try.
"OssieMac" wrote: I have seen this occur when you select another tab and address the range only without the worksheet reference using code in a macro which has been called by an event and is in one of the Sheets areas. (I think it is because Excel is confused about which worksheet it is addressing.) After selecting the worksheet one would think that you should be able to address a range simply as Range("A1:A30"). However, try Sheets("Sheet1").Range("A1:A30") or ActiveSheet.Range("A1:A30"). I found that this fixes the problem. Regards, OssieMac "MLK" wrote: I have worksheet that I want to trigger a macro when a user selects a value from a pivot table. For the most part, the macro works fine except I also want to delete a range of rows within a different tab in the workbook, but in the macro when I switch to the other tab and set the range to delete, I get a "Run-time error 1004 - Application defined or object defined error". Am I not able to access another tab from private sub? Any help would be greatly appreciated. Thanks, Mary-Lou |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about private subs
Hi again Mary-Lou,
I just did a test with the following code in a sub as follows:- Private Sub Worksheet_Change(ByVal Target As Range) 'Called from change in Sheet1 Sheets("Sheet2").Select 'The following row fails even though the worksheet is selected 'However, it works fine in a normal module. Rows("3:5").Delete ActiveSheet.Rows("3:5").Delete 'This works 'This is the preferred method without selecting the worksheet Sheets("Sheet2").Rows("3:5").Delete Regards, OssieMac "MLK" wrote: I have worksheet that I want to trigger a macro when a user selects a value from a pivot table. For the most part, the macro works fine except I also want to delete a range of rows within a different tab in the workbook, but in the macro when I switch to the other tab and set the range to delete, I get a "Run-time error 1004 - Application defined or object defined error". Am I not able to access another tab from private sub? Any help would be greatly appreciated. Thanks, Mary-Lou |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about private subs
Ok thanks.
"OssieMac" wrote: Hi again Mary-Lou, I just did a test with the following code in a sub as follows:- Private Sub Worksheet_Change(ByVal Target As Range) 'Called from change in Sheet1 Sheets("Sheet2").Select 'The following row fails even though the worksheet is selected 'However, it works fine in a normal module. Rows("3:5").Delete ActiveSheet.Rows("3:5").Delete 'This works 'This is the preferred method without selecting the worksheet Sheets("Sheet2").Rows("3:5").Delete Regards, OssieMac "MLK" wrote: I have worksheet that I want to trigger a macro when a user selects a value from a pivot table. For the most part, the macro works fine except I also want to delete a range of rows within a different tab in the workbook, but in the macro when I switch to the other tab and set the range to delete, I get a "Run-time error 1004 - Application defined or object defined error". Am I not able to access another tab from private sub? Any help would be greatly appreciated. Thanks, Mary-Lou |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Private Textbox Exit Sub question... | Excel Worksheet Functions | |||
Excel VBA: Automatically activating Private Subs | Excel Programming | |||
Basic Question on Subs | Excel Worksheet Functions | |||
Private subs | Excel Programming | |||
calling private subs | Excel Programming |