Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel help. I need a formula or directions

Posted on 2014-02-07
4
283 Views
Last Modified: 2014-02-07
Need a formula or directions to force a multiple choice drop down list in column G if the adjacent cell in column F=0.

On the same sheet I need a formula or directions where if a cell in column M=N than the adjacent cell in Rowe N can be edited, if M=Y then adjacent cell in N cannot be edited.
0
Comment
Question by:Mcottuli
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39842160
I believe you can do what you request using a combination of worksheet protection and a Worksheet_Change macro.

If you protect the worksheet, you can prevent data entry in cells that are locked. You will need to unlock all cells that you want the user to be able to change prior to protecting the worksheet. You do this by selecting those cells, then using the Home...Format Cells...Lock Cells menu item. This is a toggle, so you click it once to unlock the cells, then click it again to lock them. You want those cells unlocked.

The Worksheet_Change macro runs automatically when the user enters data in the worksheet. It can be designed to watch cells in column F and M for inputs. If in F, then it either adds a data validation dropdown to the adjacent cell or removes it. If in M, it either locks the adjacent cell in column N, or unlocks it.

'This code must go in the worksheet code pane. It won't work at all if installed anywhere else!
Private Sub Worksheet_Activate()
Me.Protect UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("F2:F1000")
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    For Each cel In targ.Cells
        With cel.Offset(0, 1).Validation
            .Delete
            If cel.Value = 0 Then
                .Add xlValidateList, Formula1:="Yes,No"
            End If
        End With
    Next
End If

Set targ = Range("M2:M1000")
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    For Each cel In targ.Cells
        Select Case UCase(cel.Value)
        Case "Y"
            cel.Offset(0, 1).Locked = True
        Case "N"
            cel.Offset(0, 1).Locked = False
        End Select
    Next
End If
End Sub

Open in new window

'This code must be installed in the ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_Open()
Worksheets("Sheet1").Protect UserInterfaceOnly:=True
End Sub

Open in new window

ColumnFandM-Q28359206.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39842183
As written, the Worksheet_Change macro is watching cells in rows 2 through 1000. It is putting a dropdown with Yes or No choices in column G. And it is locking or unlocking cells in column N.

There is a Workbook_Open sub to protect the worksheet with UserInterfaceOnly property set to True so macros can make changes. A Worksheet_Activate sub also applies the UserInterfaceOnly property. I used the belt and suspenders approach so you would have a higher likelihood of success during testing on your real workbook. You wouldn't need the Worksheet_Activate sub when you release the workbook to your users.
0
 
LVL 1

Author Comment

by:Mcottuli
ID: 39842761
Thank you so much, I'll give a shot and let you know.
0
 
LVL 1

Author Comment

by:Mcottuli
ID: 39842949
Perfect, Thank you
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I comeā€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question