VBa Code not coming out of loop

Question

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`

I think uDisable is locking and protecting the sheet. While uEnable is only unlocking. Will probably need to unprotect, with the relevant password, too. — David Rushton
– David Rushton, Commented Nov 2, 2016 at 16:10
Also seems to be redundant to have a yes/no check in more than one spot. You should just let the one section handle that — Doug Coats
– Doug Coats, Commented Nov 2, 2016 at 16:13
@destination-data I tried to use unprotect/protect in uEnable (modified the code above). But that dint help too!! — sady
– sady, Commented Nov 2, 2016 at 16:42
@destination-data Issue still persist. Cells are not getting enabled when the drop is set "yes". I have to select "no" and click "ok" on message prompt to click "ok", so that cells are enabled. — sady
– sady, Commented Nov 2, 2016 at 16:46

Comintern · Accepted Answer · 2016-11-02 16:41:27Z

4

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.

answered Nov 2, 2016 at 16:41

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

sady Over a year ago

@Comintem, sure, let me quickly try that!!

sady Over a year ago

@Comintem, Unfortunately, It dint help. Any idea, why uEnable function module is not getting called when i select "Yes" value in dropdown

sady Over a year ago

I guess thats the problem. Call uEnable is not calling the module. But not sure what could be the reason. Please suggest if you have any

Comintern Over a year ago

@sady - Public uEnable() should be Public Sub uEnable(). Also, you have Range("A11") in uDisable, but Range("A1") in Worksheet_Change. Are those supposed to be different cells?

sady Over a year ago

@Comintem - I did a blunder while copying. It was a typo. On the workbook i see it is public Sub uEnable(). Range ("A1") is actually (A11), ?It was a typo again

|

Collectives™ on Stack Overflow

VBa Code not coming out of loop

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related