CODEWITHSUNDEEP
excelvba
Kaskade
Kaskade

Reputation: 795

How do I disable cells based on a previous input

I am trying to create an advanced spreadsheet in excel that caters for people who really need to be told exactly what to do and what not to do. So based on the selection in column A I want to disable some cells as follows (but only disable the cells on the same row):

Number- disable cells D, F, G
Link - disable cells E, F, G
Image - disable cells D, E

Any help much appreciated, I really am not to up on my excel/vba skills.

Upvotes: 0

Views: 7302

Answers (1)

Justin Self
Justin Self

Reputation: 6265

You can lock cells so they may not be selected.

Range("A2").Locked = True

However, this requires that you lock the sheet (with or without a password).

WorkSheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
WorkSheets("Sheet1").EnableSelection = xlUnlockedCells

So you would just toggle which cells are locked as the user clicks on the 'main' cell.

In order to have this change when the user selects a new cell, you would need to hook into the Selection Change event (put the below code in your sheet module).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Target is the currently selected cell. So now you could test to see which cell is currently selected and then lock the other cells as needed.

With all of that said, I would recommend against this and instead use a UserForm.

EDIT 2 - Updated based on comments.

Here's a simple implementation

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
    Dim tRow As Integer
    tRow = Target.Row

    Worksheets("Sheet1").Unprotect

    Dim dLock, eLock, fLock, gLock As Boolean

    Range("D" & tRow & ":G" & tRow).Locked = False
    Select Case Range("b" & tRow).Value
      Case "Number"
        dLock = True
        fLock = True
        gLock = True
      Case "Link"
        eLock = True
        fLock = True
        gLock = True
      Case "Image"
        dLock = True
        eLock = True
    End Select

      Range("d" & tRow).Locked = dLock
      Range("e" & tRow).Locked = eLock
      Range("f" & tRow).Locked = fLock
      Range("g" & tRow).Locked = gLock


    Worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Worksheets("Sheet1").EnableSelection = xlUnlockedCells

  End If
End Sub

Upvotes: 2

Related Questions