How do I disable cells based on a previous input
Results 1 to 2 of 2

Thread: How do I disable cells based on a previous input
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2011
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 and change the background colour of some cells as follows (but only disable the cells on the same row):

    columnA = Number then disable cells D, F, G and change to red

    columnA = Link then disable cells E, F, G and change to red

    columnA = Image then disable cells D, E and change to red

    Any help much appreciated, I really my excel/vba skills are really not up to much.
    Last edited by kaskade; Dec 2nd, 2011 at 08:48 AM.

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do I disable cells based on a previous input

    Far from elegant but perhaps something along the lines of:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngInterest As Range, rngCell As Range
        Dim strCols As String
        Const C_WS_PWD = "password"
        Set rngInterest = Intersect(Target, Columns("A"))
        If Not rngInterest Is Nothing Then
            For Each rngCell In rngInterest.Cells
                Select Case UCase(rngCell.Value)
                    Case "NUMBER"
                        strCols = "D:D,F:G"
                    Case "LINK"
                        strCols = "E:G"
                    Case "IMAGE"
                        strCols = "D:E"
                    Case Else
                        strCols = ""
                End Select
                If strCols <> "" Then
                    Me.Unprotect C_WS_PWD
                    With Intersect(rngCell.EntireRow, Range("D:G"))
                        .Locked = False
                        .Cells.Interior.ColorIndex = xlNone
                    End With
                    With Intersect(rngCell.EntireRow, Range(strCols))
                        .Locked = True
                        .Cells.Interior.ColorIndex = 3
                    End With
                    Me.Protect C_WS_PWD
                End If
            Next rngCell
        End If
    End Sub
    To install the above right click on the sheet to which you wish to apply the code, choose View Code and paste above into resulting window.

    Modify C_WS_PWD to be whichever password you want to apply to the worksheet.
    (protect the VB Project Model as appropriate)

    NOTE:

    The above code assumes that columns of interest are A, D:G and that prior to the worksheet being protected for the first time these columns have been set as unlocked (VBA will lock as appropriate)

    The code should also account for the possibility of multiple cells being altered simultaneously and possibly via a single formula (thus potentially generating different results in each cell of affected range)
    Does my a$$ look big in this picture ?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •