Private Sub Worksheet_Calculate() is not private?

S

spacely

Board Regular
Joined
Oct 26, 2007
Messages
248
Hi,

I have a workbook/sheet that has a callback on Calculation associated with it:

Private Sub Worksheet_Calculate()

But whenever it is opened in the same workBOOK as anothe excel application, changes in the other excel sheets/book actually trigger this callback. Why is that?

Baffled,
Dave
 
Sort by date Sort by votes
same answer. the event is triggered by the container worksheet calculating, which in turn is being caused by the other workbook. I suspect you have volatile functions but, if your code is written properly, that should not cause errors.
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
I'm really lost. Would you mind me posting the whole sub routine or even send you the workbook to look at? If not, thanks for the info and I'll try and hunt down the problem. It's been bugging me for awhile now.
 
Upvote 0
Post the code and we'll start there. :)
 
Upvote 0
I know this a lot for you to go through, so please don't feel obliged to check it. The majority of my coding is based around the IF command. All stated ranges all refer to the worksheet they are contained on. I just don't understand why this should be called upon when the Worksheet_Calculate in the other workbook is clear.

Code: 
Private Sub Worksheet_Calculate()
Worksheets("EWTC").Unprotect Password:=Worksheets("EWTC").Range("A1")
             
If Worksheets("EWTC").Range("O10").Value = 1 Then
    If Worksheets("EWTC").Range("J10").Value < 1 Then
        OptionButton1.BackColor = &H80&
    Else
        OptionButton1.BackColor = Worksheets("EWTC").Range("M10").Interior.Color
    End If
Else
    OptionButton1.BackColor = Worksheets("EWTC").Range("M10").Interior.Color
End If
If Worksheets("EWTC").Range("O11").Value = 1 Then
    If Worksheets("EWTC").Range("J11").Value < 1 Then
        OptionButton2.BackColor = &H80&
    Else
        OptionButton2.BackColor = Worksheets("EWTC").Range("M11").Interior.Color
    End If
Else
    OptionButton2.BackColor = Worksheets("EWTC").Range("M11").Interior.Color
End If
If Worksheets("EWTC").Range("O12").Value = 1 Then
    If Worksheets("EWTC").Range("J12").Value < 1 Then
        OptionButton3.BackColor = &H80&
    Else
        OptionButton3.BackColor = Worksheets("EWTC").Range("M12").Interior.Color
    End If
Else
    OptionButton3.BackColor = Worksheets("EWTC").Range("M12").Interior.Color
End If
If Worksheets("EWTC").Range("O13").Value = 1 Then
    If Worksheets("EWTC").Range("J13").Value < 1 Then
        OptionButton4.BackColor = &H80&
    Else
        OptionButton4.BackColor = Worksheets("EWTC").Range("M13").Interior.Color
    End If
Else
    OptionButton4.BackColor = Worksheets("EWTC").Range("M13").Interior.Color
End If
If Worksheets("EWTC").Range("O14").Value = 1 Then
    If Worksheets("EWTC").Range("J14").Value < 1 Then
        OptionButton5.BackColor = &H80&
    Else
        OptionButton5.BackColor = Worksheets("EWTC").Range("M14").Interior.Color
    End If
Else
    OptionButton5.BackColor = Worksheets("EWTC").Range("M14").Interior.Color
End If
If Worksheets("EWTC").Range("O15").Value = 1 Then
    If Worksheets("EWTC").Range("J15").Value < 1 Then
        OptionButton6.BackColor = &H80&
    Else
        OptionButton6.BackColor = Worksheets("EWTC").Range("M15").Interior.Color
    End If
Else
    OptionButton6.BackColor = Worksheets("EWTC").Range("M15").Interior.Color
End If
If Worksheets("EWTC").Range("O16").Value = 1 Then
    If Worksheets("EWTC").Range("J16").Value < 1 Then
        OptionButton7.BackColor = &H80&
    Else
        OptionButton7.BackColor = Worksheets("EWTC").Range("M16").Interior.Color
    End If
Else
    OptionButton7.BackColor = Worksheets("EWTC").Range("M16").Interior.Color
End If
If Worksheets("EWTC").Range("O17").Value = 1 Then
    If Worksheets("EWTC").Range("J17").Value < 1 Then
        OptionButton8.BackColor = &H80&
    Else
        OptionButton8.BackColor = Worksheets("EWTC").Range("M17").Interior.Color
    End If
