Drop Down list 'if' Function command

bobfritter

New Member
Joined
Oct 28, 2006
Messages
10
Is it possible to use the ‘if’ function command in a drop down menu list with two other variables from other drop down lists, so that it will not allow an option to be selected until the variable changes (Yes/No Scenario) or even send a pop up message with the reference to the cell in which the variable requires changing.

Sorry I am very new to the quirks and wonders of the excel world. Any help would be appreciated.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, bobfritter
Welcome to the Board !!!!!

to my sense this needs more clarification

please provide an example

kind regards,
Erik
 

bobfritter

New Member
Joined
Oct 28, 2006
Messages
10
EXAMPLE FOR ABOVE

COLUMN 'D' HAS A DROP DOWN LIST OF TASKS
COLUMN 'E' IS IF THAT TASKS REQUIRES UPDATING YES OR NO OPTION
COLUMM 'F' ID COMPLETE LIST YES OR NO

If selected the same task in row d and that is already being updated higher up the spread sheet row (column e = yes ,F = no)

I want the 'if' function to stop me from selecting Yes in column E for that row until column f is changed to ''yes'' once completed for the row above this.

Thanks for any advice, as I said I am a novice to the excel world so any help would be great thanks.


[/list]
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

is it this what you mean ?
E6 may not get "yes" because E3 has "yes" and F3 has "no"
E5 can get "yes" because there is nothing in E2
  D     E        F        
1 TASKS UPDATING COMPLETE 
2 task1                   
3 task2 yes      no       
4 task3 yes      yes      
5 task1 yes               
6 task2                   

test

[Table-It] version 06 by Erik Van Geit

best regards,
Erik
 

bobfritter

New Member
Joined
Oct 28, 2006
Messages
10

ADVERTISEMENT

Yes that is what i'm trying to achieve can you help with this

Many thanks for your help very much appreciated.

They are all in drop down list format currently
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
when you're "very new", this code could look like "chinese"
my advise would be to experiment a lot, surf this board and the helpfiles ...

assuming the example from above is something like your sheet ...
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As Range
Dim FA As String    'First Address
Dim problem As Boolean
Dim msg As String

Set rng = Intersect(Columns("E"), Target)
If rng Is Nothing Then Exit Sub

    If Target.Count > 1 Then
        With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
        End With
    MsgBox "one at a time please", 48, "ERROR"
    Exit Sub
    End If

If Target = "" Then Exit Sub

    With Columns("D")
    Set c = .Find(Cells(Target.Row, "D"), lookat:=xlWhole, MatchCase:=False)
        If Not c Is Nothing Then
        FA = c.Address
            Do
                If LCase(Cells(c.Row, "E")) = "yes" And LCase(Cells(c.Row, "F")) = "no" Then
                problem = True
                Exit Do
                End If
            Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FA
        End If
    End With

    If problem Then
        With Application
        .EnableEvents = False
        .Undo
        msg = "the item """ & Target & """ has been found in cell " & c.Address(0, 0) & " with UPDATING = ""yes"" and COMPLETE = ""no"""
        MsgBox msg, vbCritical, "ERROR"
        .EnableEvents = True
        End With
    End If

End Sub
http://www.cpearson.com/excel/events.htm
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE


best regards,
Erik
 

bobfritter

New Member
Joined
Oct 28, 2006
Messages
10

ADVERTISEMENT

Thanks so much for your help really appreciate you taking time to help me with this.

I have tried to change the code to allow it to accept Update = ''No'' at any time with a
If LCase(Cells(c.Row, "E")) = "no" Then
problem = False

but the message is still displayed each time

How can I get it ignore it if the selection is No?

Thanks again
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi again,

if you mean you want to type "no" in column E anyway then add one line

Code:
If LCase(Target) = "no" Then Exit Sub
before or after
Code:
If Target = "" Then Exit Sub

or combined
Code:
If Target = "" Or LCase(Target) = "no" Then Exit Sub

best regards,
Erik
 

bobfritter

New Member
Joined
Oct 28, 2006
Messages
10
Erik,

Your help has given us great results in tracking our instructions being duplicated. Thank you kindly

Just one more little question, you know this part of the code

msg = "the item """ & Target & """ has been found in cell " & c.Address(0, 0)

Is it possible to Change c.address(0,0) to display the contents of a cell in column 'G' of that row, as this will then display a completion date in the message box to the user.

Thanks Again
Bob

GBY
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
take a look in the helpfiles for

ROW
CELLS

use one of those
Cells(c.Row, "G")
Cells(c.Row, 7)


you can add .Value, but that's the default
Code:
msg = "the item """ & Target & """ has been found in cell " & Cells(c.Row, "G").Value

best regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,113,862
Messages
5,544,728
Members
410,630
Latest member
JFORTH97
Top