asked on

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

ccravenbartle

ASKER

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

Rory Archibald

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.

ccravenbartle

ASKER

I set B4 to the first of my defined validation values - it is not empty.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ccravenbartle

ASKER

Thank you for your help. I did not have the content of B4 set to a static named range.