HELP!!!! "The Item with the specified name wasn't found" SERIOUSLY, PLEASE!

seventy9mph · May 12th, 2009, 01:27 PM

here is the deal...Here is my code and I am sorry for it being really long, but i am in desperate need of help! I hope that I still get help even though it is lots of code

In my worksheet I have 11 option buttons and 1 'regular' button. The option buttons populate a cell with a given value (CODE IS BELOW) which in turn tons of calculations are based off of. The 1 'regular' button simply copies the current WS (CODE IS BELOW).

PROBLEM : Everything works great on the original WS, but when it is copied, and the option buttons are selected (on the copied WS), I get this error

RUN TIME ERROR '-2147024809 (80070057)':
THE ITEM WITH THE SPECIFIED NAME WASN'T FOUND

Below is a list of option buttons and numbers. In my code you will find RED NUMBERS. This means that when that option button is selected (errors occur only in copied WS) the error comes up in the line denoted with the in the code with the corresponding number.

1) When Option Button 3000 is selected
2) When Option Button 3001 is selected
3) When Option Button 3002 is selected
4) When Option Button 5158 is selected
5) When Option Button 5159 is selected
6) When Option Button 5160 is selected
7) When Option Button 6314 is selected
8) When Option Button 6315 is selected
9) When Option Button 6316 is selected

Here is my Worksheet Code:
Most of this is very simple cell formating based on option button populated cells.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

'THIS PART HANDLES THE AUTO FILL FROM PREVIOUS INPUTS

'THIS AUTO FILLS #2 (COLUMNS F:G)

If Range("$I$2") = "VC" Then
If Range("$I$3") = "VC" Then
If Target.Count > 1 Then Exit Sub
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False '<-- Stops the code re-triggering
    Select Case Target.Address
        Case "$B$4", "$F$4", "$B$6", "$B$7", "$B$8"
            If Range("$F$4") > Range("$B$4") Then
                Range("$F$7") = Range("$B$8")
                Range("$F$8") = ""     'may/maynot like this line
                Range("$F$7").Locked = True
                Range("$F$8").Locked = False
            Else
                Range("$F$8") = Range("$B$7")
                Range("$F$7") = ""     'may/maynot like this line
                Range("$F$8").Locked = True
                Range("$F$7").Locked = False
            End If
        Case Else:
    End Select
    Application.EnableEvents = True '<-- Turn 'Events' back on
    ActiveSheet.Protect ("Password")
ElseIf Range("$I$3") = "GB" Then
If Target.Count > 1 Then Exit Sub
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False '<-- Stops the code re-triggering
    Select Case Target.Address
        Case "$B$4", "$F$4", "$B$6"
            If Range("$F$4") > Range("$B$4") Then
                Range("$F$7") = Range("$B$6")
                Range("$F$8") = ""     'may/maynot like this line
                Range("$F$7").Locked = True
                Range("$F$8").Locked = False
                Range("B6").Select
            Else
                Range("$F$8") = Range("$B$6")
                Range("$F$7") = ""     'may/maynot like this line
                Range("$F$8").Locked = True
                Range("$F$7").Locked = False
                Range("B6").Select
            End If
        Case Else:
    End Select
    Application.EnableEvents = True '<-- Turn 'Events' back on
    ActiveSheet.Protect ("Password")
End If
End If

'THIS AUTO FILLS #3 (COLUMNS M:N)
            
If Range("$P$2") = "VC" Then
If Range("$I$2") = "VC" Then
If Target.Count > 1 Then Exit Sub
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False '<-- Stops the code re-triggering
    Select Case Target.Address
        Case "$F$4", "$M$4", "$F$6", "$F$7", "$F$8"
            If Range("$M$4") > Range("$F$4") Then
                Range("$M$7") = Range("$F$8")
                Range("$M$8") = ""     'may/maynot like this line
                Range("$M$7").Locked = True
                Range("$M$8").Locked = False
            Else
                Range("$M$8") = Range("$F$7")
                Range("$M$7") = ""     'may/maynot like this line
                Range("$M$8").Locked = True
                Range("$M$7").Locked = False
            End If
        Case Else:
    End Select
    Application.EnableEvents = True '<-- Turn 'Events' back on
    ActiveSheet.Protect ("Password")