Else
    OptionButton8.BackColor = Worksheets("EWTC").Range("M17").Interior.Color
End If
If Worksheets("EWTC").Range("O18").Value = 1 Then
    If Worksheets("EWTC").Range("J18").Value < 1 Then
        OptionButton9.BackColor = &H80&
    Else
        OptionButton9.BackColor = Worksheets("EWTC").Range("M18").Interior.Color
    End If
Else
    OptionButton9.BackColor = Worksheets("EWTC").Range("M18").Interior.Color
End If
If Worksheets("EWTC").Range("O19").Value = 1 Then
    If Worksheets("EWTC").Range("J19").Value < 1 Then
        OptionButton10.BackColor = &H80&
    Else
        OptionButton10.BackColor = Worksheets("EWTC").Range("M19").Interior.Color
    End If
Else
    OptionButton10.BackColor = Worksheets("EWTC").Range("M19").Interior.Color
End If
             
If Worksheets("EWTC").Range("L7").Value = 1 Then
    GoTo EndLine
Else
    If Worksheets("EWTC").Range("B10") = 0 Then
        Worksheets("EWTC").Range("B11:C11").Locked = True
        Worksheets("EWTC").Range("E11").Locked = True
        Worksheets("EWTC").Range("H11").Locked = True
        If Worksheets("EWTC").Range("C10") = 0 Then
            If Worksheets("EWTC").Range("E10") = 0 Then
                If Worksheets("EWTC").Range("H10") = 0 Then
                    Worksheets("EWTC").Range("B11:C11").Locked = True
                    Worksheets("EWTC").Range("E11").Locked = True
                    Worksheets("EWTC").Range("H11").Locked = True
                    OptionButton1.Visible = False
                    OptionButton1.Value = False
                    Worksheets("EWTC").Range("J10:M10").Interior.Color = Worksheets("EWTC").Range("B10").Interior.Color
                    With Worksheets("EWTC").Range("B10:M10").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B10:M10").Font.Bold = False
                    Worksheets("EWTC").Range("B10:M10").Font.Size = 10
                    OptionButton1.BackColor = Worksheets("EWTC").Range("B10").Interior.Color
                    Rows("10:10").RowHeight = 14
                    CommandButton3.Visible = False
                Else
                    OptionButton1.Visible = True
                    CommandButton3.Visible = True
                End If
            Else:
                OptionButton1.Visible = True
                CommandButton3.Visible = True
            End If
        Else:
            OptionButton1.Visible = True
            CommandButton3.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B11:C11").Locked = False
        Worksheets("EWTC").Range("E11").Locked = False
        Worksheets("EWTC").Range("H11").Locked = False
        OptionButton1.Visible = True
        CommandButton3.Visible = True
    End If
    
    If Worksheets("EWTC").Range("B11") = 0 Then
        Worksheets("EWTC").Range("B12:C12").Locked = True
        Worksheets("EWTC").Range("E12").Locked = True
        Worksheets("EWTC").Range("H12").Locked = True
        If Worksheets("EWTC").Range("C11") = 0 Then
            If Worksheets("EWTC").Range("E11") = 0 Then
                If Worksheets("EWTC").Range("H11") = 0 Then
                    Worksheets("EWTC").Range("B12:C12").Locked = True
                    Worksheets("EWTC").Range("E12").Locked = True
                    Worksheets("EWTC").Range("H12").Locked = True
                    OptionButton2.Visible = False
                    OptionButton2.Value = False
                    If OptionButton1.Value = False Then
                        With Worksheets("EWTC").Range("B11:M11").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    Worksheets("EWTC").Range("J11:M11").Interior.Color = Worksheets("EWTC").Range("B11").Interior.Color
                    With Worksheets("EWTC").Range("B11:M11").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B11:M11").Font.Bold = False
                    Worksheets("EWTC").Range("B11:M11").Font.Size = 10
                    OptionButton2.BackColor = Worksheets("EWTC").Range("B11").Interior.Color
                    Rows("11:11").RowHeight = 14
                    CommandButton4.Visible = False
                Else
                    OptionButton2.Visible = True
                    CommandButton4.Visible = True
                End If
            Else:
                OptionButton2.Visible = True
                CommandButton4.Visible = True
            End If
        Else:
            OptionButton2.Visible = True
            CommandButton4.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B12:C12").Locked = False
        Worksheets("EWTC").Range("E12").Locked = False
        Worksheets("EWTC").Range("H12").Locked = False
        OptionButton2.Visible = True
        CommandButton4.Visible = True
    End If
    
    If Worksheets("EWTC").Range("B12") = 0 Then
        Worksheets("EWTC").Range("B13:C13").Locked = True
        Worksheets("EWTC").Range("E13").Locked = True
        Worksheets("EWTC").Range("H13").Locked = True
        If Worksheets("EWTC").Range("C12") = 0 Then
            If Worksheets("EWTC").Range("E12") = 0 Then
                If Worksheets("EWTC").Range("H12") = 0 Then
                    Worksheets("EWTC").Range("B13:C13").Locked = True
                    Worksheets("EWTC").Range("E13").Locked = True
                    Worksheets("EWTC").Range("H13").Locked = True
                    OptionButton3.Visible = False
                    OptionButton3.Value = False
                    Worksheets("EWTC").Range("J12:M12").Interior.Color = Worksheets("EWTC").Range("B12").Interior.Color
                    If OptionButton2.Value = False Then
                        With Worksheets("EWTC").Range("B12:M12").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    With Worksheets("EWTC").Range("B12:M12").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B12:M12").Font.Bold = False
                    Worksheets("EWTC").Range("B12:M12").Font.Size = 10
                    OptionButton3.BackColor = Worksheets("EWTC").Range("B12").Interior.Color
                    Rows("12:12").RowHeight = 14
                    CommandButton5.Visible = False
                Else
                    OptionButton3.Visible = True
                    CommandButton5.Visible = True
                End If
            Else:
                OptionButton3.Visible = True
                CommandButton5.Visible = True
            End If
        Else:
            OptionButton3.Visible = True
            CommandButton5.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B13:C13").Locked = False
        Worksheets("EWTC").Range("E13").Locked = False
        Worksheets("EWTC").Range("H13").Locked = False
        OptionButton3.Visible = True
        CommandButton5.Visible = True
    End If
    
    If Worksheets("EWTC").Range("B13") = 0 Then
        Worksheets("EWTC").Range("B14:C14").Locked = True
        Worksheets("EWTC").Range("E14").Locked = True
        Worksheets("EWTC").Range("H14").Locked = True
        If Worksheets("EWTC").Range("C13") = 0 Then
            If Worksheets("EWTC").Range("E13") = 0 Then
                If Worksheets("EWTC").Range("H13") = 0 Then
                    Worksheets("EWTC").Range("B14:C14").Locked = True
                    Worksheets("EWTC").Range("E14").Locked = True
                    Worksheets("EWTC").Range("H14").Locked = True
                    OptionButton4.Visible = False
                    OptionButton4.Value = False
                    Worksheets("EWTC").Range("J13:M13").Interior.Color = Worksheets("EWTC").Range("B13").Interior.Color
                    If OptionButton3.Value = False Then
                        With Worksheets("EWTC").Range("B13:M13").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    With Worksheets("EWTC").Range("B13:M13").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B13:M13").Font.Bold = False
                    Worksheets("EWTC").Range("B13:M13").Font.Size = 10
                    OptionButton4.BackColor = Worksheets("EWTC").Range("B13").Interior.Color
                    Rows("13:13").RowHeight = 14
                    CommandButton6.Visible = False
                Else
                    OptionButton4.Visible = True
                    CommandButton6.Visible = True
                End If
            Else:
                OptionButton4.Visible = True
                CommandButton6.Visible = True
            End If
        Else:
            OptionButton4.Visible = True
            CommandButton6.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B14:C14").Locked = False
        Worksheets("EWTC").Range("E14").Locked = False
        Worksheets("EWTC").Range("H14").Locked = False
        OptionButton4.Visible = True
        CommandButton6.Visible = True
    End If
    If Worksheets("EWTC").Range("B14") = 0 Then
        Worksheets("EWTC").Range("B15:C15").Locked = True
        Worksheets("EWTC").Range("E15").Locked = True
        Worksheets("EWTC").Range("H15").Locked = True
        If Worksheets("EWTC").Range("C14") = 0 Then
            If Worksheets("EWTC").Range("E14") = 0 Then
                If Worksheets("EWTC").Range("H14") = 0 Then
                    Worksheets("EWTC").Range("B15:C15").Locked = True
                    Worksheets("EWTC").Range("E15").Locked = True
                    Worksheets("EWTC").Range("H15").Locked = True
                    OptionButton5.Visible = False
                    OptionButton5.Value = False
                    Worksheets("EWTC").Range("J14:M14").Interior.Color = Worksheets("EWTC").Range("B14").Interior.Color
                    If OptionButton4.Value = False Then
                        With Worksheets("EWTC").Range("B14:M14").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    With Worksheets("EWTC").Range("B14:M14").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B14:M14").Font.Bold = False
                    Worksheets("EWTC").Range("B14:M14").Font.Size = 10
                    OptionButton5.BackColor = Worksheets("EWTC").Range("B14").Interior.Color
                    Rows("14:14").RowHeight = 14
                    CommandButton7.Visible = False
                Else
                    OptionButton5.Visible = True
                    CommandButton7.Visible = True
                End If
            Else:
                OptionButton5.Visible = True
                CommandButton7.Visible = True
            End If
        Else:
            OptionButton5.Visible = True
            CommandButton7.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B15:C15").Locked = False
        Worksheets("EWTC").Range("E15").Locked = False
        Worksheets("EWTC").Range("H15").Locked = False
        OptionButton5.Visible = True
        CommandButton7.Visible = True
    End If
    If Worksheets("EWTC").Range("B15") = 0 Then
        Worksheets("EWTC").Range("B16:C16").Locked = True
        Worksheets("EWTC").Range("E16").Locked = True
        Worksheets("EWTC").Range("H16").Locked = True
        If Worksheets("EWTC").Range("C15") = 0 Then
            If Worksheets("EWTC").Range("E15") = 0 Then
                If Worksheets("EWTC").Range("H15") = 0 Then
                    Worksheets("EWTC").Range("B16:C16").Locked = True
                    Worksheets("EWTC").Range("E16").Locked = True
                    Worksheets("EWTC").Range("H16").Locked = True
                    OptionButton6.Visible = False
                    OptionButton6.Value = False
                    Worksheets("EWTC").Range("J15:M15").Interior.Color = Worksheets("EWTC").Range("B15").Interior.Color
                    If OptionButton5.Value = False Then
                        With Worksheets("EWTC").Range("B15:M15").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    With Worksheets("EWTC").Range("B15:M15").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B15:M15").Font.Bold = False
                    Worksheets("EWTC").Range("B15:M15").Font.Size = 10
                    OptionButton6.BackColor = Worksheets("EWTC").Range("B15").Interior.Color
                    Rows("15:15").RowHeight = 14
                    CommandButton8.Visible = False
                Else
                    OptionButton6.Visible = True
                    CommandButton8.Visible = True
                End If
            Else:
                OptionButton6.Visible = True
                CommandButton8.Visible = True
            End If
        Else:
            OptionButton6.Visible = True
            CommandButton8.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B16:C16").Locked = False
        Worksheets("EWTC").Range("E16").Locked = False
        Worksheets("EWTC").Range("H16").Locked = False
        OptionButton6.Visible = True
        CommandButton8.Visible = True
    End If
    If Worksheets("EWTC").Range("B16") = 0 Then
        Worksheets("EWTC").Range("B17:C17").Locked = True
        Worksheets("EWTC").Range("E17").Locked = True
        Worksheets("EWTC").Range("H17").Locked = True
        If Worksheets("EWTC").Range("C16") = 0 Then
            If Worksheets("EWTC").Range("E16") = 0 Then
                If Worksheets("EWTC").Range("H16") = 0 Then
                    Worksheets("EWTC").Range("B17:C17").Locked = True
                    Worksheets("EWTC").Range("E17").Locked = True
                    Worksheets("EWTC").Range("H17").Locked = True
                    OptionButton7.Visible = False
                    OptionButton7.Value = False
                    Worksheets("EWTC").Range("J16:M16").Interior.Color = Worksheets("EWTC").Range("B16").Interior.Color
                    If OptionButton6.Value = False Then
                        With Worksheets("EWTC").Range("B16:M16").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    With Worksheets("EWTC").Range("B16:M16").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B16:M16").Font.Bold = False
                    Worksheets("EWTC").Range("B16:M16").Font.Size = 10
                    OptionButton7.BackColor = Worksheets("EWTC").Range("B16").Interior.Color
                    Rows("16:16").RowHeight = 14
                    CommandButton9.Visible = False
                Else
                    OptionButton7.Visible = True
                    CommandButton9.Visible = True
                End If
            Else:
                OptionButton7.Visible = True
                CommandButton9.Visible = True
            End If
        Else:
            OptionButton7.Visible = True
            CommandButton9.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B17:C17").Locked = False
        Worksheets("EWTC").Range("E17").Locked = False
        Worksheets("EWTC").Range("H17").Locked = False
        OptionButton7.Visible = True
        CommandButton9.Visible = True
    End If
    
    If Worksheets("EWTC").Range("B17") = 0 Then
        Worksheets("EWTC").Range("B18:C18").Locked = True
        Worksheets("EWTC").Range("E18").Locked = True
        Worksheets("EWTC").Range("H18").Locked = True
        If Worksheets("EWTC").Range("C17") = 0 Then
            If Worksheets("EWTC").Range("E17") = 0 Then
                If Worksheets("EWTC").Range("H17") = 0 Then
                    Worksheets("EWTC").Range("B18:C18").Locked = True
                    Worksheets("EWTC").Range("E18").Locked = True
                    Worksheets("EWTC").Range("H18").Locked = True
                    OptionButton8.Visible = False
                    OptionButton8.Value = False
                    Worksheets("EWTC").Range("J17:M17").Interior.Color = Worksheets("EWTC").Range("B17").Interior.Color
                    If OptionButton7.Value = False Then
                        With Worksheets("EWTC").Range("B17:M17").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    With Worksheets("EWTC").Range("B17:M17").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B17:M17").Font.Bold = False
                    Worksheets("EWTC").Range("B17:M17").Font.Size = 10
                    OptionButton8.BackColor = Worksheets("EWTC").Range("B17").Interior.Color
                    Rows("17:17").RowHeight = 14
                    CommandButton10.Visible = False
                Else
                    OptionButton8.Visible = True
                    CommandButton10.Visible = True
                End If
            Else:
                OptionButton8.Visible = True
                CommandButton10.Visible = True
            End If
        Else:
            OptionButton8.Visible = True
            CommandButton10.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B18:C18").Locked = False
        Worksheets("EWTC").Range("E18").Locked = False
        Worksheets("EWTC").Range("H18").Locked = False
        OptionButton8.Visible = True
        CommandButton10.Visible = True
    End If
    
    If Worksheets("EWTC").Range("B18") = 0 Then
        Worksheets("EWTC").Range("B19:C19").Locked = True
        Worksheets("EWTC").Range("E19").Locked = True
        Worksheets("EWTC").Range("H19").Locked = True
        If Worksheets("EWTC").Range("C18") = 0 Then
            If Worksheets("EWTC").Range("E18") = 0 Then
                If Worksheets("EWTC").Range("H18") = 0 Then
                    Worksheets("EWTC").Range("B19:C19").Locked = True
                    Worksheets("EWTC").Range("E19").Locked = True
                    Worksheets("EWTC").Range("H19").Locked = True
                    OptionButton9.Visible = False
                    OptionButton9.Value = False
                    Worksheets("EWTC").Range("J18:M18").Interior.Color = Worksheets("EWTC").Range("B18").Interior.Color
                    If OptionButton8.Value = False Then
                        With Worksheets("EWTC").Range("B18:M18").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    With Worksheets("EWTC").Range("B18:M18").Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                    End With
                    Worksheets("EWTC").Range("B18:M18").Font.Bold = False
                    Worksheets("EWTC").Range("B18:M18").Font.Size = 10
                    OptionButton9.BackColor = Worksheets("EWTC").Range("B18").Interior.Color
                    Rows("18:18").RowHeight = 14
                    CommandButton11.Visible = False
                Else
                    OptionButton9.Visible = True
                    CommandButton11.Visible = True
                End If
            Else:
                OptionButton9.Visible = True
                CommandButton11.Visible = True
            End If
        Else:
            OptionButton9.Visible = True
            CommandButton11.Visible = True
        End If
    Else:
        Worksheets("EWTC").Range("B19:C19").Locked = False
        Worksheets("EWTC").Range("E19").Locked = False
        Worksheets("EWTC").Range("H19").Locked = False
        OptionButton9.Visible = True
        CommandButton11.Visible = True
    End If
    If Worksheets("EWTC").Range("B19") = 0 Then
        If Worksheets("EWTC").Range("C19") = 0 Then
            If Worksheets("EWTC").Range("E19") = 0 Then
                If Worksheets("EWTC").Range("H19") = 0 Then
                    OptionButton10.Visible = False
                    OptionButton10.Value = False
                    If OptionButton9.Value = False Then
                        With Worksheets("EWTC").Range("B19:M19").Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .Weight = xlThin
                        End With
                    End If
                    Worksheets("EWTC").Range("J19:M19").Interior.Color = Worksheets("EWTC").Range("B19").Interior.Color
                    Worksheets("EWTC").Range("B19:M19").Font.Bold = False
                    Worksheets("EWTC").Range("B19:M19").Font.Size = 10
                    OptionButton10.BackColor = Worksheets("EWTC").Range("B19").Interior.Color
                    Rows("19:19").RowHeight = 14
                    CommandButton12.Visible = False
                Else
                    OptionButton10.Visible = True
                    CommandButton12.Visible = True
                End If
            Else:
                OptionButton10.Visible = True
                CommandButton12.Visible = True
            End If
        Else:
            OptionButton10.Visible = True
            CommandButton12.Visible = True
        End If
    Else:
        OptionButton10.Visible = True
        CommandButton12.Visible = True
    End If
