Unable to set a range into the Listbox.RowSource method or Listbox.List method
When the line reaches lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
I get the error:
When I try to replace it with:
lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
, I get the error:
I believe the name of the table is correct as shown in the image below:
Option Explicit
Dim filterRangeFoilProfile As Range, filteredRangeFoilProfile As Range
Private Sub cbxSupplier_AfterUpdate()
Dim Supplier_col As Long
lbxFoilInfoDisplay.RowSource = vbNullString
Supplier_col = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index
filterRangeFoilProfile.AutoFilter Field:=Supplier_col, Criteria1:=cbxSupplier.Text
On Error Resume Next
Set filteredRangeFoilProfile = Intersect(filterRangeFoilProfile, filterRangeFoilProfile.Offset(1, 0)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not filteredRangeFoilProfile Is Nothing Then
ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets("List_Box").Cells(2, 1).PasteSpecial
lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
'lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
End If
End Sub
User-Defined Functions (to add context):
1.)
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
2.)
Function TotalRowsCount(WBString As String, WorksheetNameString As String, TableNameString As String) As Long
Dim Total_columns As Long
Dim i As Long
Total_columns = Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns.Count
ReDim Rows_count(1 To Total_columns) As Long
For i = 1 To Total_columns
With Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns(i).Range
Rows_count(i) = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
Next i
TotalRowsCount = WorksheetFunction.Max(Rows_count)
End Function
3.)
Function TotalColumnsCount(WBString As String, WorksheetNameString As String) As Long
Dim lastColumn As Long
lastColumn = Workbooks(WBString).Worksheets(WorksheetNameString).Cells(1, Columns.Count).End(xlToLeft).Column
TotalColumnsCount = lastColumn
End Function
excel vba excel-vba listbox
add a comment |
When the line reaches lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
I get the error:
When I try to replace it with:
lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
, I get the error:
I believe the name of the table is correct as shown in the image below:
Option Explicit
Dim filterRangeFoilProfile As Range, filteredRangeFoilProfile As Range
Private Sub cbxSupplier_AfterUpdate()
Dim Supplier_col As Long
lbxFoilInfoDisplay.RowSource = vbNullString
Supplier_col = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index
filterRangeFoilProfile.AutoFilter Field:=Supplier_col, Criteria1:=cbxSupplier.Text
On Error Resume Next
Set filteredRangeFoilProfile = Intersect(filterRangeFoilProfile, filterRangeFoilProfile.Offset(1, 0)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not filteredRangeFoilProfile Is Nothing Then
ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets("List_Box").Cells(2, 1).PasteSpecial
lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
'lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
End If
End Sub
User-Defined Functions (to add context):
1.)
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
2.)
Function TotalRowsCount(WBString As String, WorksheetNameString As String, TableNameString As String) As Long
Dim Total_columns As Long
Dim i As Long
Total_columns = Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns.Count
ReDim Rows_count(1 To Total_columns) As Long
For i = 1 To Total_columns
With Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns(i).Range
Rows_count(i) = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
Next i
TotalRowsCount = WorksheetFunction.Max(Rows_count)
End Function
3.)
Function TotalColumnsCount(WBString As String, WorksheetNameString As String) As Long
Dim lastColumn As Long
lastColumn = Workbooks(WBString).Worksheets(WorksheetNameString).Cells(1, Columns.Count).End(xlToLeft).Column
TotalColumnsCount = lastColumn
End Function
excel vba excel-vba listbox
Can i suggest to do aDebug.Print "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
and see that your range gets generated properly? Do this before or instead of the problem line of course.
– DarXyde
Nov 21 '18 at 15:23
@DarXyde I gottblFoilInfoHelper!A1:K4
as the output i'm not sure if I did theRowSource
method correctly but I believe it is
– Pherdindy
Nov 21 '18 at 15:35
1
Try using the sheet name instead of the table name? ie:lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
– DarXyde
Nov 21 '18 at 15:48
@DarXyde Oh yes... That works didn't realize that was the issue. I had a worksheet namedTable
in the reference workbook I was using I confused it as a table. Thanks
– Pherdindy
Nov 21 '18 at 16:01
Glad i could help... :)
– DarXyde
Nov 21 '18 at 16:20
add a comment |
When the line reaches lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
I get the error:
When I try to replace it with:
lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
, I get the error:
I believe the name of the table is correct as shown in the image below:
Option Explicit
Dim filterRangeFoilProfile As Range, filteredRangeFoilProfile As Range
Private Sub cbxSupplier_AfterUpdate()
Dim Supplier_col As Long
lbxFoilInfoDisplay.RowSource = vbNullString
Supplier_col = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index
filterRangeFoilProfile.AutoFilter Field:=Supplier_col, Criteria1:=cbxSupplier.Text
On Error Resume Next
Set filteredRangeFoilProfile = Intersect(filterRangeFoilProfile, filterRangeFoilProfile.Offset(1, 0)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not filteredRangeFoilProfile Is Nothing Then
ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets("List_Box").Cells(2, 1).PasteSpecial
lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
'lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
End If
End Sub
User-Defined Functions (to add context):
1.)
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
2.)
Function TotalRowsCount(WBString As String, WorksheetNameString As String, TableNameString As String) As Long
Dim Total_columns As Long
Dim i As Long
Total_columns = Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns.Count
ReDim Rows_count(1 To Total_columns) As Long
For i = 1 To Total_columns
With Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns(i).Range
Rows_count(i) = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
Next i
TotalRowsCount = WorksheetFunction.Max(Rows_count)
End Function
3.)
Function TotalColumnsCount(WBString As String, WorksheetNameString As String) As Long
Dim lastColumn As Long
lastColumn = Workbooks(WBString).Worksheets(WorksheetNameString).Cells(1, Columns.Count).End(xlToLeft).Column
TotalColumnsCount = lastColumn
End Function
excel vba excel-vba listbox
When the line reaches lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
I get the error:
When I try to replace it with:
lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
, I get the error:
I believe the name of the table is correct as shown in the image below:
Option Explicit
Dim filterRangeFoilProfile As Range, filteredRangeFoilProfile As Range
Private Sub cbxSupplier_AfterUpdate()
Dim Supplier_col As Long
lbxFoilInfoDisplay.RowSource = vbNullString
Supplier_col = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index
filterRangeFoilProfile.AutoFilter Field:=Supplier_col, Criteria1:=cbxSupplier.Text
On Error Resume Next
Set filteredRangeFoilProfile = Intersect(filterRangeFoilProfile, filterRangeFoilProfile.Offset(1, 0)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not filteredRangeFoilProfile Is Nothing Then
ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets("List_Box").Cells(2, 1).PasteSpecial
lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
'lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
End If
End Sub
User-Defined Functions (to add context):
1.)
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
2.)
Function TotalRowsCount(WBString As String, WorksheetNameString As String, TableNameString As String) As Long
Dim Total_columns As Long
Dim i As Long
Total_columns = Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns.Count
ReDim Rows_count(1 To Total_columns) As Long
For i = 1 To Total_columns
With Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns(i).Range
Rows_count(i) = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
Next i
TotalRowsCount = WorksheetFunction.Max(Rows_count)
End Function
3.)
Function TotalColumnsCount(WBString As String, WorksheetNameString As String) As Long
Dim lastColumn As Long
lastColumn = Workbooks(WBString).Worksheets(WorksheetNameString).Cells(1, Columns.Count).End(xlToLeft).Column
TotalColumnsCount = lastColumn
End Function
excel vba excel-vba listbox
excel vba excel-vba listbox
edited Nov 21 '18 at 14:50
asked Nov 21 '18 at 14:45
Pherdindy
109116
109116
Can i suggest to do aDebug.Print "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
and see that your range gets generated properly? Do this before or instead of the problem line of course.
– DarXyde
Nov 21 '18 at 15:23
@DarXyde I gottblFoilInfoHelper!A1:K4
as the output i'm not sure if I did theRowSource
method correctly but I believe it is
– Pherdindy
Nov 21 '18 at 15:35
1
Try using the sheet name instead of the table name? ie:lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
– DarXyde
Nov 21 '18 at 15:48
@DarXyde Oh yes... That works didn't realize that was the issue. I had a worksheet namedTable
in the reference workbook I was using I confused it as a table. Thanks
– Pherdindy
Nov 21 '18 at 16:01
Glad i could help... :)
– DarXyde
Nov 21 '18 at 16:20
add a comment |
Can i suggest to do aDebug.Print "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
and see that your range gets generated properly? Do this before or instead of the problem line of course.
– DarXyde
Nov 21 '18 at 15:23
@DarXyde I gottblFoilInfoHelper!A1:K4
as the output i'm not sure if I did theRowSource
method correctly but I believe it is
– Pherdindy
Nov 21 '18 at 15:35
1
Try using the sheet name instead of the table name? ie:lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
– DarXyde
Nov 21 '18 at 15:48
@DarXyde Oh yes... That works didn't realize that was the issue. I had a worksheet namedTable
in the reference workbook I was using I confused it as a table. Thanks
– Pherdindy
Nov 21 '18 at 16:01
Glad i could help... :)
– DarXyde
Nov 21 '18 at 16:20
Can i suggest to do a
Debug.Print "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
and see that your range gets generated properly? Do this before or instead of the problem line of course.– DarXyde
Nov 21 '18 at 15:23
Can i suggest to do a
Debug.Print "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
and see that your range gets generated properly? Do this before or instead of the problem line of course.– DarXyde
Nov 21 '18 at 15:23
@DarXyde I got
tblFoilInfoHelper!A1:K4
as the output i'm not sure if I did the RowSource
method correctly but I believe it is– Pherdindy
Nov 21 '18 at 15:35
@DarXyde I got
tblFoilInfoHelper!A1:K4
as the output i'm not sure if I did the RowSource
method correctly but I believe it is– Pherdindy
Nov 21 '18 at 15:35
1
1
Try using the sheet name instead of the table name? ie:
lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
– DarXyde
Nov 21 '18 at 15:48
Try using the sheet name instead of the table name? ie:
lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
– DarXyde
Nov 21 '18 at 15:48
@DarXyde Oh yes... That works didn't realize that was the issue. I had a worksheet named
Table
in the reference workbook I was using I confused it as a table. Thanks– Pherdindy
Nov 21 '18 at 16:01
@DarXyde Oh yes... That works didn't realize that was the issue. I had a worksheet named
Table
in the reference workbook I was using I confused it as a table. Thanks– Pherdindy
Nov 21 '18 at 16:01
Glad i could help... :)
– DarXyde
Nov 21 '18 at 16:20
Glad i could help... :)
– DarXyde
Nov 21 '18 at 16:20
add a comment |
1 Answer
1
active
oldest
votes
Use the sheet name instead of the table name. ie: "List_Box!A1:" &...
lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
Might find some use for this... or not. You can do this using the table as well, but you still need to use the Sheetname in order to get the address of the cells. ie.
ActiveSheet.ListObjects(1).Range.Cells(3, 1).Address
This is useful if you need to refer to specific cells in your table, and care less where the table is located. However this is only to help you build the address part - and not to replace the sheet name with the table name.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53414565%2funable-to-set-a-range-into-the-listbox-rowsource-method-or-listbox-list-method%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use the sheet name instead of the table name. ie: "List_Box!A1:" &...
lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
Might find some use for this... or not. You can do this using the table as well, but you still need to use the Sheetname in order to get the address of the cells. ie.
ActiveSheet.ListObjects(1).Range.Cells(3, 1).Address
This is useful if you need to refer to specific cells in your table, and care less where the table is located. However this is only to help you build the address part - and not to replace the sheet name with the table name.
add a comment |
Use the sheet name instead of the table name. ie: "List_Box!A1:" &...
lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
Might find some use for this... or not. You can do this using the table as well, but you still need to use the Sheetname in order to get the address of the cells. ie.
ActiveSheet.ListObjects(1).Range.Cells(3, 1).Address
This is useful if you need to refer to specific cells in your table, and care less where the table is located. However this is only to help you build the address part - and not to replace the sheet name with the table name.
add a comment |
Use the sheet name instead of the table name. ie: "List_Box!A1:" &...
lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
Might find some use for this... or not. You can do this using the table as well, but you still need to use the Sheetname in order to get the address of the cells. ie.
ActiveSheet.ListObjects(1).Range.Cells(3, 1).Address
This is useful if you need to refer to specific cells in your table, and care less where the table is located. However this is only to help you build the address part - and not to replace the sheet name with the table name.
Use the sheet name instead of the table name. ie: "List_Box!A1:" &...
lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
Might find some use for this... or not. You can do this using the table as well, but you still need to use the Sheetname in order to get the address of the cells. ie.
ActiveSheet.ListObjects(1).Range.Cells(3, 1).Address
This is useful if you need to refer to specific cells in your table, and care less where the table is located. However this is only to help you build the address part - and not to replace the sheet name with the table name.
answered Nov 21 '18 at 16:19
DarXyde
24115
24115
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53414565%2funable-to-set-a-range-into-the-listbox-rowsource-method-or-listbox-list-method%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can i suggest to do a
Debug.Print "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
and see that your range gets generated properly? Do this before or instead of the problem line of course.– DarXyde
Nov 21 '18 at 15:23
@DarXyde I got
tblFoilInfoHelper!A1:K4
as the output i'm not sure if I did theRowSource
method correctly but I believe it is– Pherdindy
Nov 21 '18 at 15:35
1
Try using the sheet name instead of the table name? ie:
lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")
– DarXyde
Nov 21 '18 at 15:48
@DarXyde Oh yes... That works didn't realize that was the issue. I had a worksheet named
Table
in the reference workbook I was using I confused it as a table. Thanks– Pherdindy
Nov 21 '18 at 16:01
Glad i could help... :)
– DarXyde
Nov 21 '18 at 16:20