I have created a combo box on my excel sheet which should have the list of all roles from my system. I created a hidden sheet that has all the list of roles and then i specify that as the range of my combo box. The issue is I can now only select something from the list in the combo box, but cannot type in anything.
Is it possible to type into the combo box if i do not want to use the mouse to select a role also there should be a check, so that the typed in value is one amongst the list of roles in the hidden sheet.
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Thank you for considering Aspose.
Well, the option to manually type a combo box items is not present in MS Excel. As Aspose APIs are designed against the functionalities supported by MS Excel, I am afraid; your requirement can not be implemented using Aspose.Cells (as not implementable by MS Excel).
Thank You & Best Regards,
I think you may try to implement List validation if it suits your needs
Following is my sample code, kindly consult to it.
In the example, I have added a new worksheet, create a range in it and fill the range cells. Then, I made the worksheet invisible. Now, I added List validation in the first worksheet based on the range in the second worksheet. I defined the validation cellarea for it. You may select or enter valid data only into the A1:A4 cells (validation area only).
Workbook workbook = new Workbook();
//Add the second sheet.
int i = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets[i];
Range range = worksheet2.Cells.CreateRange("H1", "H4");
range.Name = "MyRange";
worksheet2.IsVisible = false;
//Get the first default worksheet.
Worksheet worksheet1 = workbook.Worksheets;
Validations validations = worksheet1.Validations;
Validation validation = validations[validations.Add()];
validation.Type = Aspose.Cells.ValidationType.List;
validation.Operator = OperatorType.None;
validation.InCellDropDown = true;
validation.Formula1 = "=MyRange";
validation.ShowError = true;
validation.AlertStyle = ValidationAlertType.Stop;
validation.ErrorTitle = "Error";
validation.ErrorMessage = "Please select/enter a valid country from the list";
//Create a validation cell area i.e.., A1:A4
area.StartRow = 0;
area.EndRow = 3;
area.StartColumn = 0;
area.EndColumn = 0;
Dim workbook As New Workbook()
'Add the second sheet.
Dim i As Integer = workbook.Worksheets.Add()
Dim worksheet2 As Worksheet = workbook.Worksheets(i)
Dim range As Range = worksheet2.Cells.CreateRange("H1", "H4")
range.Name = "MyRange"
worksheet2.IsVisible = False
'Get the first default worksheet.
Dim worksheet1 As Worksheet = workbook.Worksheets(0)
Dim validations As Validations = worksheet1.Validations
Dim validation As Validation = validations(validations.Add())
validation.Type = Aspose.Cells.ValidationType.List
validation.Operator = OperatorType.None
validation.InCellDropDown = True
validation.Formula1 = "=MyRange"
validation.ShowError = True
validation.AlertStyle = ValidationAlertType.Stop
validation.ErrorTitle = "Error"
validation.ErrorMessage = "Please select/enter a valid country from the list"
'Create a validation cell area i.e.., A1:A4
Dim area As CellArea
area.StartRow = 0
area.EndRow = 3
area.StartColumn = 0
area.EndColumn = 0
Yes, this is exactly what i wanted.