End If
If OptionButton1.Value = False Then
    If OptionButton2.Value = False Then
        If OptionButton3.Value = False Then
            If OptionButton4.Value = False Then
                If OptionButton5.Value = False Then
                    If OptionButton6.Value = False Then
                        If OptionButton7.Value = False Then
                            If OptionButton8.Value = False Then
                                If OptionButton9.Value = False Then
                                    If OptionButton10.Value = False Then
                                    ActiveSheet.Shapes("CommandButton3").Top = 55.5
                                    ActiveSheet.Shapes("CommandButton4").Top = 69
                                    ActiveSheet.Shapes("CommandButton5").Top = 82.5
                                    ActiveSheet.Shapes("CommandButton6").Top = 96
                                    ActiveSheet.Shapes("CommandButton7").Top = 109.5
                                    ActiveSheet.Shapes("CommandButton8").Top = 123
                                    ActiveSheet.Shapes("CommandButton9").Top = 136.5
                                    ActiveSheet.Shapes("CommandButton10").Top = 150
                                    ActiveSheet.Shapes("CommandButton11").Top = 163.5
                                    ActiveSheet.Shapes("CommandButton12").Top = 178
                                    ActiveSheet.Shapes("OptionButton1").Top = 57
                                    ActiveSheet.Shapes("OptionButton2").Top = 70.5
                                    ActiveSheet.Shapes("OptionButton3").Top = 84
                                    ActiveSheet.Shapes("OptionButton4").Top = 97.5
                                    ActiveSheet.Shapes("OptionButton5").Top = 111
                                    ActiveSheet.Shapes("OptionButton6").Top = 124.5
                                    ActiveSheet.Shapes("OptionButton7").Top = 138
                                    ActiveSheet.Shapes("OptionButton8").Top = 151.5
                                    ActiveSheet.Shapes("OptionButton9").Top = 165
                                    ActiveSheet.Shapes("OptionButton10").Top = 178.5
                                    If Worksheets("EWTC").Range("O20").Value > 0 Then
                                        Worksheets("EWTC").Range("L7").Value = 1
                                        Worksheets("EWTC").Range("J10").Formula = "=IF(E10,E10,"""")"
                                        Worksheets("EWTC").Range("J11").Formula = "=IF(E11,E11,"""")"
                                        Worksheets("EWTC").Range("J12").Formula = "=IF(E12,E12,"""")"
                                        Worksheets("EWTC").Range("J13").Formula = "=IF(E13,E13,"""")"
                                        Worksheets("EWTC").Range("J14").Formula = "=IF(E14,E14,"""")"
                                        Worksheets("EWTC").Range("J15").Formula = "=IF(E15,E15,"""")"
                                        Worksheets("EWTC").Range("J16").Formula = "=IF(E16,E16,"""")"
                                        Worksheets("EWTC").Range("J17").Formula = "=IF(E17,E17,"""")"
                                        Worksheets("EWTC").Range("J18").Formula = "=IF(E18,E18,"""")"
                                        Worksheets("EWTC").Range("J19").Formula = "=IF(E19,E19,"""")"
                                        Worksheets("EWTC").Range("O10:O19").Value = ""
                                        Worksheets("EWTC").Range("M20").Value = ""
                                        Worksheets("EWTC").Range("L7").Value = ""
                                    End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If