ElseIf Range("$I$2") = "GB" Then
If Target.Count > 1 Then Exit Sub
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False '<-- Stops the code re-triggering
    Select Case Target.Address
        Case "$F$4", "$M$4", "$F$6"
            If Range("$M$4") > Range("$F$4") Then
                Range("$M$7") = Range("$F$6")
                Range("$M$8") = ""     'may/maynot like this line
                Range("$M$7").Locked = True
                Range("$M$8").Locked = False
                Range("$f$6").Locked = False
                Range("F6").Select
            Else
                Range("$M$8") = Range("$F$6")
                Range("$M$7") = ""     'may/maynot like this line
                Range("$M$8").Locked = True
                Range("$M$7").Locked = False
                Range("$f$6").Locked = False
                Range("F6").Select
            End If
        Case Else:
    End Select
    Application.EnableEvents = True '<-- Turn 'Events' back on
    ActiveSheet.Protect ("Password")
End If
End If
     
'THIS AUTO FILLS #4 (COLUMNS T:U)
            
If Range("$W$2") = "VC" Then
If Range("$P$2") = "VC" Then
If Target.Count > 1 Then Exit Sub
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False '<-- Stops the code re-triggering
    Select Case Target.Address
        Case "$M$4", "$T$4", "$M$6", "$M$7", "$M$8"
            If Range("$T$4") > Range("$M$4") Then
                Range("$T$7") = Range("$M$8")
                Range("$T$8") = ""     'may/maynot like this line
                Range("$T$7").Locked = True
                Range("$T$8").Locked = False
            Else
                Range("$T$8") = Range("$M$7")
                Range("$T$7") = ""     'may/maynot like this line
                Range("$T$8").Locked = True
                Range("$T$7").Locked = False
            End If
        Case Else:
    End Select
    Application.EnableEvents = True '<-- Turn 'Events' back on
    ActiveSheet.Protect ("Password")
ElseIf Range("$P$2") = "GB" Then
If Target.Count > 1 Then Exit Sub
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False '<-- Stops the code re-triggering
    Select Case Target.Address
        Case "$M$4", "$T$4", "$M$6"
            If Range("$T$4") > Range("$M$4") Then
                Range("$T$7") = Range("$M$6")
                Range("$T$8") = ""     'may/maynot like this line
                Range("$T$7").Locked = True
                Range("$T$8").Locked = False
                Range("$m$6").Locked = False
                Range("m6").Select
            Else
                Range("$T$8") = Range("$M$6")
                Range("$T$7") = ""     'may/maynot like this line
                Range("$T$8").Locked = True
                Range("$T$7").Locked = False
                Range("$m$6").Locked = False
                Range("m6").Select
            End If
        Case Else:
    End Select
    Application.EnableEvents = True '<-- Turn 'Events' back on
    ActiveSheet.Protect ("Password")
End If
End If


'HERE DOWN HANDLES THE WHICH CELLS TO LOCK, FORMAT AND CLEAR AND WHEN TO DO IT


If Target.Address = "$I$3" Then
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False
    Select Case Target.Value
      Case "GB"
                If Range("$I$2") = "VC" Then
                    Range("C4:C8").Locked = True
                    Range("B5").Locked = False
                    Range("B6").Locked = False
                    Range("B6").Select
                ElseIf Range("$I$2") = "GB" Then
                    Range("C4:C8").Locked = True
                    Range("B5").Locked = False
                    Range("B6").Locked = False
                    Range("B6").Select
                End If
            Call UNMERGE1
            Range("A12:C14").Locked = True
            Range("B6:B8").ClearContents
            Range("B4").Select
      Case "VC"
            Call MERGE1
            Range("B6").Locked = False
            Range("B6").ClearContents
            Range("A12:C14").Locked = True
            Range("B4").Select
    End Select
    Application.EnableEvents = True
    ActiveSheet.Protect ("Password")
  End If
If Target.Address = "$I$2" Then
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False
    Select Case Target.Value
      Case "GB"
                If Range("$P$2") = "VC" Then
                    Range("G4:G8").Locked = True
                    Range("F5").Locked = True
                    Range("F6").Locked = False
                ElseIf Range("$P$2") = "GB" Then
                    Range("G4:G8").Locked = True
                    Range("F5").Locked = True
                    Range("F6").Locked = False
                ElseIf Range("$P$2") = "DONE" Then
                    Range("G4:G8").Locked = True
                    Range("F5").Locked = True: 'F6
                End If
            Call UNMERGE2
