当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - VBA code not activating cell

问题描述:

Looks like something wrong with my code. But, I am not able to figure the problem.

I have 2 tabs on workbook. Main sheet and Sub Sheet.

Selecting "yes" in the drop-down on main sheet will enable Sub sheet for entry.

Selecting "No" in the drop-down on main sheet will disable cells on Sub-sheet.

My problem : When I select "No", I dont see the "Active Cell" on any of the sheets. What I mean by Active Cell is the green border we get when we click on cell(Screenshot attached).

Code on Main Sheet

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("R12")) Is Nothing Then

If Target.Value = "YES" Then

Call Enabler

Else

Call Disabler

End If

End If

Application.EnableEvents = True

End Sub

Code on Modules

Public Sub Disabler()

With ThisWorkbook.Sheets("SubSheet")

.Unprotect Password:="xyz"

.Range("E13:E14").Locked = True

.Protect Password:="xyz"

End With

End Sub

Public Sub Enabler()

With ThisWorkbook.Sheets("SubSheet")

.Unprotect Password:="xyz"

.Range("E13:E14").Locked = False

.Protect Password:="xyz"

End With

End Sub

网友答案:

Something like the following should work for you...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
    Application.EnableEvents = False
    If Target.Address <> "$R$12" Then Exit Sub
    If Target.Value = "YES" Then
        Call LockRange(False)
    Else
        Call LockRange(True)
    End If

ExitSub:
    Application.EnableEvents = True
End Sub

Private Function LockRange(bFlag As Boolean) As Boolean
    On Error Resume Next
    With ThisWorkbook.Sheets("SubSheet")
        .Unprotect Password:="xyz"
        .Range("E13:E14").Locked = bFlag
        .Protect Password:="xyz"
        'Debug.Print bFlag
    End With
    LockRange = True
End Function
网友答案:

I guess you have to type in:

.EnableSelection = xlNoRestrictions

BTW you may want to shorten your code by merging Disabler() and Enabler() subs into one Sub:

Public Sub DisableSubSheet(disable As Boolean)
    With ThisWorkbook.Worksheets("SubSheet")
        .Unprotect Password:="xyz"
        .Range("E13:E14").Locked = disable
        .Protect Password:="xyz"
        .EnableSelection = xlNoRestrictions '<--| make it possible for user to select cells
    End With
End Sub

thus, changing your Worksheet_Change event handler code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("R12")) Is Nothing Then
        If Target.Value = "YES" Then
            DisableSubSheet False '<--| in place of previous 'Call Enabler'
        Else
            DisableSubSheet True '<--| in place of previous 'Call Disabler'
        End If
    End If
    Application.EnableEvents = True
End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: