Creating Dropdown List with VBA

[PipBoy808]

Hello people of Chandoo,

I have data that will vary in size over time. The data always starts in row 12, and I know that I'll need a dropdown list containing different addresses in column E every time. So, I have a previously defined 'lastrow' variable to help me select the data exactly:

'Including a dropdown list in column E
Range(Cells(12, 5), Cells(lastRow, 5)).Select
'Insert dropdown list here

Can anyone help me with inserting a dropdown list from here on? Thanking you in advance!

 

[Abhijeet R. Joshi]

Will the below help?

drc = Range("E" & Application.Rows.Count).End(xlUp).Row
  With Selection.Validation
  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  xlBetween, Formula1:="=$E$12:$E$" & drc
  .IgnoreBlank = True
  .InCellDropdown = True
  .InputTitle = "Select Address"
  .ErrorTitle = ""
  .InputMessage = "Invalid Entry"
  .ErrorMessage = "Invalid!! Try Again"
  .ShowInput = True
  .ShowError = True
  End With
End Sub

 

[PipBoy808]

This did work! However, it has created a separate problem.

My code is now as follows:

Sub Dropdown()
Dim Rowcounter As Long
Dim lastrow As Long
 
For Rowcounter = 1 To 300 'Last row of DSV data
If Cells(11 + Rowcounter, 2).Value = "" Then
lastrow = Rowcounter + 10
Exit For
End If
Next Rowcounter
 
Range(Cells(12, 5), Cells(lastrow, 5)).WrapText = True 'wrap text so the addresses all fit
With Range(Cells(12, 5), Cells(lastrow, 5)).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Super Mega Long Address 1 Netherlands The World,Super Mega Long Address 2 Netherlands The World,Super Mega Long Address 3 Netherlands The World,Super Mega Long Address 4 Netherlands The World,Super Mega Long Address 5 Netherlands The World"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

This places a tidy little dropdown list in column E of my data with different addresses as options. The problem occurs when I try to reopen the file. Excel 2013 claims to find:

"...a problem with some content. Do you want us to recover as much as possible?"

and then gives me a list of 'repairs' it has done, namely

"Removed Feature: Data validation from /xl/worksheets/sheet1.xml part"

and sure enough my dropdown list is gone! Is there any reason Excel doesn't like this code, or is there perhaps a setting I could change to make it accept the code in future? Thanking you in advance!

 

[NARAYANK991]

Hi ,

I am not so sure that this is restricted to Excel 2013 ; from what I have seen Googling , this has something to do with the 255 character limit for the string ; since your string exceeds this limit , this may be the problem.

Can you check it out by reducing the length of the string below 256 characters ?

Narayan

 

[PipBoy808]

It could quite possibly be that Naranyan. Although, I haven't officially declared a string within the code. Is it the case that xl declares the dropdown list as a string by default? I unfortunately can't make the list any shorter, so if anyone knows of an alternative way to incorporate this, then I'd be most grateful.

EDIT: This is the definite cause of it. Good research! My addresses are simply too long to be written into the VBA. Given that I can't just change address, is there any way around this string character limit?