Solved

# Display Validation based on Conditions

Posted on 2011-02-22
355 Views
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
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
• 10
• 8

LVL 30

Expert Comment

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
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
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
``````
Auto-Validation-Option.xlsm
0

LVL 30

Expert Comment

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

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
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
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
``````
Auto-Validation-Option.xlsm
0

Author Comment

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

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

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

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

``````Private Sub Worksheet_Calculate()
Worksheet_Change
End Sub
``````

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

Sid
0

Author Comment

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

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

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

ID: 34956827
Workbook protected.

Sid
0

Author Comment

ID: 34956901
0

LVL 30

Accepted Solution

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

Sid
Validation-Sample.xlsm
0

Author Comment

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

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

ID: 34957075
Great Job!!!!!
0

Author Comment

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

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

Question has a verified solution.

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