?
Solved

Excel help. I need a formula or directions

Posted on 2014-02-07
4
Medium Priority
?
288 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 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