VBA Drop down list based on dynamic Lists dependent on ref list attributes

GioP · Jul 12, 2012

In the worksheet “PB” range (“C14:C313”) contains drop down list “Account”.

In the worksheet “CA” range (“B3:B1000”) is dynamic List named as “Account”

In the same sheet “CA” range (“D3:D1000”) represents account rules assigned to each account

In total there are 8 rules as listed below:

“Rule 1”

“Rule 2”

“Rule 3”

“Rule 4”

“Rule 5”

“Rule 6”

“Rule 7”

“Rule 8”

“Rule 1” and “Rule 2” requires no list

“Rule 2” and “Rule 4” requires dynamic “LR” list from the worksheet “RC”

“Rule 5” requires dynamic “LA” list from the worksheet “AC”

“Rule 6” requires dynamic “LC” list from the worksheet “CC”

“Rule 7” requires dynamic “LT” list from the worksheet “TC”

“Rule 8” requires dynamic “LP” list from the worksheet “PC”

Question is how to make drop down list available in column “E” in "PB" sheet referring to the rule of for selected account in column “C”?

Example:

If in cell C14 account code is 1100 and respective account rule is “Rule 2” then in cell E14 available list should be “LR”

If in cell C15 account code is 1200 and respective account rule is “Rule 1” then in cell E15 should not contain a list. (Preferably the cell should be locked)

If in cell C16 account code is 3800 and respective account rule is “Rule 5” then in cell E16 available list should be “LA”

If in cell C17 account code is 5500 and respective account rule is “Rule 8” then in cell E16 available list should be “LP”

Etc

I tried to enter the following formula in data validation in column “E” but it failed

Here is formula

=IF(OR(VLOOKUP(Table3[@Account],Table2,3,FALSE)="Rule 2",VLOOKUP(Table3[@Account],Table2,3,FALSE)="Rule 4"),LR,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)="Rule 5",LA,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)="Rule 6",LC,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)="Rule 7",LT,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)="Rule 8",LP,"")))))

I also tried to write VBA but being completely new .. hmmmm got no result

Here it is

Sub LookupLRMain()

If Formula = "=IF(OR(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 2"",VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 4""),LR,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 5"",LA,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 6"",LC,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 7"",LT,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 8"",LP,"""")))))" = True Then

With Selection.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula:= _

"=IF(OR(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 2"",VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 4""),LR,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 5"",LA,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 6"",LC,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 7"",LT,IF(VLOOKUP(Table3[@Account],Table2,3,FALSE)=""Rule 8"",LP,""""))))) "

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

Else

Sheets("PB").Select

Range("B14").Select

End If

End Sub

It look this task for me is a Rubicon and any help to solve this problem I is highly appreciated.

BIG, BIG Thanks in advance

GioP

NARAYANK991 · Jul 12, 2012

Hi ,

I am not very clear on your requirement ; can I say that what you are looking for is to have a validation drop-down , which can be either range LR , LA or LP , depending on which of several different rules apply ?

If you can use the rules to derive the numbers 1 , 2 , 3 , ... then the drop-down can be a list defined by the formula :

=CHOOSE(selected_number,LR,LA,LP)

If the selected_number ( through application of the rules ) is 1 , then the drop-down will be LR , if the selected_number is 2 , then the drop-down will be LA , and so on.

Can you clarify ?

Narayan

GioP · Jul 12, 2012

Hi

Thanks for your swift reply.

Would it be easier if I send you the file to have a look? You are right, different list based on specific rules shall become available in dropdown.

Probably my explanations how dropdown lists should work in column "E" is not completely clear.

I will try to formulate differently by giving the example:

Let say account code in cell “C14” is 1100.

“Rule 2” is attributed to Account 1100. (we can number the rules as you mentioned 1,2,etc)

“Rule 2” by itself defines the list which has to be presented in Dropdown in cell “E14” and it should be “LR” list

