Unable to use INDIRECT function in VB - Please help for new user to VB

[Dan Pend]

Hi all,

I'm just beginning my journey into Visual Basic and am having trouble linking a function that I am able to do in excel, but cannot figure how to assign it to a macro (I've changed the headings to something able to post).

I have a lists created in 'Descriptions' with the following:

• List called Colour, with range of $A$2:$A$6
• List called Red, with range of $B$2:$D$2
• List called Blue, with range of $B$3:$E$3
• List called Green, with range of $B$4:$E$4
• List called Brown, with range of $B$5:$C$5
• List called Purple, with range of $B$6
• List called Location, with range of $A$8:$A$10

In another worksheet ('Colour_Collection) I am using as a collection tool and has a macro called CommandButton1_Click which I wold like to add a dependant drop-down list.

I am looking to having this command button:

• Add a new line along row 6
• Add date in B6
• Add list 'Location' in C6
• Add list 'Colour' in D6
• Add dependant drop down list of the colour ranges above (ie shades of red etc). I am able to manually achieve this through using the ValidateList=INDIRECT(D6) function but not able to figure out how to achieve this through visual basic.

I have included the file and also listed the manual code. I am really new to VB and looking at learning as much as possible from the group, the solution is probably something easy, and I'm looking at using CASE statements or something, but though it might be alot quicker to put it out there for any input from knowledgeable people. Any assistance would be greatly appreciated in the short term.

Thanks,
Dan

 

[NARAYANK991]

Hi Dan ,

Instead of the statement that you have , try this statement :

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=IF(D6="""",INDIRECT(""D6""),INDIRECT(D6))"

Narayan

 

[Dan Pend]

Thank you Narayan,

The statement worked exactly as I had hoped. Using the IF statement is something I am now going to research into a little more.

Once again thanks,
Dan