How to validate dropdown using Aspose.Cells.Validation

Aspose.Cells Product Family
1

Hi,

In Excel I have a dropdown called Commodity which contains gas,power,electricity etc

Next to this dropdown, I have one more dropdown called Units which contains kwh, kw, mwh etc.

Now my requirement is,

If I select gas from dropdown1, the 2nd dropdown should show only kwh and not other values.

and If I select electricity from dropdown1, the 2nd dropdown should display kw and mwh and not kwh.

How can I achieve this functionality using Aspose 5.0??

Require your help on this as sooon as possible please.

2

Hi,


How could you do this in MS Excel? Please create a sample Excel file (in Excel) in which you provide two drop downs each is linked with other, so that if a value is selected from one drop down, the corresponding value is selected in other drop down. Attach the file here, we will check how to do it with Aspose.Cells product.

Thank you.
3

Hi Amjad,

Using Aspose.Cells.Validation, we can find if any wrong value is entered in dropdown like the code below.

Dim valFactor As Aspose.Cells.Validation

valFactor = WorkSheet.Validations(WorkSheet.Validations.Add())

valFactor.Type = Aspose.Cells.ValidationType.List

valFactor.Operator = Aspose.Cells.OperatorType.None

valFactor.InCellDropDown = True

valFactor.ShowError = True

valFactor.AlertStyle = Aspose.Cells.ValidationAlertType.Stop

valFactor.ErrorTitle = "Error"

valFactor.ErrorMessage = "Please select a Unit from the list"

valFactor.Formula1 = String.Join("kwh,mwh,kh")

area = New Aspose.Cells.CellArea

cell = GetCellFromEnum(Of CellPosition)(CellPosition.VolumeUnitValue)

area.StartColumn = cell.Column

area.EndColumn = cell.Column

area.StartRow = cell.Row

area.EndRow = cell.Row

valFactor.AreaList.Add(area)

This code throws an error message if we enter some other value other than "kwh,mwh,kh".

In the same way, Is there any possible way that we can validate dropdown in excel using Aspose.

No Idea of doing in MSExcel. Please provide me some solution.

4

Hi,


I don’t think it is possible directly in MS Excel. Aspose.Cells follows MS Excel standards, so it cannot perform either.

Thanks,
5

Hi,

You have to use Macro in your template file.

6

Hi mshakeel,

Thanks for your reply.

Could you please tell me detailly as how to implement this since I dont have any idea on how to do this..

Please do explain me in detail.

Am using VB.Net as code behind.

7

Hi,

In order to create a Macro, you need to learn Ms-Excel Macro Script, it is also called VBA (Visual Basic for Application).

The script is based on Visual Basic language.

It will give you a control to access your controls and validate them.

I am afraid, you will have to learn VBA from internet and use it for your own needs.