Solved

Display Validation based on Conditions

Posted on 2011-02-22
18
339 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
  • 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I wanted to share this with fellow Experts, who might not know. How often have you wanted to learn something, only to be set back by either restrictions imposed on "trial" or "evaluation" software?  How often have you had to rebuild a home networ…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

705 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

20 Experts available now in Live!

Get 1:1 Help Now