Excel VB.Net Conditional Dropdown Validation problem

In my VB.Net application, I am generating Excel spreadsheets with conditional drop down validation.

Cell B4 is a drop down list of named ranges, for example Europe{UK, France, Germany}, Asia{India, Pakistan, China}, Africa{Kenya, Libya, Uganda}.  The drop down cells from E7 to I107 pick up the values depending on the selection made in B4 so that if B4 is set to Asia then the drop down list for all the cells from E7 to I107 show India, Pakistan, China.

I've tested the functionality by manually creating the validation drop downs in Excel and recording Macros to help me write the VB.Net code.  However, when I execute the VB code the Formula1:="=INDIRECT($B$4") reference  is raising a  general Excel exception error.  If I take out INDIRECT it executes but the dropdowns do not pick up the range of values.


Dim _UnitRange As Excel.Range
        _UnitRange = osheet.Range("E7:I107")
        _UnitRange.HorizontalAlignment = Excel.Constants.xlCenter
        With _UnitRange.Cells.Validation
            .Delete()
            .Add(XlDVType.xlValidateList, AlertStyle:=XlDVAlertStyle.xlValidAlertStop, Operator:=XlFormatConditionOperator.xlBetween, Formula1:="=INDIRECT($B$4"))
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
ccravenbartleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hi ccravenbartle,

If that is your real code, then you are closing the quotes on the Indirect  too early. Try this

            .Add(XlDVType.xlValidateList, AlertStyle:=XlDVAlertStyle.xlValidAlertStop, Operator:=XlFormatConditionOperator.xlBetween, Formula1:="=INDIRECT($B$4)")


cheers, teylyn
0
ccravenbartleAuthor Commented:
Hi Teylyn

Sorry, I made a typing error when I was editing the question.  The program code is exactly the same as yours.  When I execute it I get "Exception from HRESULT: 0x800A03EC"

For your information, I based my code on this is the Macro I recorded :

Sub Macro1()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT($B$4)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

and this is the actual VB.Net code :

 Dim _UnitRange As Excel.Range
        _UnitRange = osheet.Range("E7:I107")
        _UnitRange.HorizontalAlignment = Excel.Constants.xlCenter
        With _UnitRange.Cells.Validation
            .Delete()
            .Add(XlDVType.xlValidateList, AlertStyle:=XlDVAlertStyle.xlValidAlertStop, Operator:=XlFormatConditionOperator.xlBetween, Formula1:="=INDIRECT($B$4)")
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Sorry, that was the one glaring thing that sprang to mind from an Excel perspective. For me, .NET is a framework I need to install so I can run certain apps on my lappy, but not something I can develop with.

Others will need to step in.

cheers, teylyn
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Rory ArchibaldCommented:
What is in B4 when you add the validation? I suspect that the validation formula is evaluating to an error, which is causing your problem.
0
ccravenbartleAuthor Commented:
I set B4 to the first of my defined validation values - it is not empty.


0
Rory ArchibaldCommented:
B4 needs to contain either the name of a static named range (can't be dynamic) or an address string. Is that the case?
Also, if your sheet is protected, you will get an error.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ccravenbartleAuthor Commented:
Thank you for your help.  I did not have the content of B4 set to a static named range.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.