CODEWITHSUNDEEP
vbaexcel
Manu
Manu

Reputation: 83

VBA to select sheet based on cell value of another sheet

On Sheet 1(viva-2) Row 11 has a drop-down(validation) with yes/no. By default, value will be "no" and sheet 11(Manage-d) cell range A11:D30 should be disabled/locked. Selecting "Yes", user should be able to select Sheet11(Manage-d) and cells from range A11:D30 should be unlocked.

I am new to VBA, but I am putting my effort to learn.

Public Sub Worksheet_SelectionChange(ByVal Target As Range)


    Dim RNG                         As Range


    If Target.Row = 11 Then
     If Range("11").Value = "YES" Then
            Sheets("Manage-d").Select
            Sheets("Manage-d").Range("A11:D30").Locked = False
            Sheets("Manage-d").Range("A11:D30").Activate
        Else
             Sheets("Manage-d").Range("A11:D30").Locked = True
     End If
    End If

Upvotes: 0

Views: 3608

Answers (2)

user6432984
user6432984

Reputation:

I use the Worksheet_Change instead of the Worksheet_SelectionChange so that the user doesn't have to ciack another cell to trigger the macro.

Assuming the drop-down(validation) is in Range("A11"):

 Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A11")) Is Nothing Then
        With Sheets("Manage-d")

            .Range("A11:D30").Locked = (UCase(Target.Value) = "NO")

            If UCase(Target.Value) = "YES" Then
                Application.Goto .Range("A11:D30"), True
            End If

        End With
    End If

End Sub

enter image description here

Upvotes: 0

Sayyed Abbas Rizvi
Sayyed Abbas Rizvi

Reputation: 102

Range object represents a single cell or a range of cells.This code is working for me

 If Range("A1").Value = "YES" Then '' Range A1 is the first cell
        Sheets("Manage-d").Select
        Sheets("Manage-d").Range("A11:D30").Locked = False
        Sheets("Manage-d").Range("A11:D30").Activate
    Else
         Sheets("Manage-d").Range("A11:D30").Locked = True
 End If

Upvotes: 1

Related Questions