1)            Call Visible3
            Range("E12:G14").Locked = True
            Range("F6:F8").ClearContents
            Range("F4").ClearContents
            Range("F6").ClearContents
            Range("F4").Select
      Case "VC"
            Call MERGE2
            Call Visible3
            Range("F6").Locked = False
            Range("E12:G14").Locked = True
            Range("F4").ClearContents
            Range("F6").ClearContents
            Range("F4").Select
      Case "DONE"
            Call UNMERGE2
            Call HIDE3
            Range("F4").ClearContents
            Range("F6:F8").ClearContents
            Range("E12:G14").Locked = True
            ActiveSheet.Shapes("Button 6779").Visible = False
    End Select
    Application.EnableEvents = True
    ActiveSheet.Protect ("Password")
  End If
If Target.Address = "$P$2" Then
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False
    Select Case Target.Value
      Case "GB"
                If Range("$W$2") = "VC" Then
                    Range("N4:N8").Locked = True
                    Range("M5").Locked = True
                    Range("M6").Locked = False
                ElseIf Range("$W$2") = "GB" Then
                    Range("N4:N8").Locked = True
                    Range("M5").Locked = True
                    Range("M6").Locked = False
                ElseIf Range("$W$2") = "DONE" Then
                    Range("N4:N8").Locked = True
                    Range("M5:M6").Locked = True
                End If
            Call UNMERGE3
4)             Call Visible4
            Range("L12:N14").Locked = True
            Range("M6:M8").ClearContents
            Range("M4").ClearContents
            Range("M6").ClearContents
            Range("M4").Select
      Case "VC"
            Call MERGE3
5)            Call Visible4
            Range("M6").Locked = False
            Range("L12:N14").Locked = True
            Range("M4").ClearContents
            Range("M6").ClearContents
            Range("M4").Select
      Case "DONE"
            Call UNMERGE3
6)            Call HIDE4
            Range("M4").ClearContents
            Range("M6:M8").ClearContents
            Range("L12:N14").Locked = True
            ActiveSheet.Shapes("Button 6779").Visible = False
    End Select
    Application.EnableEvents = True
    ActiveSheet.Protect ("Password")
  End If
If Target.Address = "$W$2" Then
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False
    Select Case Target.Value
      Case "GB"
                If Range("$AD$2") = "VC" Then
                    Range("U4:U8").Locked = True
                    Range("T5").Locked = True
                    Range("T6").Locked = False
                ElseIf Range("$AD$2") = "GB" Then
                    Range("U4:U8").Locked = True
                    Range("T5").Locked = True
                    Range("T6").Locked = False
                ElseIf Range("$AD$2") = "DONE" Then
                    Range("U4:U8").Locked = True
                    Range("T5:T6").Locked = True
                End If
            Call UNMERGE4
            Range("S12:U14").Locked = True
            Range("T6:T8").ClearContents
            Range("T4").ClearContents
            Range("T6").ClearContents
            Range("T4").Select
7)             ActiveSheet.Shapes("Button 6779").Visible = True
      Case "VC"
            Call MERGE4
            Range("T6").Locked = False
            Range("S12:U14").Locked = True
            Range("T4").ClearContents
            Range("T6").ClearContents
            Range("T4").Select
8)             ActiveSheet.Shapes("Button 6779").Visible = True
      Case "DONE"
            Call UNMERGE4
            Range("T4").ClearContents
            Range("T6:T8").ClearContents
            Range("S12:U14").Locked = True
9)            ActiveSheet.Shapes("Button 6779").Visible = False
    End Select
    Application.EnableEvents = True
    ActiveSheet.Protect ("Password")
  End If
End Sub

Here is my Option Button Code (in a Module)

Code:

Sub OptionButton3000_Click()
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "GB"
    Range("F4").Select
End Sub
Sub OptionButton3001_Click()
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "VC"
    Range("F4").Select
End Sub
Sub OptionButton3002_Click()
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "DONE"
    Range("B4").Select
End Sub
Sub OptionButton5158_Click()
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "GB"
    Range("P3").Select
