Hi,
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.
Please advice.
Regards
Hi,
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,
Hi,
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).
Sample code:
[C#]
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";
range[0, 0].PutValue("Administrator");
range[1, 0].PutValue("User");
range[2, 0].PutValue("Guest");
range[3, 0].PutValue("Clerk");
worksheet2.IsVisible = false;
//Get the first default worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];
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
CellArea area;
area.StartRow = 0;
area.EndRow = 3;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AreaList.Add(area);
workbook.Save("f:\\test\\list_validtest1.xls");
[VB]
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"
range(0, 0).PutValue("Administrator")
range(1, 0).PutValue("User")
range(2, 0).PutValue("Guest")
range(3, 0).PutValue("Clerk")
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
validation.AreaList.Add(area)
workbook.Save("f:\test\list_validtest1.xls")]
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";
range[0, 0].PutValue("Administrator");
range[1, 0].PutValue("User");
range[2, 0].PutValue("Guest");
range[3, 0].PutValue("Clerk");
worksheet2.IsVisible = false;
//Get the first default worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];
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
CellArea area;
area.StartRow = 0;
area.EndRow = 3;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AreaList.Add(area);
workbook.Save("f:\\test\\list_validtest1.xls");
[VB]
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"
range(0, 0).PutValue("Administrator")
range(1, 0).PutValue("User")
range(2, 0).PutValue("Guest")
range(3, 0).PutValue("Clerk")
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
validation.AreaList.Add(area)
workbook.Save("f:\test\list_validtest1.xls")]
Hopefully it may help you for your requirements.
Thank you.
Yes, this is exactly what i wanted.
Thanks heaps