Solved

Excel help. I need a formula or directions

Posted on 2014-02-07
4
273 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 80

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 80

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now