(Problem is that it is not really dependent list to use as a solution. On the other hand I can insert extra column to have rule reference aside to account and then choice formula may work. I should try this option with "Choose" formula to say it is working)

Hope it makes the issue more clear

thanks again for your support and time

Giop

NARAYANK991 · Jul 12, 2012

Hi ,

If you can upload your file , that would be ideal. Can you see this link for the details on how to upload ?

http://chandoo.org/forums/topic/posting-a-sample-workbook

Narayan

GioP · Jul 12, 2012

Hi again

Here is the link

https://docs.google.com/open?id=0B4mitJ-SIt9bV3JuWFZFaXI1ckk

Could it be the VBA in the file can be corrected to function or it is complete disaster?

regards

shailyog · Jul 12, 2012

GiOP

Please check the following file if I understood your issue correctly an is it the result you wanted. I did not see the VBA code.

https://www.box.com/s/6bb7560de3356e2917f6

Remember Dropdowns in Data validation are not automatically refreshed while you change the criteria. you have to select the right options again from the dropdown. I hope the automatic refresh can be achieved by VBA.

NARAYANK991 · Jul 12, 2012

Hi ,

The only problem seems to be where NO list is required ; otherwise , the other cases can be treated as follows :

1. Change the data in your CA tab as follows :

[pre]

Code:

1000	Account 1000	Rule 1	1	NO List is required
1100	Account 1100	Rule 2	2	LR
1200	Account 1200	Rule 3	3	NO List is required
1300	Account 1300	Rule 4	4	LR
1400	Account 1400	Rule 5	5	LA
1500	Account 1500	Rule 6	6	LC
1600	Account 1600	Rule 7	7	LT
1700	Account 1700	Rule 8	8	LP

[/pre]
I have removed the words "list is required" wherever a list is required. Now , in your PB tab , in column G , use the formula ( in cell G14 , and then copy downwards ) :

=VLOOKUP($C14,Lookup_Table,5,FALSE)

where Lookup_Table refers to : =CA!$B$4:$F$52

Now , in your validation list , use the following formula ( in cell F14 , and then copy downwards ) : =INDIRECT($G14)

Since your ranges LA , LR , LC , LT and LP are defined , you will get your drop-downs wherever these are available ; we need to take care of those cells where NO list is required. Some VBA might be required ; is this OK with you ?

Narayan

GioP · Jul 12, 2012

DONE it is working

Amazing, the dropdown list is working. Thanks a lot. I followed your suggestions and edited file accordingly.

In worksheet PB I modified suggested formula for column “G” as following:

=IF(ISERROR(VLOOKUP(C14,CA!B:F,5,FALSE)),"",IF(VLOOKUP(C14,CA!B:F,5,FALSE)="NoList","",VLOOKUP(C14,CA!B:F,5,FALSE)))

In column “F” I have set data validation list as you suggested:

=INDIRECT($G14)

Lists references in the worksheet “CA” were assigned in column “F” according to your advice:

LR

LA

LC

LT

LP

NoList

Use link below to review the updated file:

https://docs.google.com/open?id=0B4mitJ-SIt9bV0FMaDVIbXI4bWc

Thanks a lot for your support; I really appreciate your kind help

Looking forward and hoping to see you as great support in my and others future excel projects

Thanks again

GioP

P.S.

In respect to “Nolist” it would be great if the cell in column “E” becomes protected. That is probably VBA then.

NARAYANK991 · Jul 12, 2012

Hi ,

Good to know you've got it working ; for "Nolist" , the amount of code would not be much. I'll see if I can post it tomorrow morning , since it's late at night here.

However , after seeing your revised file , I think it's not necessary , since you have ensured that nothing can be entered in the cells corresponding to "Nolist". Good for you.

Narayan

GioP · Jul 12, 2012

