Drop Down list 'if' Function command

[bobfritter]

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.

 

[erik.van.geit]

Hi, bobfritter
Welcome to the Board !!!!!

to my sense this needs more clarification

please provide an example

kind regards,
Erik

 

[bobfritter]

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]

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]

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]

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 ...

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]

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]

Hi again,

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

If LCase(Target) = "no" Then Exit Sub

before or after

If Target = "" Then Exit Sub

or combined

If Target = "" Or LCase(Target) = "no" Then Exit Sub

best regards,
Erik

 

[bobfritter]

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]

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

msg = "the item """ & Target & """ has been found in cell " & Cells(c.Row, "G").Value

best regards,
Erik