CODEWITHSUNDEEP
excelvba
Falhuddin
Falhuddin

Reputation: 103

How to Lock Range with more than 2 If Statement

Is it Lock cell with more than 2 "IF" statement.

Example : I want to Check each row ("L12:L48") if there is value between 1100000 ~ 1149999 then useMsgBox . If value more than 1150000 then use MsgBox and lock other cell ("E12:E28").

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

i = 12

Do
    If Cells(i, 12).Value >= 1100000 And Cells(i, 12).Value <= 1149999 Then
        MsgBox "Caution : Cutter Meter Nearly Exceed Limit"
    ElseIf Cells(i, 12).Value >= 1150000 Then
        MsgBox "Please Change Cutter"
    ElseIf Cells(i, 12).Value >= 1150000 Then
        ActiveSheet.Unprotect
        Range("E12:E48").Locked = True
        ActiveSheet.Protect
    End If
i = i + 1
Loop Until i = 48

End Sub

I already run this code but MsgBox keep appear until reach last checked row (b'coz I use accumulate calculation on checked row) and cell lock is not function. Please open picture link below for excel view.

Can this be achieved using VBA and if so, how?

Many thanks.

Locked Excel

Upvotes: 0

Views: 178

Answers (2)

Falhuddin
Falhuddin

Reputation: 103

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer Static bWarning As Boolean

i = 12

Do
    If bWarning = False Then
        If Cells(i, 12).Value >= 1100000 And Cells(i, 12).Value <= 1149999 Then
            MsgBox "Caution : Cutter Meter Nearly Exceed Limit"
            bWarning = True
        ElseIf Cells(i, 12).Value >= 1150000 Then
            MsgBox "Please Change Cutter"
            bWarning = True
            ActiveSheet.Unprotect
            Range("E12:K48").Locked = True
            ActiveSheet.Protect
        End If
    End If
i = i + 1
Loop Until i = 48

End Sub

*Ok guys... I already solve the problem.. Both of them... 1) Lock area in merged cell (2)Stop msgBox from multiple pop-up... *Thank You Very much for your quick respond... Really appreciate... :)

Upvotes: 0

Eduard
Eduard

Reputation: 666

Won't changing

From

If Cells(i, 12).Value >= 1100000 And Cells(i, 12).Value <= 1149999 Then
    MsgBox "Caution : Cutter Meter Nearly Exceed Limit"
ElseIf Cells(i, 12).Value >= 1150000 Then
    MsgBox "Please Change Cutter"
ElseIf Cells(i, 12).Value >= 1150000 Then
    ActiveSheet.Unprotect
    Range("E12:E48").Locked = True
    ActiveSheet.Protect
End If

To

If Cells(i, 12).Value >= 1100000 And Cells(i, 12).Value <= 1149999 Then
    MsgBox "Caution : Cutter Meter Nearly Exceed Limit"
ElseIf Cells(i, 12).Value >= 1150000 Then
    MsgBox "Please Change Cutter"
    ActiveSheet.Unprotect

    If Range("E12:E48").MergeCells = False Then
        Range("E12:E48").Locked = True
    Else
        Range("E12:E48").MergeArea.Locked = True
    End If

    ActiveSheet.Protect
End If

Solve your problem?

Upvotes: 1

Related Questions