CODEWITHSUNDEEP
vbaexcel
sady
sady

Reputation: 301

VBa Code not coming out of loop

Question looks big but answer for you guys will be simple I have code that works for first time and not working for second attempt. I have 2 sheets "Menu" and "Subsheet" Basically, i have data validation drop-down set on Menu Sheet yes/no values. First scenario Selecting "Yes" will enable the cells on second sheet (Subsheet) Selecting "No" will disable cells on second sheet(Subsheet).

Second scenario, User selecting "no" and selecting second sheet will throw a prompt for him to enable cells "ok" and cancel. Select "ok" will enable cells and value in dropdown will be changed to "yes" selecting "cancel" in msgprompt will disable cells and value in dropdown will remain "no" Msg prompt should not be displayed, if user has selected "yes" in dropdown..

Question:Code works fine, until it comes to second scenario. User selects "No" and selects second sheet in the message prompt, he selects "no". Now cells are disabled. If user comes back to Menu Sheet and selects "Yes", will not enable cells. Not sure what is it not enabling cells now. Please help

Code on Menu Sheet

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("A11")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case (Target.Value)
      Case "YES"
        Call uEnable
      Case "NO"
         Call uDisable
         Exit Sub
    End Select
  Application.EnableEvents = True
End Sub

Code on SubSheet

Private Sub Worksheet_Activate()
  UDisable
End Sub

Code on Module

Option Explicit
Private mMessageDisplayed As Boolean
Public Sub uDisable()
  If ActiveSheet.ProtectContents And Not mMessageDisplayed Then
    mMessageDisplayed = True
    If ThisWorkbook.Sheets("Menu").Range("A11") = "NO" Then
      If MsgBox("Cells are locked on current sheet, press ok to Unlock", vbOKCancel + vbInformation) = vbOK Then
        ThisWorkbook.Worksheets("Menu").Range("A11") = "YES"
        With ThisWorkbook.Sheets("Subsheet")
          ActiveWorkbook.Unprotect Password:="xyz"
          .Range("E13:E14").Locked = False
          ActiveWorkbook.Unprotect Password:="xyz"
        End With
      Else
        ThisWorkbook.Worksheets("Menu").Range("A11") = "NO"
        With ThisWorkbook.Sheets("Subsheet")
          ActiveWorkbook.Unprotect Password:="xyz"
          .Range("E13:E14").Locked = True
          ActiveWorkbook.Protect Password:="xyz"
        End With
      End If
    Else
      Exit Sub
    End If
  End If
End Sub

Second module

Public Sub uEnable()
  With ThisWorkbook.Sheets("Subsheet")
    ActiveWorkbook.Unprotect Password:="xyz"
    .Range("E13:E14").Locked = False
    ActiveWorkbook.Protect Password:="xyz"
  End With
End Sub

I tried to use debug method, couldn't identify the root cause.

Two intersect codes

`Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E42")) Is Nothing Then Exit Sub
        Application.EnableEvents = False
        Dim inputCell As Range
        Set inputCell = Range("E43")
            Select Case (Target.Value)
                Case "Specific Days"
                    inputCell.Locked = False
                    inputCell.Activate
                Case Else
                'This handles **ANY** other value in the dropdown
                    inputCell.Locked = True
    '                inputCell.Clear
            End Select
       Application.EnableEvents = True

If Intersect(Target, Range("E29")) Is Nothing Then Exit Sub
        Application.EnableEvents = False
        Select Case (Target.Value)
            Case "YES"
                Call Notify
            Case "NO"
                 Call NotifyUserGeneral
        End Select
       Application.EnableEvents = True
End Sub`

Upvotes: 0

Views: 79

Answers (1)

Comintern
Comintern

Reputation: 22205

Remove the Exit Sub from underneath Call uDisable. Otherwise Application.EnableEvents = True never gets called...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case (Target.Value)
        Case "YES"
            Call uEnable
        Case "NO"
            Call uDisable
            'Exit Sub     <---Can't do this.
    End Select
    Application.EnableEvents = True
End Sub

...and there isn't any other code that will turn them back on. You can't rely on an event handler to set Application.EnableEvents = True after you've turned off event handling.

Upvotes: 4

Related Questions