Hello,

I'm trying to create a combo list in Excel with the following VBA code, but
it cuts off after only eight lines. Any suggestions?

Columns("N:N").Select
With Columns("N:N")
Selection.Locked = False
.Validation.Delete
.Validation.Add xlValidateList, , , "Example 1, Example 2, Example
3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
10, Example 11, Example 12, Example 13, Example 14"
Cells.EntireColumn.AutoFit
End With

Unfortunately it also cuts off some lines (here listed as "Example X") which
are long. Is there a character limit? I can't find any reference to one, but
I'm not sure why else it would cut off?

Thanks!

Is there a slide bar in the right hand side of the combobox? I get only 8
values until I slide the bar to see the other values.

"Nico" wrote:

> Hello,
>
> I'm trying to create a combo list in Excel with the following VBA code, but
> it cuts off after only eight lines. Any suggestions?
>
> Columns("N:N").Select
> With Columns("N:N")
> Selection.Locked = False
> .Validation.Delete
> .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> 10, Example 11, Example 12, Example 13, Example 14"
> Cells.EntireColumn.AutoFit
> End With
>
> Unfortunately it also cuts off some lines (here listed as "Example X") which
> are long. Is there a character limit? I can't find any reference to one, but
> I'm not sure why else it would cut off?
>
> Thanks!

No, it cuts off at eight, mid-sentance on the eighth. I can't scroll further.

"joel" wrote:

> Is there a slide bar in the right hand side of the combobox? I get only 8
> values until I slide the bar to see the other values.
>
> "Nico" wrote:
>
> > Hello,
> >
> > I'm trying to create a combo list in Excel with the following VBA code, but
> > it cuts off after only eight lines. Any suggestions?
> >
> > Columns("N:N").Select
> > With Columns("N:N")
> > Selection.Locked = False
> > .Validation.Delete
> > .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> > 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> > 10, Example 11, Example 12, Example 13, Example 14"
> > Cells.EntireColumn.AutoFit
> > End With
> >
> > Unfortunately it also cuts off some lines (here listed as "Example X") which
> > are long. Is there a character limit? I can't find any reference to one, but
> > I'm not sure why else it would cut off?
> >
> > Thanks!

This is the code I used. the only chbanges I did was to break the string
into multiple lines. I wondering if the problem has something to do with the
length of the string. I'm using excel 2003, don't know if there is a problem
with excel 2007.

Sub test()

Columns("N:N").Select
With Columns("N:N")
Selection.Locked = False
.Validation.Delete
.Validation.Add xlValidateList, , , _
"Example 1, Example 2, Example 3," & _
"Example 4, Example 5, Example 6," & _
"Example 7, Example 8, Example 9," & _
"Example 10, Example 11, Example 12," & _
"Example 13, Example 14"
Cells.EntireColumn.AutoFit
End With
End Sub

"Nico" wrote:

> No, it cuts off at eight, mid-sentance on the eighth. I can't scroll further.
>
> "joel" wrote:
>
> > Is there a slide bar in the right hand side of the combobox? I get only 8
> > values until I slide the bar to see the other values.
> >
> > "Nico" wrote:
> >
> > > Hello,
> > >
> > > I'm trying to create a combo list in Excel with the following VBA code, but
> > > it cuts off after only eight lines. Any suggestions?
> > >
> > > Columns("N:N").Select
> > > With Columns("N:N")
> > > Selection.Locked = False
> > > .Validation.Delete
> > > .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> > > 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> > > 10, Example 11, Example 12, Example 13, Example 14"
> > > Cells.EntireColumn.AutoFit
> > > End With
> > >
> > > Unfortunately it also cuts off some lines (here listed as "Example X") which
> > > are long. Is there a character limit? I can't find any reference to one, but
> > > I'm not sure why else it would cut off?
> > >
> > > Thanks!

Thanks, I just tried that, but I'm still getting the same incomplete list.

"joel" wrote:

> This is the code I used. the only chbanges I did was to break the string
> into multiple lines. I wondering if the problem has something to do with the
> length of the string. I'm using excel 2003, don't know if there is a problem
> with excel 2007.
>
> Sub test()
>
> Columns("N:N").Select
> With Columns("N:N")
> Selection.Locked = False
> .Validation.Delete
> .Validation.Add xlValidateList, , , _
> "Example 1, Example 2, Example 3," & _
> "Example 4, Example 5, Example 6," & _
> "Example 7, Example 8, Example 9," & _
> "Example 10, Example 11, Example 12," & _
> "Example 13, Example 14"
> Cells.EntireColumn.AutoFit
> End With
> End Sub
>
> "Nico" wrote:
>
> > No, it cuts off at eight, mid-sentance on the eighth. I can't scroll further.
> >
> > "joel" wrote:
> >
> > > Is there a slide bar in the right hand side of the combobox? I get only 8
> > > values until I slide the bar to see the other values.
> > >
> > > "Nico" wrote:
> > >
> > > > Hello,
> > > >
> > > > I'm trying to create a combo list in Excel with the following VBA code, but
> > > > it cuts off after only eight lines. Any suggestions?
> > > >
> > > > Columns("N:N").Select
> > > > With Columns("N:N")
> > > > Selection.Locked = False
> > > > .Validation.Delete
> > > > .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> > > > 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> > > > 10, Example 11, Example 12, Example 13, Example 14"
> > > > Cells.EntireColumn.AutoFit
> > > > End With
> > > >
> > > > Unfortunately it also cuts off some lines (here listed as "Example X") which
> > > > are long. Is there a character limit? I can't find any reference to one, but
> > > > I'm not sure why else it would cut off?
> > > >
> > > > Thanks!

I can't find reference to it anywhere, but the list must be capped at 255
characters, because that's where it's cutting off.

I'm trying a workaround by extracting the list from another spreadsheet, but
I'm receiving a whole new error: it won't find the list:

Windows(Master).Activate
Sheets("ResolutionCodesEN").Select
Range("A1").Select
ActiveWorkbook.Names.Add Name:="ResponseList",
RefersToR1C1:="=ResolutionCodesEN!R1C1:R16C1"
Sheets("BranchEN").Select
Windows(TransClustFile).Activate

'Inserts drop down list for New Resolution Code
Range("N4").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=ResponseList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.ErrorMessage = "Please select from the drop down menu"
.ShowInput = True
.ShowError = True
End With

Any idea where I'm going wrong?

Thanks!

"joel" wrote:

> This is the code I used. the only chbanges I did was to break the string
> into multiple lines. I wondering if the problem has something to do with the
> length of the string. I'm using excel 2003, don't know if there is a problem
> with excel 2007.
>
> Sub test()
>
> Columns("N:N").Select
> With Columns("N:N")
> Selection.Locked = False
> .Validation.Delete
> .Validation.Add xlValidateList, , , _
> "Example 1, Example 2, Example 3," & _
> "Example 4, Example 5, Example 6," & _
> "Example 7, Example 8, Example 9," & _
> "Example 10, Example 11, Example 12," & _
> "Example 13, Example 14"
> Cells.EntireColumn.AutoFit
> End With
> End Sub
>
> "Nico" wrote:
>
> > No, it cuts off at eight, mid-sentance on the eighth. I can't scroll further.
> >
> > "joel" wrote:
> >
> > > Is there a slide bar in the right hand side of the combobox? I get only 8
> > > values until I slide the bar to see the other values.
> > >
> > > "Nico" wrote:
> > >
> > > > Hello,
> > > >
> > > > I'm trying to create a combo list in Excel with the following VBA code, but
> > > > it cuts off after only eight lines. Any suggestions?
> > > >
> > > > Columns("N:N").Select
> > > > With Columns("N:N")
> > > > Selection.Locked = False
> > > > .Validation.Delete
> > > > .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> > > > 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> > > > 10, Example 11, Example 12, Example 13, Example 14"
> > > > Cells.EntireColumn.AutoFit
> > > > End With
> > > >
> > > > Unfortunately it also cuts off some lines (here listed as "Example X") which
> > > > are long. Is there a character limit? I can't find any reference to one, but
> > > > I'm not sure why else it would cut off?
> > > >
> > > > Thanks!

Is there a reason you don't put the list in a range on a worksheet?