OptionButton1.Height = 9
OptionButton1.Width = 12
OptionButton2.Height = 9
OptionButton2.Width = 12
OptionButton3.Height = 9
OptionButton3.Width = 12
OptionButton4.Height = 9
OptionButton4.Width = 12
OptionButton5.Height = 9
OptionButton5.Width = 12
OptionButton6.Height = 9
OptionButton6.Width = 12
OptionButton7.Height = 9
OptionButton7.Width = 12
OptionButton8.Height = 9
OptionButton8.Width = 12
OptionButton9.Height = 9
OptionButton9.Width = 12
OptionButton10.Height = 9
OptionButton10.Width = 12
Worksheets("EWTC").Unprotect Password:=Worksheets("EWTC").Range("A1")
Worksheets("EWTC").Range("L7").Locked = True
Worksheets("EWTC").Protect Password:=Worksheets("EWTC").Range("A1")
EndLine:
End Sub
 
Upvote 0
you've got references to shapes on the Activesheet at the end of that code, which I suspect is the problem. You can also shorten that code!

Is the code in the EWTC sheet?
 
Upvote 0
Thanks for taking a look. I will amend those references and give it a try later.

Yes, this code is contained within Sheet1 (EWTC) under Sub Private Worksheet_Calculate

Would I also have to add more precise references for the option/command buttons and the like or will they be ok as is?

