Excel VBA Compile Error: Else without If

[Popsicle]

Hello All,

I am trying to use a check-box macro named, "AGLFAqueRun" and I am using an If Else statement to use it. When checked, I want it to make a few cells white with black font and unlocked from protected. Then, when checked, the cell returns to it's original form: orange box with red font and some values that I have set. Posted below is the code I have, excluding the variables I have established. I am running Office 2010 with Windows 7.

Excel Code:

---

If AGLFAqueRun = True Then
        Max.Locked = False
        Min.Locked = False
        With Max
            .NumberFormat = "0.00"
         With Max.Interior
            .ColorIndex = 1
         With Max.Font
            .Color = vbBlack
        End With
        With Min
            .NumberFormat = "0.00"
         With Min.Interior
            .ColorIndex = 1
         With Max.Font
            .Color = vbBlack
        End With
     [I][B][COLOR=#000000] Else[/COLOR][/B][/I]
        With Max.Interior
            .ColorIndex = 46
         With Max.Font
            .Color = vbRed
        End With
        With Min.Interior
            .ColorIndex = 46
         With Min.Font
            .Color = vbRed
        End With
        Bmax = TrueBmax
        Bmin = TrueBmin
        Cmax = TrueCmax
        Cmin = TrueCmin
        Dmax = TrueDmax
        Dmin = TrueDmin
        Emax = TrueEmax
        Emin = TrueEmin
        Max.Locked = True
        Min.Locked = True
    End If

End Sub

---

The bold and italicized portion is what the debugger highlights as blue when I run the code.

Thank you,
Popsicle

 

[Popsicle]

Hello All,

I did some more tests on my macro and I ran into a few problems. Basically, I am making a program that calculates values from specific values in certain cells. Some of these cells I have unlocked and some are locked. Once the user has adjusted the unlocked cells and hits, "compute", then the program executes the calculation (The code for this macro starts with "ActiveSheet.Unprotect Password:="MyPass""and finishes with, "ActiveSheet.Protect Password:= "MyPass"" with the actual calculations being carried out in between these two lines of code). The checkbox macro, when checked, is to unlock another set of cells To be manipulated. Then, when the checkbox macro is unchecked, that same set of cells become locked and return to their original values. So, here's my problem: When I hit the compute button, everything locks. Then when I check the checkbox, I get an error, "Runtime 'Error 1004': Unable to set the Locked property of the Range class." Then, when I manually unprotect my sheet, I check the checkbox (no error when I do this) and then hit "Compute" followed by unchecking the check box, I get an error, "Runtime 'Error 1004': Application-defined or Object-defined error." Below is the checkbox code:

Sub AGLFAqueRun_Click()

    Dim Max As Object, Min As Object, Bmax As Variant, Bmin As Variant, Cmax As Variant, _
    Cmin As Variant, Dmax As Variant, Dmin As Variant, Emax As Variant, Emin As Variant, _
    TrueBmax As Variant, TrueBmin As Variant, TrueCmax As Variant, TrueCmin As Variant, _
    TrueDmax As Variant, TrueDmin As Variant, TrueEmax As Variant, TrueEmin As Variant, _
    Batch As Object
    
        Set Batch = Worksheets("Batches").Range("C3")
        Set Max = Worksheets("AG-LF").Range("N21:N24")
        Set Min = Worksheets("AG-LF").Range("P21:P24")
        Bmax = Range("N21")
        Bmin = Range("P21")
        Cmax = Range("N22")
        Cmin = Range("P22")
        Dmax = Range("N23")
        Dmin = Range("P23")
        Emax = Range("N24")
        Emin = Range("P24")
        TrueBmax = Range("U21")
        TrueBmin = Range("V21")
        TrueCmax = Range("U22")
        TrueCmin = Range("V22")
        TrueDmax = Range("U23")
        TrueDmin = Range("V23")
        TrueEmax = Range("U24")
        TrueEmin = Range("V24")
        
    If AGLFAqueRun = True Then
        [B][I]Max.Locked = False[/I][/B]
        Min.Locked = False
        Batch = "MOC"
        With Batch.Font
            .Size = 20
            .Italic = True
        End With
        With Max
            .Interior.ColorIndex = 0
            .Font.Color = vbBlack
        End With
        With Min
            .Interior.ColorIndex = 0
            .Font.Color = vbBlack
        End With
      Else
        Batch.Clear
        With Max
            [B].Interior.ColorIndex = 44[/B]
            .Font.ColorIndex = 3
        End With
        With Min
            .Interior.ColorIndex = 44
            .Font.ColorIndex = 3
        End With
        Bmax = TrueBmax
        Bmin = TrueBmin
        Cmax = TrueCmax
        Cmin = TrueCmin
        Dmax = TrueDmax
        Dmin = TrueDmin
        Emax = TrueEmax
        Emin = TrueEmin
        Max.Locked = True
        Min.Locked = True
    End If
    
    With Max
        .NumberFormat = "0.00%"
    End With
    
    With Min
        .NumberFormat = "0.00%"
    End With
    
End Sub

What is bold and italicized is the runtime error, "Unable to set the Locked property of the Range class" and the only bold portion is the runtime error, "Application-defined or Object-defined error." Let me know if any of this needs more clarification.

Thank you so much for your help,
Popsicle

 

[StephenCrump]

Two things you could quickly check:

1. Is ActiveSheet the same as Worksheets("AG-LF"), i.e. are you unprotecting the right worksheet?

2. Are there any merged cells straddling Worksheets("AG-LF").Range("N21:N24") and other cells?

If no success, can you post the rest of your code?

 

[Popsicle]

Thank you Stephen for your reply. I found no merged cells (I used the ctrl+F method) and the active sheet is indeed the AG-LF sheet. I get the same two errors when I manually lock the sheet and play with the checkbox. Below is the all the code for the calculations and then I'll also post the check box code again.
Here is the calculation macro:

Sub AGLFOrgRun_ADJUSTMENT()

    ActiveSheet.Unprotect Password:="MyPass"
    
'   x change(lb)              x want(lb)           A= x have(lb)
    Range("G71") = Round(Range("M58").Value - Range("F58").Value, 4)
    
'   x solution(lb)               x want(lb)                x have(lb)                x have(%)
    Range("H71") = Round((Range("M58").Value - Range("F58").Value) / Range("D58").Value, 4)

'   y change(lb)               y want(lb)            B= y have(lb)
    Range("G72") = Round((Range("M60").Value - Range("F60").Value) / 0.05, 4)
     
'   y solution(lb)              y want(lb)                y have(lb)                y have(%)
    Range("H72") = Round((Range("M60").Value - Range("F60").Value) / Range("D60").Value, 4)
    
    Dim A As Variant, B As Variant, Amax As Variant, Amin As Variant, _
        Bmax As Variant, Bmin As Variant, xadd As Range, _
        yadd As Range, xchng As Variant, ychng As Variant, _
        Removethis As Variant, Marker As Range, rng As Range, Min As Variant
        
        A = Range("F58").Value
        B = Range("F60").Value
        
'       x RANGE
        Amax = Range("J67").Value
        Amin = Range("J69").Value
'       y RANGE
        Bmax = Range("K67").Value
        Bmin = Range("K69").Value
        
        xchng = Range("G71").Value
        ychng = Range("G72").Value
        
        Set xadd = Range("G66")
        Set yadd = Range("G68")
        
        Set Marker = Range("A51")
'                 solutions
        Set rng = Range("H71:H72")
        
        Min = Application.WorksheetFunction.Min(rng)
        Removethis = Min
        
    If A <= Amax And A >= Amin And B <= Bmax Then
        xadd = 0
        yadd = 0
        Marker = 1
     ElseIf A < Amin And B <= Bmax And B >= Bmin Then
        xadd = xchng
        yadd = 0
        Marker = 2
     ElseIf A <= Amax And A >= Amin And B < Bmin Then
        xadd = 0
        yadd = ychng
        Marker = 3
     ElseIf A < Amin And B < Bmin Then
        xadd =xchng - ychng * 0.95
        xadd = ychng
        Marker = 4
    Else
        xadd = 0
        yadd = 0
        Marker = 5
        MsgBox ("Please remove " & Abs(Round(Removethis, 2)) & "lbs from the solution and press 'Adjust'.")
    End If
    
    ActiveSheet.Protect Password:="MyPass"
            
End Sub

x and y are chemicals that had to change their actual name so as to avoid breaching my confidentiality agreements. y is in a 5% solution and x is a pure chemical. I work as a chemist at a chemical manufacturing plant, if that helps you understand what I am trying to do with this.

Below is the checkbox code:

Sub AGLFOrgRun_Click()
    Dim Max As Object, Min As Object, Bmax As Variant, Bmin As Variant, Cmax As Variant, _
    Cmin As Variant, Dmax As Variant, Dmin As Variant, Emax As Variant, Emin As Variant, _
    TrueBmax As Variant, TrueBmin As Variant, TrueCmax As Variant, TrueCmin As Variant, _
    TrueDmax As Variant, TrueDmin As Variant, TrueEmax As Variant, TrueEmin As Variant, _
    Batch As Object
    
        Set Batch = Worksheets("Batches").Range("C3")
        Set Max = Range("K66")
        Set Min = Range("K68")
        Cmax = Range("J68")
        Cmin = Range("K68")
        TrueCmax = Range("U68")
        TrueCmin = Range("V68")
        
    If AGLFOrgRun = True Then
        Max.Locked = False
        Min.Locked = False
        Batch = "MOC"
        With Batch.Font
            .Size = 20
            .Italic = True
        End With
        With Max
            .Interior.ColorIndex = 0
            .Font.Color = vbBlack
        End With
        With Min
            .Interior.ColorIndex = 0
            .Font.Color = vbBlack
        End With
      Else
        Batch.Clear
        With Max
            .Interior.ColorIndex = 44
            .Font.ColorIndex = 3
        End With
        With Min
            .Interior.ColorIndex = 44
            .Font.ColorIndex = 3
        End With
        Bmax = TrueBmax
        Bmin = TrueBmin
        Cmax = TrueCmax
        Cmin = TrueCmin
        Max.Locked = True
        Min.Locked = True
    End If
    
    With Max
        .NumberFormat = "0.000%"
    End With
    
    With Min
        .NumberFormat = "0.000%"
    End With
End Sub

The checkbox was originally a private sub, but I read somewhere that may causing my problem; it wasn't.
Basically, what I am trying to do is this: I am making a program that allows the user to make chemical solutions that are used later in production. The users can only adjust certain values so that the solutions they make all remain the same. The checkbox is to allow the users to change certain values (when we want to conduct an experiment and deviate from standard production) by changing the color of the cell and its contents to make it obvious that it can be changed and are no longer locked. Does this make any sense?

Thank you again for all your help,
Popsicle

 

[StephenCrump]

Don't you need to unprotect the sheet when you click the checkbox, i.e. inside AGLFOrgRun_Click()?

 

[Popsicle]

Wow... That was the problem. Thank you! I am very happy now! I figured all I needed to do was unprotect the cells I wanted to grant access to. Why does VBA read it like that?

Thank you so much!
Popsicle

 

[StephenCrump]

You're welcome!

Why does VBA read it like that?

It's the same in Excel. You need to:

- Unprotect the sheet before you can lock/unlock cells

- Protect the sheet to protect locked cells.