| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Runtime Error 1004... help?
error: Unable to set the Hidden Property of the Range Class
code: under Worksheet, Change. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("D2,C6,D6,C7,D7,C8,D8,C9,D9,C14,F17,F18") = 0 Range("C25,F30,F31,F32,F33,F44,F45,F46,F47") = 0 If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("47:47").EntireRow.Hidden = False Range("C8,D8") = 0 Range("F10") = "=F6*F9" : Range("F47") = 0 ElseIf Range("B3") = "Standardized Module Height" Then Rows("6:6").EntireRow.Hidden = True : Rows("47:47").EntireRow.Hidden = True Range("C6,D6") = 0 Range("F10") = "=F7*F8" Range("F15") = "=IF((F8+SUMMARY!E6)=F9,F9,F8)" : Range("F47") = "=IF((F8+SUMMARY!E6=F9),2,1)" ElseIf Range("B3") = "Non-Modular Frame" Then Rows("6:6").EntireRow.Hidden = True Rows("8:8").EntireRow.Hidden = True Rows("10:10").EntireRow.Hidden = True Rows("15:47").EntireRow.Hidden = False Range("C6,D6") = 0 Range("C8,D8") = 0 Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("F17,F18,F30,F31,F32,F33,F44,F45,F46,F47") = 0 End If End Sub when Debugging, The highligted row is the "Rows("6:47").EntireRow.Hidden = False" when i delete that row, it goes to the next .entirerow.hidden = true/false code. this problem started when i copied my template sheet (which used this code) to make dupicates of the template. now none work. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Runtime Error 1004... help?
The syntax is ok.
Is your worksheet protected? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Derrick" wrote: error: Unable to set the Hidden Property of the Range Class code: under Worksheet, Change. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("D2,C6,D6,C7,D7,C8,D8,C9,D9,C14,F17,F18") = 0 Range("C25,F30,F31,F32,F33,F44,F45,F46,F47") = 0 If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("47:47").EntireRow.Hidden = False Range("C8,D8") = 0 Range("F10") = "=F6*F9" : Range("F47") = 0 ElseIf Range("B3") = "Standardized Module Height" Then Rows("6:6").EntireRow.Hidden = True : Rows("47:47").EntireRow.Hidden = True Range("C6,D6") = 0 Range("F10") = "=F7*F8" Range("F15") = "=IF((F8+SUMMARY!E6)=F9,F9,F8)" : Range("F47") = "=IF((F8+SUMMARY!E6=F9),2,1)" ElseIf Range("B3") = "Non-Modular Frame" Then Rows("6:6").EntireRow.Hidden = True Rows("8:8").EntireRow.Hidden = True Rows("10:10").EntireRow.Hidden = True Rows("15:47").EntireRow.Hidden = False Range("C6,D6") = 0 Range("C8,D8") = 0 Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("F17,F18,F30,F31,F32,F33,F44,F45,F46,F47") = 0 End If End Sub when Debugging, The highligted row is the "Rows("6:47").EntireRow.Hidden = False" when i delete that row, it goes to the next .entirerow.hidden = true/false code. this problem started when i copied my template sheet (which used this code) to make dupicates of the template. now none work. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Runtime Error 1004... help?
ya it was. but, it had worked before when the page was protected?
the only thing i did was copy the sheet to make a duplicate. also, now im getting a 'ambiguous name detected: Change' box popping up whenever i edit a cell, on any sheet, in any location. more help? "Gary Brown" wrote: The syntax is ok. Is your worksheet protected? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Derrick" wrote: error: Unable to set the Hidden Property of the Range Class code: under Worksheet, Change. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("D2,C6,D6,C7,D7,C8,D8,C9,D9,C14,F17,F18") = 0 Range("C25,F30,F31,F32,F33,F44,F45,F46,F47") = 0 If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("47:47").EntireRow.Hidden = False Range("C8,D8") = 0 Range("F10") = "=F6*F9" : Range("F47") = 0 ElseIf Range("B3") = "Standardized Module Height" Then Rows("6:6").EntireRow.Hidden = True : Rows("47:47").EntireRow.Hidden = True Range("C6,D6") = 0 Range("F10") = "=F7*F8" Range("F15") = "=IF((F8+SUMMARY!E6)=F9,F9,F8)" : Range("F47") = "=IF((F8+SUMMARY!E6=F9),2,1)" ElseIf Range("B3") = "Non-Modular Frame" Then Rows("6:6").EntireRow.Hidden = True Rows("8:8").EntireRow.Hidden = True Rows("10:10").EntireRow.Hidden = True Rows("15:47").EntireRow.Hidden = False Range("C6,D6") = 0 Range("C8,D8") = 0 Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("F17,F18,F30,F31,F32,F33,F44,F45,F46,F47") = 0 End If End Sub when Debugging, The highligted row is the "Rows("6:47").EntireRow.Hidden = False" when i delete that row, it goes to the next .entirerow.hidden = true/false code. this problem started when i copied my template sheet (which used this code) to make dupicates of the template. now none work. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Runtime Error 1004... help?
You have at least two of those worksheet_change procedures in the module behind
that worksheet. Your challenge will be to find both (or all) of them and either merge them together into one procedure--or delete the ones you don't want. ===== You can actually protect the worksheet in code so that your code can do stuff that the user's can't. (Look for UserInterfaceOnly in VBA's help if you need more info.) Maybe you were protecting that original worksheet using that option???? Derrick wrote: ya it was. but, it had worked before when the page was protected? the only thing i did was copy the sheet to make a duplicate. also, now im getting a 'ambiguous name detected: Change' box popping up whenever i edit a cell, on any sheet, in any location. more help? "Gary Brown" wrote: The syntax is ok. Is your worksheet protected? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Derrick" wrote: error: Unable to set the Hidden Property of the Range Class code: under Worksheet, Change. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("D2,C6,D6,C7,D7,C8,D8,C9,D9,C14,F17,F18") = 0 Range("C25,F30,F31,F32,F33,F44,F45,F46,F47") = 0 If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("47:47").EntireRow.Hidden = False Range("C8,D8") = 0 Range("F10") = "=F6*F9" : Range("F47") = 0 ElseIf Range("B3") = "Standardized Module Height" Then Rows("6:6").EntireRow.Hidden = True : Rows("47:47").EntireRow.Hidden = True Range("C6,D6") = 0 Range("F10") = "=F7*F8" Range("F15") = "=IF((F8+SUMMARY!E6)=F9,F9,F8)" : Range("F47") = "=IF((F8+SUMMARY!E6=F9),2,1)" ElseIf Range("B3") = "Non-Modular Frame" Then Rows("6:6").EntireRow.Hidden = True Rows("8:8").EntireRow.Hidden = True Rows("10:10").EntireRow.Hidden = True Rows("15:47").EntireRow.Hidden = False Range("C6,D6") = 0 Range("C8,D8") = 0 Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("F17,F18,F30,F31,F32,F33,F44,F45,F46,F47") = 0 End If End Sub when Debugging, The highligted row is the "Rows("6:47").EntireRow.Hidden = False" when i delete that row, it goes to the next .entirerow.hidden = true/false code. this problem started when i copied my template sheet (which used this code) to make dupicates of the template. now none work. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Runtime Error 1004... help?
Dave's answer was perfect. Hope he helped.
-- Sincerely, Gary Brown "Derrick" wrote: ya it was. but, it had worked before when the page was protected? the only thing i did was copy the sheet to make a duplicate. also, now im getting a 'ambiguous name detected: Change' box popping up whenever i edit a cell, on any sheet, in any location. more help? "Gary Brown" wrote: The syntax is ok. Is your worksheet protected? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Derrick" wrote: error: Unable to set the Hidden Property of the Range Class code: under Worksheet, Change. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("D2,C6,D6,C7,D7,C8,D8,C9,D9,C14,F17,F18") = 0 Range("C25,F30,F31,F32,F33,F44,F45,F46,F47") = 0 If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("47:47").EntireRow.Hidden = False Range("C8,D8") = 0 Range("F10") = "=F6*F9" : Range("F47") = 0 ElseIf Range("B3") = "Standardized Module Height" Then Rows("6:6").EntireRow.Hidden = True : Rows("47:47").EntireRow.Hidden = True Range("C6,D6") = 0 Range("F10") = "=F7*F8" Range("F15") = "=IF((F8+SUMMARY!E6)=F9,F9,F8)" : Range("F47") = "=IF((F8+SUMMARY!E6=F9),2,1)" ElseIf Range("B3") = "Non-Modular Frame" Then Rows("6:6").EntireRow.Hidden = True Rows("8:8").EntireRow.Hidden = True Rows("10:10").EntireRow.Hidden = True Rows("15:47").EntireRow.Hidden = False Range("C6,D6") = 0 Range("C8,D8") = 0 Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("F17,F18,F30,F31,F32,F33,F44,F45,F46,F47") = 0 End If End Sub when Debugging, The highligted row is the "Rows("6:47").EntireRow.Hidden = False" when i delete that row, it goes to the next .entirerow.hidden = true/false code. this problem started when i copied my template sheet (which used this code) to make dupicates of the template. now none work. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Runtime Error 1004... help?
thanks. does that mean i've made a typo or something in one module and it's
screwing with the rest? this morning i emailed the file to myself, so i could work on it later, and when i opened that file i didn't get any error messages. strange? "Dave Peterson" wrote: You have at least two of those worksheet_change procedures in the module behind that worksheet. Your challenge will be to find both (or all) of them and either merge them together into one procedure--or delete the ones you don't want. ===== You can actually protect the worksheet in code so that your code can do stuff that the user's can't. (Look for UserInterfaceOnly in VBA's help if you need more info.) Maybe you were protecting that original worksheet using that option???? Derrick wrote: ya it was. but, it had worked before when the page was protected? the only thing i did was copy the sheet to make a duplicate. also, now im getting a 'ambiguous name detected: Change' box popping up whenever i edit a cell, on any sheet, in any location. more help? "Gary Brown" wrote: The syntax is ok. Is your worksheet protected? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Derrick" wrote: error: Unable to set the Hidden Property of the Range Class code: under Worksheet, Change. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("D2,C6,D6,C7,D7,C8,D8,C9,D9,C14,F17,F18") = 0 Range("C25,F30,F31,F32,F33,F44,F45,F46,F47") = 0 If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("47:47").EntireRow.Hidden = False Range("C8,D8") = 0 Range("F10") = "=F6*F9" : Range("F47") = 0 ElseIf Range("B3") = "Standardized Module Height" Then Rows("6:6").EntireRow.Hidden = True : Rows("47:47").EntireRow.Hidden = True Range("C6,D6") = 0 Range("F10") = "=F7*F8" Range("F15") = "=IF((F8+SUMMARY!E6)=F9,F9,F8)" : Range("F47") = "=IF((F8+SUMMARY!E6=F9),2,1)" ElseIf Range("B3") = "Non-Modular Frame" Then Rows("6:6").EntireRow.Hidden = True Rows("8:8").EntireRow.Hidden = True Rows("10:10").EntireRow.Hidden = True Rows("15:47").EntireRow.Hidden = False Range("C6,D6") = 0 Range("C8,D8") = 0 Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("F17,F18,F30,F31,F32,F33,F44,F45,F46,F47") = 0 End If End Sub when Debugging, The highligted row is the "Rows("6:47").EntireRow.Hidden = False" when i delete that row, it goes to the next .entirerow.hidden = true/false code. this problem started when i copied my template sheet (which used this code) to make dupicates of the template. now none work. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro Runtime Error 1004... help?
If you emailed the same file and enabled macros (and didn't disable events),
then I don't have an explanation why this version worked ok. Are you sure you did each of those 3 things? Derrick wrote: thanks. does that mean i've made a typo or something in one module and it's screwing with the rest? this morning i emailed the file to myself, so i could work on it later, and when i opened that file i didn't get any error messages. strange? "Dave Peterson" wrote: You have at least two of those worksheet_change procedures in the module behind that worksheet. Your challenge will be to find both (or all) of them and either merge them together into one procedure--or delete the ones you don't want. ===== You can actually protect the worksheet in code so that your code can do stuff that the user's can't. (Look for UserInterfaceOnly in VBA's help if you need more info.) Maybe you were protecting that original worksheet using that option???? Derrick wrote: ya it was. but, it had worked before when the page was protected? the only thing i did was copy the sheet to make a duplicate. also, now im getting a 'ambiguous name detected: Change' box popping up whenever i edit a cell, on any sheet, in any location. more help? "Gary Brown" wrote: The syntax is ok. Is your worksheet protected? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Derrick" wrote: error: Unable to set the Hidden Property of the Range Class code: under Worksheet, Change. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub Rows("6:47").EntireRow.Hidden = False Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("D2,C6,D6,C7,D7,C8,D8,C9,D9,C14,F17,F18") = 0 Range("C25,F30,F31,F32,F33,F44,F45,F46,F47") = 0 If Range("B3") = "Standardized Module Width" Then Rows("6:6").EntireRow.Hidden = False : Rows("47:47").EntireRow.Hidden = False Range("C8,D8") = 0 Range("F10") = "=F6*F9" : Range("F47") = 0 ElseIf Range("B3") = "Standardized Module Height" Then Rows("6:6").EntireRow.Hidden = True : Rows("47:47").EntireRow.Hidden = True Range("C6,D6") = 0 Range("F10") = "=F7*F8" Range("F15") = "=IF((F8+SUMMARY!E6)=F9,F9,F8)" : Range("F47") = "=IF((F8+SUMMARY!E6=F9),2,1)" ElseIf Range("B3") = "Non-Modular Frame" Then Rows("6:6").EntireRow.Hidden = True Rows("8:8").EntireRow.Hidden = True Rows("10:10").EntireRow.Hidden = True Rows("15:47").EntireRow.Hidden = False Range("C6,D6") = 0 Range("C8,D8") = 0 Range("F10") = "=F7*F9" Range("F15") = "=F9-F30-F44" Range("F16") = "=F7-F31-F45" Range("F17,F18,F30,F31,F32,F33,F44,F45,F46,F47") = 0 End If End Sub when Debugging, The highligted row is the "Rows("6:47").EntireRow.Hidden = False" when i delete that row, it goes to the next .entirerow.hidden = true/false code. this problem started when i copied my template sheet (which used this code) to make dupicates of the template. now none work. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro runtime 1004 error on opening worksheet | Excel Discussion (Misc queries) | |||
| unable to protect cells in macro sheet b/c runtime error 1004 | Excel Worksheet Functions | |||
| Macro runtime error 1004 with Autofilter | Excel Discussion (Misc queries) | |||
| runtime error '1004' | Excel Discussion (Misc queries) | |||
| Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Linear Mode