I'm sure you could work a lot of magic with this in cutting it down in size. I really need to find some time to hone my skills and get it sorted. Like I said, it's mainly IF commands and these could be cut down drastically I'm sure.

Thanks again and I'll let you know if changing the ActiveSheet references works.
 
Upvote 0
the rest should be fine as they will all default to the sheet containing the code. I haven't tested - obviously - but something like this I think would do the same:
Code: 
Private Sub Worksheet_Calculate()
    Dim n As Long
    With Worksheets("EWTC")
        .Unprotect Password:=.Range("A1").Value
        
        For n = 1 To 10
            If .Range("O9").Offset(n).Value = 1 And .Range("J9").Offset(n).Value < 1 Then
                Me.OLEObjects("OptionButton" & n).Object.BackColor = &H80&
            Else
                Me.OLEObjects("OptionButton" & n).Object.BackColor = .Range("M9").Offset(n).Interior.Color
            End If
        Next n
        
        If .Range("L7").Value <> 1 Then
            For n = 1 To 10
                If .Range("B9").Offset(n).Value = 0 Then
                
                    .Range("B10:C10,E10,H10").Offset(n).Locked = True
                    If .Range("C9").Offset(n).Value = 0 And _
                        .Range("E9").Offset(n).Value = 0 And _
                        .Range("H9").Offset(n).Value = 0 And _
                        .Range("H9").Offset(n).Value = 0 Then
                        
                        With Me.OLEObjects("OptionButton" & n)
                            .Visible = False
                            .Object.Value = False
                            .Object.BackColor = Me.Range("B9").Offset(n).Interior.Color
                      End With
                        
                        If n > 1 Then
                            If Me.OLEObjects("OptionButton" & n - 1).Object.Value = False Then
                                With .Range("B9:M9").Offset(2).Borders(xlEdgeTop)
                                    .LineStyle = xlContinuous
                                    .ColorIndex = 0
                                    .Weight = xlThin
                                End With
                            End If
                        End If
                        
                        Me.OLEObjects("CommandButton" & n + 2).Visible = False
                        
                        .Range("J9:M9").Offset(n).Interior.Color = .Range("B9").Offset(n).Interior.Color
                        
                        With .Range("B9:M9").Offset(n)
                            With .Borders(xlEdgeBottom)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .Weight = xlThin
                            End With
                            With .Font
                                .Bold = False
                                .Size = 10
                            End With
                        End With
                        .Rows(9 + n).RowHeight = 14
                    Else
                        Me.OLEObjects("OptionButton" & n).Visible = True
                        Me.OLEObjects("CommandButton" & n + 2).Visible = True
                    End If
                    
                Else
                    .Range("B10:C10,E10,H10").Offset(n).Locked = False
                End If
                
                With .Shapes("OptionButton" & n)
                    .Height = 9
                    .Width = 12
                End With
            Next n
        
            If OptionButton1.Value = False And OptionButton2.Value = False And _
                OptionButton3.Value = False And OptionButton4.Value = False And _
                OptionButton5.Value = False And OptionButton6.Value = False And _
                OptionButton7.Value = False And OptionButton8.Value = False And _
                OptionButton9.Value = False And OptionButton10.Value = False Then
                
                    .Shapes("CommandButton3").Top = 55.5
                    .Shapes("CommandButton4").Top = 69
                    .Shapes("CommandButton5").Top = 82.5
                    .Shapes("CommandButton6").Top = 96
                    .Shapes("CommandButton7").Top = 109.5
                    .Shapes("CommandButton8").Top = 123
                    .Shapes("CommandButton9").Top = 136.5
                    .Shapes("CommandButton10").Top = 150
                    .Shapes("CommandButton11").Top = 163.5
                    .Shapes("CommandButton12").Top = 178
                    .Shapes("OptionButton1").Top = 57
                    .Shapes("OptionButton2").Top = 70.5
                    .Shapes("OptionButton3").Top = 84
                    .Shapes("OptionButton4").Top = 97.5
                    .Shapes("OptionButton5").Top = 111
                    .Shapes("OptionButton6").Top = 124.5
                    .Shapes("OptionButton7").Top = 138
                    .Shapes("OptionButton8").Top = 151.5
                    .Shapes("OptionButton9").Top = 165
                    .Shapes("OptionButton10").Top = 178.5
                    If .Range("O20").Value > 0 Then
                        .Range("L7").Value = 1
                        .Range("J10:J19").Formula = "=IF(E10,E10,"""")"
                        .Range("O10:O19").Value = ""
                        .Range("M20").Value = ""
                        .Range("L7").Value = ""
                    End If
                End If
                
                .Range("L7").Locked = True
                .Protect Password:=.Range("A1").Value
                
            End If '.Range("L7").Value <> 1
            
    End With ' Worksheets("EWTC")
End Sub
 
Last edited:
Upvote 0
Thanks for providing that cut down version.

I first tried just changing the ActiveSheet parts to Worksheet("EWTC").Range and so on but that didn't work. I then proceeded to copy your code and replaced mine.

The majority seems to work (cell locking and formatting) but the visibility of the command/option buttons and a few other bits produce an error. It also didn't solve the clash with other workbooks being open. It's a lot neater and easier to follow though.

I'll take another look at it all next week and see if I can work it out and sort it then. My head is killing me now and it's a Friday.

Thanks again for your help. It's much appriciated!

*EDIT*

Forgot to ask, I'm guessing <> means anything other than?
 
Last edited:
Upvote 0
Boltie said:
but the visibility of the command/option buttons and a few other bits produce an error.
Click to expand...

did you pick up my edited code? I did a quick test after posting and altered a couple of things
Forgot to ask, I'm guessing <> means anything other than?
Click to expand...

yes - it means 'not equal to'
 
Upvote 0
I just quickly tried again and unfortunately it's the same. The buttons appear only after you have removed a value from the corresponding cell in column B (B10:B19) and this isn't always the case. Sometimes it does turn visible, other times it doesn't. This is a little backwards (I think) as I want them to appear when I input a figure and disappear when empty. Once they are set to visible, they don't get set to not visible later when the value is removed.

As a little bit of background, this is something for my work and it was just a little pet project that gradually built up over time. I'm reached my limit on what I understand and I think more learning is in order.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA to store previous value of continuously updating cell
Replies
3
Views
286
Joe4
Joe4
U
  • Question
Type Mismatch Error
Replies
9
Views
247
Alex Blakenburg
A
M
  • Question
Table issue with VBA
Replies
2
Views
132
Mike2
M
W
  • Question
a backup nightmare (may cause depression)
Replies
6
Views
467
johnnyL
J
B
  • Question
Auto calculation off on single sheet even after saving workbook?
Replies
4
Views
235
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,215,734
Messages
6,126,543
Members
449,316
Latest member
sravya

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top