Protection of inactive cells in column “F” makes sense, because in original file this “PB” worksheet is password protected where “Select locked cells” is deactivated. In other words user only moves from one to another active cell using “Tab” button. In case of “Nolist” it will be great if inactive cell becomes locked to skip and pushing “Tab” user will jump to next active cell in row.

Thanks in advance once again and regards.

Getting late here too

GioP

NARAYANK991 · Jul 13, 2012

Hi ,

Is this something like what you are looking for ?

[pre]

Code:

Public Sub Lock_cells()
Dim data_range As Range
Set data_range = ThisWorkbook.Worksheets(&#34;PB&#34;).Range(&#34;Table3_Range&#34;).Resize(, 1)
ThisWorkbook.Worksheets(&#34;PB&#34;).Activate
ActiveSheet.Unprotect
data_range.Select
For Each cell In Selection
If cell.Offset(0, 4).Value = &#34;&#34; Then
cell.Offset(0, 3).Locked = True
End If
Next
ActiveSheet.Protect
ActiveSheet.ScrollArea = data_range.Offset(0, 3).Resize(Range(&#34;Table3&#34;).Rows.Count).Address
Set data_range = Nothing
End Sub

[/pre]
Narayan

GioP · Jul 13, 2012

Hi again

I copied the code and got the following error massage

Application-defined or object-defined error

Then I changed code as shown below:

Public Sub Lock_cells()

Dim data_range As Range

Set data_range = ThisWorkbook.Worksheets("PB").Range("Table3").Resize(, 5)

ThisWorkbook.Worksheets("PB").Activate

ActiveSheet.Unprotect

data_range.Select

For Each cell In Selection

If cell.Offset(0, 4).Value = "" Then

cell.Offset(0, 4).Locked = True

End If

Next

ActiveSheet.Protect

ActiveSheet.ScrollArea = data_range.Offset(0, 4).Resize(Range("Table3").Rows.Count).Address

Set data_range = Nothing

End Sub

As a result the code is working, however there are two elements to consider

1) The “table3” becomes selected on the screen

2) Unable to select any previous unlocked cells in vertical. For example in column “C” if your current selection is cell “C20” you cannot select cell "C15" unless you keep pushing “Tab” to get to the end to start from the beginning.

Any solution for this matters? In original file there are more the 10 active columns

Thanks a lot and sorry for troubling you with my questions due to my Zoro knowledge in VBA

GioP

NARAYANK991 · Jul 13, 2012

Hi ,

If you will be using columns other than the drop-down column ( column F ) , then remove the following statement from your procedure :

[pre]

Code:

ActiveSheet.ScrollArea = data_range.Offset(0, 4).Resize(Range(&#34;Table3&#34;).Rows.Count).Address

[/pre]
Narayan

GioP · Jul 13, 2012

Dear Narayan

Step by step I’m getting closer to my goal but still to go. It is obvious without your contribution it may not work or will take me ages. &#62

I followed your advice and can confirm that code is working. I tried to copy the same code in the original file but unfortunately it did give me the same effect.

This is my case in main file Table2 in “PB” worksheet is the range(B14:X313)

Following columns in the mentioned range are not for entries and locked by default:

I, L, N, P, T, W,

These columns contain “vlookup” formulas can be ignored.

Following columns are set for data entries and by default are unlocked:

B, C, J, K, M, O, Q, R, S, U, X,

No-List or condition “” applies to columns:

J, K, M, O, Q,

Here is where my brain started boiling, how to apply VBA-"lock cell" to “J, K, M, O, Q,” columns on “” principle as it worked in simplified case file.

P.S.

Life in Excel is hard on beginners and sure any help is appreciated,

For main file I made following changes in the code

Public Sub Lock_cells()

Dim data_range As Range

Set data_range = ThisWorkbook.Worksheets("PB").Range("B14:X313").Resize(, 23)

ThisWorkbook.Worksheets("PB").Activate

ActiveSheet.Unprotect

Rem data_range.Select

For Each cell In Selection

If cell.Offset(0, 10).Value = "" Then

cell.Offset(0, 10).Locked = True

End If

Next

ActiveSheet.Protect

Rem ActiveSheet.ScrollArea = data_range.Offset(0, 3).Resize(Range("Table2").Rows.Count).Address

Set data_range = Nothing

End Sub

Giop

NARAYANK991 · Jul 13, 2012

Hi Giop ,

Can you clarify one point ?

You say that columns J , K , M , O and Q are unlocked to start with , but should get locked ; which column(s) should have "Nolist" or "" for these 5 columns to be locked ?

Anyway , if the range starts with column B , and extends till column X , the numbering for the offset is as follows : column C is offset 1 from column B ; so columns J , K , M , O and Q will be offset 8 , 9 , 11 , 13 and 15 from column B. You will need to have 5 statements for these 5 columns as follows :

[pre]

Code:

cell.Offset(0, 8).Locked = True
cell.Offset(0, 9).Locked = True
cell.Offset(0, 11).Locked = True
cell.Offset(0, 13).Locked = True
cell.Offset(0, 15).Locked = True

[/pre]
Narayan

GioP · Jul 13, 2012

Hi

Point is that if in any cell of the columns (J, K, M, O, Q,) value is “Nolist”=”” then the cells have to be locked. In the mentioned columns drop down lists are defined based on selected account code in column “C”.

If I got it right, setting offset to lock cells in columns for (J, K, M, O, Q,) shall require 5 if statements too?

GioP

NARAYANK991 · Jul 13, 2012

Hi Giop ,

You are right. You will need 5 sets of statements like the following :

[pre]

Code:

If cell.Offset(0, 10).Value = &#34;&#34; Then
cell.Offset(0, 10).Locked = True
End If

[/pre]
The 10 will be replaced in each of them by 8 , 9 , 11 , 13 and 15.

Is it possible for you to upload your actual workbook , not just a sample one ? Or at least your PB worksheet , since that is where the action is ?

Narayan

GioP · Jul 13, 2012

Hi Narayan

Sure I will upload the file and let you know when done

Just give me some time to smoke and to have a coffee otherwise my eyes are like...

Regards

Do you smoke?

NARAYANK991 · Jul 13, 2012

Hi GioP ,

Go ahead and relax. Here it is bedtime ! I'll check out your posts tomorrow morning. Have a nice day.

Narayan

GioP · Jul 13, 2012

Here is the link

https://docs.google.com/open?id=0B4mitJ-SIt9bU2NsUUczWG5uRHc

I modified the code and it looks like it is working, but when examining I have noticed that code fails starting from row 19 in “PB” worksheet. It starts jumping between B and C columns

Any idea? Possible to correct?

Frankly spiking to have this file complete I need to accomplish some additional tasks.

For sure question is if you find it challenging and the same time interesting quest in my struggle to build this template

?

VBA code 1:

In sheet “PB” column “C” is set as drop down list for account codes.

Cell “K9” defines currency in use

VBA Code should lime entry into any cell in column “C” according to the following limitations:

If “§K§9” = “USD” then

Value in any cell should be:

(<=1750 and >=1800) or (<=1850 and >=1900)

Else

(<=1700 and >=1749) or (<=1800 and >=1849)

VBA code 2:

Once data entry is finished in sheet “PB” then by single click on “Command Button” entries from “PB” worksheet should be copied into sheet “Result”

Where results of the following columns (B, C, J, K, M, O, P, Q, R, S, U, V, W, X) should be copied starting from row 10, (top 10 rows will be used as header)

Coping should be limited to last entry from source table.

VBA code 3:

In case any VBA code fails and visual basic runs beforehand user should be asked to enter password,

Is it possible to set password for visual basic or to avoid running in case of code failure?

To unprotect sheet password is: 1234

Biggggg thankssssss again.

VBA Drop down list based on dynamic Lists dependent on ref list attributes

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member