Display Validation based on Conditions

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
Bright01Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Try the file now. :)

Sid
Validation-Sample.xlsm
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
@Bright01: You forgot to add the Excel Zone and probably why you didn't get any response till now :)

Sid
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
SiddharthRoutCommented:
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
 
Bright01Author Commented:
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
 
SiddharthRoutCommented:
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
 
Bright01Author Commented:
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
 
SiddharthRoutCommented:
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
 
Bright01Author Commented:
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
 
SiddharthRoutCommented:
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
 
Bright01Author Commented:
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
 
SiddharthRoutCommented:
Workbook protected.

Sid
0
 
Bright01Author Commented:
Drat!  Sorry.  Password = lmm
0
 
Bright01Author Commented:
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
 
SiddharthRoutCommented:
That is not because of my code. It is because of the code that you put in Workbook Open event, I guess.

Sid
0
 
Bright01Author Commented:
Great Job!!!!!
0
 
Bright01Author Commented:
Sid,

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

Thanks,

B.
0
 
SiddharthRoutCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.