End Sub
Sub OptionButton5159_Click()
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "VC"
    Range("P3").Select
End Sub
Sub OptionButton5160_Click()
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "DONE"
    Range("P3").Select
End Sub
Sub OptionButton6145_Click()
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "VC"
    Range("B4").Select
End Sub
Sub OptionButton6800_Click()
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "done"
    Range("B4").Select
End Sub
Sub OptionButton6146_Click()
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "GB"
    Range("B4").Select
End Sub
Sub OptionButton6314_Click()
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "GB"
    Range("T4").Select
End Sub
Sub OptionButton6315_Click()
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "VC"
    Range("T4").Select
End Sub
Sub OptionButton6316_Click()
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "DONE"
    Range("T4").Select
End Sub

Here is my Regular Button code (in a Module)
It just simply copies the current WS and opens the new one

Code:

Sub Button6779_Click()
    ActiveSheet.Unprotect ("Password")
    Application.EnableEvents = False
    
    Dim NewSheetName As String
    NewSheetName = ""
    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
    NewSheetName = InputBox("Type in the new worksheet name.")
    If NewSheetName <> "" Then
      ActiveSheet.Name = NewSheetName
    End If
    Range("$F$6:$F$8").Locked = False
    Range("$M$6:$M$8").Locked = False
    Range("$M$6:$M$8").Locked = False
    ActiveSheet.Range("T4:T8").Select
    Selection.Copy
    ActiveSheet.Range("B4:B8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("$F$4").ClearContents
    Range("$F$6:$F$8").ClearContents
    Range("$M$4").ClearContents
    Range("$M$6:$M$8").ClearContents
    Range("$T$4").ClearContents
    Range("$T$6:$T$8").ClearContents
    
    Application.EnableEvents = True
    ActiveSheet.Protect ("Password")
End Sub

Here is more SubRoutine Code from another Module
This code is called upon from my WS code

Code:

Sub Visible3()
    Columns("L:O").Select
    Selection.EntireColumn.Hidden = False                       'THIS SHOWS THE COLUMNS FOR NEXT INPUT
2)     ActiveSheet.Shapes("Option Button 5158").Visible = True
    ActiveSheet.Shapes("Option Button 5159").Visible = True
    ActiveSheet.Shapes("Option Button 5160").Visible = True
    ActiveSheet.GroupBoxes("group box 5175").Visible = True     'THIS SHOWS THE NEXT BOXES AND OPTION BUTTONS
End Sub
Sub HIDE3()
            Columns("I:R").Select
            Selection.EntireColumn.Hidden = True
3)            ActiveSheet.Shapes("Option Button 5158").Visible = False
            ActiveSheet.Shapes("Option Button 5159").Visible = False
            ActiveSheet.Shapes("Option Button 5160").Visible = False
            ActiveSheet.GroupBoxes("group box 5175").Visible = False
End Sub
Sub Visible4()
    Columns("S:V").Select
    Selection.EntireColumn.Hidden = False                       'THIS SHOWS THE COLUMNS FOR NEXT INPUT
    ActiveSheet.Shapes("Option Button 6314").Visible = True
    ActiveSheet.Shapes("Option Button 6315").Visible = True
    ActiveSheet.Shapes("Option Button 6316").Visible = True
    ActiveSheet.GroupBoxes("group box 6313").Visible = True     'THIS SHOWS THE NEXT BOXES AND OPTION BUTTONS
End Sub
Sub HIDE4()
            Columns("P:Y").Select
            Selection.EntireColumn.Hidden = True
            ActiveSheet.Shapes("Option Button 6314").Visible = False
            ActiveSheet.Shapes("Option Button 6315").Visible = False
            ActiveSheet.Shapes("Option Button 6316").Visible = False
            ActiveSheet.GroupBoxes("group box 6313").Visible = False
End Sub
Sub UNMERGE1()
            Range("C12:C14").Select
                With Selection.Interior
                    .Pattern = xlNone
                End With
                With Selection.Borders
                    .LineStyle = xlNone
                    .ColorIndex = xlNone
                End With
            Selection.UnMerge
            Range("A12:B14").Select
                With Selection.Interior
                    .Pattern = xlNone
                End With
                With Selection.Borders
                    .LineStyle = xlNone
                    .ColorIndex = xlNone
                End With
            Selection.UnMerge