Nico wrote:
>
> Hello,
>
> I'm trying to create a combo list in Excel with the following VBA code, but
> it cuts off after only eight lines. Any suggestions?
>
> Columns("N:N").Select
> With Columns("N:N")
> Selection.Locked = False
> .Validation.Delete
> .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> 10, Example 11, Example 12, Example 13, Example 14"
> Cells.EntireColumn.AutoFit
> End With
>
> Unfortunately it also cuts off some lines (here listed as "Example X") which
> are long. Is there a character limit? I can't find any reference to one, but
> I'm not sure why else it would cut off?
>
> Thanks!

--

Dave Peterson

I tried that, but I'm still getting an error. Perhaps you can see where I'm
going wrong?

[...]

Windows(Master).Activate
Sheets("ResolutionCodesEN").Select
Range("A1").Select
ActiveWorkbook.Names.Add Name:="ResponseList",
RefersToR1C1:="=ResolutionCodesEN!R1C1:R16C1"
Sheets("BranchEN").Select
Windows(TransClustFile).Activate

'Inserts drop down list for New Resolution Code
Range("N4").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=ResponseList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.ErrorMessage = "Please select from the drop down menu"
.ShowInput = True
.ShowError = True
End With

[...]

Thanks!

"Dave Peterson" wrote:

> Is there a reason you don't put the list in a range on a worksheet?
>
> Nico wrote:
> >
> > Hello,
> >
> > I'm trying to create a combo list in Excel with the following VBA code, but
> > it cuts off after only eight lines. Any suggestions?
> >
> > Columns("N:N").Select
> > With Columns("N:N")
> > Selection.Locked = False
> > .Validation.Delete
> > .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> > 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> > 10, Example 11, Example 12, Example 13, Example 14"
> > Cells.EntireColumn.AutoFit
> > End With
> >
> > Unfortunately it also cuts off some lines (here listed as "Example X") which
> > are long. Is there a character limit? I can't find any reference to one, but
> > I'm not sure why else it would cut off?
> >
> > Thanks!
>
> --
>
> Dave Peterson
>

So the list is in a different workbook, right?

If that's true, then I'd start here:
http://contextures.com/xlDataVal05.html

Option Explicit
Sub testme()

Dim Master As String
Dim TransClustFile As String
Dim myRng As Range
Dim myMasterName As String
Dim myListName As String

Master = "book1.xls"
TransClustFile = "Book2.xls"

myMasterName = "ResponseList"
myListName = "myList"

With Workbooks(Master).Worksheets("ResolutionCodesEN")
'in case the range can grow
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
myRng.Name = "ResponseList"
End With

With Workbooks(TransClustFile).Worksheets("sheet1")
.Names.Add Name:="MyList", _
RefersTo:="='" & Master & "'!" & myMasterName

With .Range("N4", .Cells(.Rows.Count, "N").End(xlUp))
With .Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & myListName
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.ErrorMessage = "Please select from the drop down menu"
.ShowInput = True
.ShowError = True
End With
End With
End With

End Sub





Nico wrote:
>
> I tried that, but I'm still getting an error. Perhaps you can see where I'm
> going wrong?
>
> [...]
>
> Windows(Master).Activate
> Sheets("ResolutionCodesEN").Select
> Range("A1").Select
> ActiveWorkbook.Names.Add Name:="ResponseList",
> RefersToR1C1:="=ResolutionCodesEN!R1C1:R16C1"
> Sheets("BranchEN").Select
> Windows(TransClustFile).Activate
>
> 'Inserts drop down list for New Resolution Code
> Range("N4").Select
> Range(Selection, Selection.End(xlDown)).Select
> With Selection.Validation
> .Delete
> .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
> xlBetween, Formula1:="=ResponseList"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = "Error"
> .ErrorMessage = "Please select from the drop down menu"
> .ShowInput = True
> .ShowError = True
> End With
>
> [...]
>
> Thanks!
>
> "Dave Peterson" wrote:
>
> > Is there a reason you don't put the list in a range on a worksheet?
> >
> > Nico wrote:
> > >
> > > Hello,
> > >
> > > I'm trying to create a combo list in Excel with the following VBA code, but
> > > it cuts off after only eight lines. Any suggestions?
> > >
> > > Columns("N:N").Select
> > > With Columns("N:N")
> > > Selection.Locked = False
> > > .Validation.Delete
> > > .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> > > 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> > > 10, Example 11, Example 12, Example 13, Example 14"
> > > Cells.EntireColumn.AutoFit
> > > End With
> > >
> > > Unfortunately it also cuts off some lines (here listed as "Example X") which
> > > are long. Is there a character limit? I can't find any reference to one, but
> > > I'm not sure why else it would cut off?
> > >
> > > Thanks!
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Change this line:
myRng.Name = "ResponseList"
to
myRng.Name = myMasterName

