ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro Runtime Error 1004... help? (https://www.excelbanter.com/excel-discussion-misc-queries/234750-macro-runtime-error-1004-help.html)

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



Gary Brown[_5_]
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.



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



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

Gary Brown[_5_]
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.



Derrick
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

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


All times are GMT +1. The time now is 03:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com