End Sub
Sub MERGE1()
            Range("C12:C14").Select
                With Selection.Borders
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
            Selection.Interior.ColorIndex = 34
            Selection.Merge
            Range("A12:B14").Select
                With Selection.Borders
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                With Selection.Interior
                    .Pattern = xlNone
                End With
            Selection.Merge
End Sub
Sub UNMERGE2()
            Range("G12:G14").Select
                With Selection.Interior
                    .Pattern = xlNone
                End With
                With Selection.Borders
                    .LineStyle = xlNone
                    .ColorIndex = xlNone
                End With
            Selection.UnMerge
            Range("E12:F14").Select
                With Selection.Interior
                    .Pattern = xlNone
                End With
                With Selection.Borders
                    .LineStyle = xlNone
                    .ColorIndex = xlNone
                End With
            Selection.UnMerge
End Sub
Sub MERGE2()
            Range("G12:G14").Select
                With Selection.Borders
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
            Selection.Interior.ColorIndex = 34
            Selection.Merge
            Range("E12:F14").Select
                With Selection.Borders
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                With Selection.Interior
                    .Pattern = xlNone
                End With
            Selection.Merge
End Sub
Sub UNMERGE3()
            Range("N12:N14").Select
                With Selection.Interior
                    .Pattern = xlNone
                End With
                With Selection.Borders
                    .LineStyle = xlNone
                    .ColorIndex = xlNone
                End With
            Selection.UnMerge
            Range("L12:M14").Select
                With Selection.Interior
                    .Pattern = xlNone
                End With
                With Selection.Borders
                    .LineStyle = xlNone
                    .ColorIndex = xlNone
                End With
            Selection.UnMerge
End Sub
Sub MERGE3()
            Range("N12:N14").Select
                With Selection.Borders
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
            Selection.Interior.ColorIndex = 34
            Selection.Merge
            Range("L12:M14").Select
                With Selection.Borders
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                With Selection.Interior
                    .Pattern = xlNone
                End With
            Selection.Merge
End Sub
Sub UNMERGE4()
            Range("U12:U14").Select
                With Selection.Interior
                    .Pattern = xlNone
                End With
                With Selection.Borders
                    .LineStyle = xlNone
                    .ColorIndex = xlNone
                End With
            Selection.UnMerge
            Range("S12:T14").Select
                With Selection.Interior
                    .Pattern = xlNone
                End With
                With Selection.Borders
                    .LineStyle = xlNone
                    .ColorIndex = xlNone
                End With
            Selection.UnMerge
End Sub
Sub MERGE4()
            Range("U12:U14").Select
                With Selection.Borders
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
            Selection.Interior.ColorIndex = 34
            Selection.Merge
            Range("S12:T14").Select
                With Selection.Borders
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                With Selection.Interior
                    .Pattern = xlNone
                End With
            Selection.Merge
End Sub

gbianchi · May 12th, 2009, 01:42 PM

Too much code make me dizzy.

A couple of things you could try out. First, point out which line throws the error.

Second, when you copy (I donÂ´t know what you copy), every button (or option) still has the same name???

And then, after you try this out, only put the relevant part of the code and maybe we can help you better.

seventy9mph · May 12th, 2009, 01:59 PM

thanks gbianchi for your reply
I know it is lots of code, sorry for that.
in the code, I have RED numbers that represent the line of code that threw the error to the corresponding option button at top of page before all the code.

yes, when I copy, it copies the entire worksheet and makes another one with a user defined name. The option button names are the same as they are on the "original" WS. I don't know how to or if they can be changed for each copied worksheet so they would be unique.

gbianchi · May 12th, 2009, 02:05 PM

so, this:

ActiveSheet.Shapes("Option Button 5158").Visible = False

Doesn't exists. The option button is not copied or has his name changed.. if you comment this line.. the next fails??

seventy9mph · May 12th, 2009, 02:24 PM

ya, it looks like that is a problem,
Yes, I commented that line and the next line failed.

gbianchi · May 12th, 2009, 06:20 PM

So all the controls name are changed (and that sound perfectly logic).

So, what to do know is up to you... Maybe there is a way to know which controls are sitting on a worksheet (never worked with that)..