I wanted to use variables so that the code would be easier to change, but missed
this one.

Dave Peterson wrote:
>
> So the list is in a different workbook, right?
>
> If that's true, then I'd start here:
> http://contextures.com/xlDataVal05.html
>
> Option Explicit
> Sub testme()
>
> Dim Master As String
> Dim TransClustFile As String
> Dim myRng As Range
> Dim myMasterName As String
> Dim myListName As String
>
> Master = "book1.xls"
> TransClustFile = "Book2.xls"
>
> myMasterName = "ResponseList"
> myListName = "myList"
>
> With Workbooks(Master).Worksheets("ResolutionCodesEN")
> 'in case the range can grow
> Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
> myRng.Name = "ResponseList"
> End With
>
> With Workbooks(TransClustFile).Worksheets("sheet1")
> .Names.Add Name:="MyList", _
> RefersTo:="='" & Master & "'!" & myMasterName
>
> With .Range("N4", .Cells(.Rows.Count, "N").End(xlUp))
> With .Validation
> .Delete
> .Add Type:=xlValidateList, _
> AlertStyle:=xlValidAlertStop, _
> Operator:=xlBetween, _
> Formula1:="=" & myListName
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = "Error"
> .ErrorMessage = "Please select from the drop down menu"
> .ShowInput = True
> .ShowError = True
> End With
> End With
> End With
>
> End Sub
>
> Nico wrote:
> >
> > I tried that, but I'm still getting an error. Perhaps you can see where I'm
> > going wrong?
> >
> > [...]
> >
> > Windows(Master).Activate
> > Sheets("ResolutionCodesEN").Select
> > Range("A1").Select
> > ActiveWorkbook.Names.Add Name:="ResponseList",
> > RefersToR1C1:="=ResolutionCodesEN!R1C1:R16C1"
> > Sheets("BranchEN").Select
> > Windows(TransClustFile).Activate
> >
> > 'Inserts drop down list for New Resolution Code
> > Range("N4").Select
> > Range(Selection, Selection.End(xlDown)).Select
> > With Selection.Validation
> > .Delete
> > .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
> > xlBetween, Formula1:="=ResponseList"
> > .IgnoreBlank = True
> > .InCellDropdown = True
> > .InputTitle = ""
> > .ErrorTitle = "Error"
> > .ErrorMessage = "Please select from the drop down menu"
> > .ShowInput = True
> > .ShowError = True
> > End With
> >
> > [...]
> >
> > Thanks!
> >
> > "Dave Peterson" wrote:
> >
> > > Is there a reason you don't put the list in a range on a worksheet?
> > >
> > > Nico wrote:
> > > >
> > > > Hello,
> > > >
> > > > I'm trying to create a combo list in Excel with the following VBA code, but
> > > > it cuts off after only eight lines. Any suggestions?
> > > >
> > > > Columns("N:N").Select
> > > > With Columns("N:N")
> > > > Selection.Locked = False
> > > > .Validation.Delete
> > > > .Validation.Add xlValidateList, , , "Example 1, Example 2, Example
> > > > 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example
> > > > 10, Example 11, Example 12, Example 13, Example 14"
> > > > Cells.EntireColumn.AutoFit
> > > > End With
> > > >
> > > > Unfortunately it also cuts off some lines (here listed as "Example X") which
> > > > are long. Is there a character limit? I can't find any reference to one, but
> > > > I'm not sure why else it would cut off?
> > > >
> > > > Thanks!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson