Solved

Display Validation based on Conditions

Posted on 2011-02-22
18
352 Views
Last Modified: 2012-05-11
I have a spreadsheet where I want to display the option for a validation list; but only if there is text in the adjointing cell. The text and location of the text shifts when selecting different industries.  So, in the sample file, If text is evident in Cell B7, than the Validation Option should show up in Cell A7. The validation should also show up in Cell A14 and A20 (although I didn't put it in as a sample).  The same would happen in Cells C and D, again if there is text in cell D, than the validation option would present itself in the corresponding cell in column C.

That's it!

B.
Auto-Validation-Option.xlsm
0
Comment
Question by:Bright01
[X]
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
  • 10
  • 8
18 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34955940
Here is a sample.

Sid

Code Used

Sub Sample()
    
    Dim rng1 As Range, rng2 As Range
    Dim i As Long, rng1LastRow As Long, rng2LastRow As Long
    Dim ws As Worksheet
    
    On Error GoTo Err
    
    Application.ScreenUpdating = False
    
    Set ws = Sheets("Sheet1")
    rng1LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
    rng2LastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
    
    Set rng1 = ws.Range("B1:B" & rng1LastRow)
    Set rng2 = ws.Range("D1:D" & rng2LastRow)
    
    For i = 1 To rng1LastRow
        If Len(Trim(ws.Range("B" & i))) <> 0 Then
           With ws.Range("A" & i).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="H,M,L"
            End With
        End If
    Next i

    For i = 1 To rng2LastRow
        If Len(Trim(ws.Range("D" & i))) <> 0 Then
           With ws.Range("C" & i).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="H,M,L"
            End With
        End If
    Next i
Sidz:
    Application.ScreenUpdating = True
    Exit Sub
Err:
    MsgBox Err.Description
    GoTo Sidz
End Sub

Open in new window

Auto-Validation-Option.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34955947
@Bright01: You forgot to add the Excel Zone and probably why you didn't get any response till now :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34956037
And if you want to generate the validation list when you manually change values in col B and Col D then use this.

Sample File attached.

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Err
    
    Dim rng1 As Range, rng2 As Range
    Dim rng1LastRow As Long, rng2LastRow As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    rng1LastRow = Range("B" & Rows.Count).End(xlUp).Row
    rng2LastRow = Range("D" & Rows.Count).End(xlUp).Row
        
    Set rng1 = Range("B1:B" & rng1LastRow)
    Set rng2 = Range("D1:D" & rng2LastRow)
    
    If Not Intersect(Target, rng1) Is Nothing Then
        With Range("A" & Target.Row).Validation
            .Delete
            If Len(Trim(Target.Value)) <> 0 Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="H,M,L"
            End If
        End With
    ElseIf Not Intersect(Target, rng2) Is Nothing Then
        With Range("C" & Target.Row).Validation
            .Delete
            If Len(Trim(Target.Value)) <> 0 Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="H,M,L"
            End If
        End With
    End If
Sidz:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Err:
    MsgBox Err.Description
    GoTo Sidz
End Sub

Open in new window

Auto-Validation-Option.xlsm
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Bright01
ID: 34956087
Sid,

Thanks for the quick response.  I opened the spreadsheet and don't see how this works. There is no validation in the cells.  Can you elaborate on how I install this module?  Do I just set up the validation then it will work?

Thank you,

B.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34956113
Bright01: I have given you two options :)

1) Either directly run the Macro "Sample" in Module1 (ID: 34955940) OR

2) When you type directly in cells in Col B abd Col C then the list will be automatically generated in Col A and Col D (ID: 34956037)

Sid
0
 

Author Comment

by:Bright01
ID: 34956476
Sid,

I've put the code into the proper "sheet".  When the sheet then populates itself (because I've made a selection), it does not display the validation list.  I ran your sample and it works perfectly.....but doesn't work when I put it into my workbook.  I think the problem is that it doesn't recognize the change in text in cells B and D when it auto populates.  Is there a simple fix for this?

Thank you,

B.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34956487
Add this piece of code with the code that I gave in ID: 34956037

Private Sub Worksheet_Calculate()
    Worksheet_Change
End Sub

Open in new window


If it still doesn't help then please upload your workbook and let me have a look at it. :)

Sid
0
 

Author Comment

by:Bright01
ID: 34956679
Sid,

Where do I add it?  At the top or bottom?  I did and it didn't work.

I cannot upload the entire Workbook and stripping out a sample to show would take an hour.  Let me see if I can do a better job explaining.  I have a macro that auto populates the cells in B and D.  The cells actually change based on the number of elements that are populated in each column (they align together so that one element in column B may have 3 or 4 elements in Column D.  What I'm looking for is based on changes to the alignment, based on the slection, I want a validation list in Columns A and C to show up (only in those cells where text is evident.  Does that make sense?

If not, I'll try to carve out a sample for you.

B.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34956696
Where have you pasted the code? In a module or worksheet area?

I would love to see a basic sample for quick and accurate solution.

Sid
0
 

Author Comment

by:Bright01
ID: 34956739
Sid,

Here's the sample.  Change the Industry in cell and you will see Col. B and D change.  Now you can see what I'm trying to do with regard to prioritization of the elements by using HML in A and C.

Thank you for taking a look.

B.
Validation-Sample.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34956827
Workbook protected.

Sid
0
 

Author Comment

by:Bright01
ID: 34956901
Drat!  Sorry.  Password = lmm
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34956974
Try the file now. :)

Sid
Validation-Sample.xlsm
0
 

Author Comment

by:Bright01
ID: 34957049
Works great except for the error I get on startup.  I've got to run to a meeting; will check in later.  Much thanks; you've more than earned 500 points!

B.  


2-22-2011-7-15-33-PM.png
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34957062
That is not because of my code. It is because of the code that you put in Workbook Open event, I guess.

Sid
0
 

Author Closing Comment

by:Bright01
ID: 34957075
Great Job!!!!!
0
 

Author Comment

by:Bright01
ID: 34957316
Sid,

What did you change?  I need to know so I can try to trouble shoot the implementation.

Thanks,

B.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34957319
If you check the sheet code area you will see that I used the code that I gave above in the worksheet change event.

Sid
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

730 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