Hello to all

I have a problem
When i create an excel file with some dropdownlists in my vb.net page the dropdownlist in the excel file doesnt ignore balnks even with the ignore blank property set to true

best regards

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Hi,

Are you using a datasource to provide the dropdown values? Could the values be either NULL values or spaces?

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

they are empty cels

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Hi could you post the code you use to populate the lists?

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

With oSheet.Range(oSheet.Cells(2, j), oSheet.Cells(100, j)).Validation

                            .Add(Type:=Excel.XlDVType.xlValidateList, AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, Operator:=Excel.XlFormatConditionOperator.xlBetween, Formula1:="=APOIO!$A$2:$A$100")


                        .IgnoreBlank = True

                    End With
Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Hi

Try changing the formula part of your code to:
Formula1:="=Trim(APOIO!$A$2:$A$100)"

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Nope it gives me an exception
Excepção de HRESULT: 0x800A03EC

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

hmmm, I think it is picking up space characters as values...
I may have the formula wrong it could be : Formula1: ="=APOIO!$A$":$A$100.TEXT.TRIM"

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Nope same exception

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Drat! I was sure you could carry out a trim on a range...

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Ye i thought so too but its not possible forsome reasn

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Hi,
I think we are trying to do too much on one line of code and screwing up the syntax. So how about this?

'Tidy up your source data first 
dim SourceRange = oWorkbook.Sheets("APOIO").CELLS(A1:A100)
   SourceRange.Text = SourceRange.Text.Trim
'Now use the tidied data to populate the dropdown   
With oSheet.Range(oSheet.Cells(2, j), oSheet.Cells(100, j)).Validation
   .Add(Type:=Excel.XlDVType.xlValidateList, AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, Operator:=Excel.XlFormatConditionOperator.xlBetween, Formula1:="=APOIO!$A$2:$A$100")
   .IgnoreBlank = True
End With
Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

nope that does not work either .
i amtrying a diferent aproach i will use gembox
but is there a wa in gembox to do this

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

???

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

Never used gembox...

Re: dropdownlist for excel file created in vb.net doesnt ignore blanks 80 80

There are multiple ways in which you can create a dropdown list in Excel.
Data Validation
Form Control (Combo Box)
ActiveX Control (Combo Box)
But of these methods, Data Validation is the most used method, for its simplicity. Later on, I’ll explain the pros and cons of each method, so that you can choose the best method among these for you.

Before going to explain how to create a dropdown list, let me tell you what a Data Validation is? It controls the type of data or the values that users enter into a cell. For example, you may want to enter only positive values in a cell, only ‘time’ in a column, or a text below the specified length. You can define all these conditions through Data Validation. Even dropdown list is a data validation where you’re constraining the user to use only those set of words in that cell. Again, in creating a dropdown list through data validation, there are different ways in doing it.

Writing the complete list in the Source Field
1) Select the Cell (or) set of Cells in which you want to see the dropdown. 2) Go to “Data” Tab –> Data Validation –> Data Validation 3) Select List in the “Allow box” 4) Just enter the names you want to see in the dropdown list in the “Source” Box

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.