Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
So the list is in a different workbook, right?
If that's true, then I'd start he 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
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 he 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
Thanks Dave! That works, but only for the first cell. Why isn't it copying
for the rest of the range (from N4 down)? Thanks! "Dave Peterson" wrote: So the list is in a different workbook, right? If that's true, then I'd start he 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel combo list in VBA
It worked ok for me.
But I did change the way that range was obtained. Instead of starting with N4 and working down, I anchored at N4, but worked my way up from the bottom. With .Range("N4", .Cells(.Rows.Count, "N").End(xlUp)) You may want: With .Range("N4", .Range("N4").end(xldown)) In fact, if you don't know how many rows you need, but you want to match the number of used rows in a different column, you may want: With .Range("N4:N" & .Cells(.Rows.Count, "A").End(xlUp).Row) This used column A to determine the last row (starting from the bottom up <vbg). Nico wrote: Thanks Dave! That works, but only for the first cell. Why isn't it copying for the rest of the range (from N4 down)? Thanks! "Dave Peterson" wrote: So the list is in a different workbook, right? If that's true, then I'd start he 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo List in Excel | Excel Worksheet Functions | |||
Excel combo box - value list? | Excel Programming | |||
Filtered list for Combo Box ListFillRange - Nested Combo Boxes | Excel Programming | |||
Creating an Autoexpand in Excel Combo Box | Excel Programming | |||
Sorting Excel combo box list | Excel Programming |