Macro Runtime Error 1004... help?

D

Derrick

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

Derrick

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?
 
D

Dave Peterson

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????
 
D

Derrick

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?
 
D

Dave Peterson

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top