`Invalid Qualifier` error on an Array variable with Combobox object names
I get an Invalid Qualifier
error in the Cbx_name(i).Clear
line. This code is placed in a separate module which i'll use: Call Dynamic_cbx
on the UserForms events. I believe I referenced it right in the module by using the form [UserForm Name].[Combo Box Name]
, but i'm not sure.
Option Explicit
Sub Dynamic_cbx()
Dim dCell As Range
Dim dict As Scripting.Dictionary
Dim Cbx_count As Long, i As Long
Cbx_count = 8
ReDim Cbx_loop(1 To Cbx_count) As Long, Cbx_name(1 To Cbx_count) As Long
Cbx_loop(1) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index
Cbx_loop(2) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL DESCRIPTION").Index
Cbx_loop(3) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("BRAND").Index
Cbx_loop(4) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("COLOR NUMBER").Index
Cbx_loop(5) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL WIDTH").Index
Cbx_loop(6) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("UOM (Foil Width)").Index
Cbx_loop(7) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL LENGTH").Index
Cbx_loop(8) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("UOM (Foil Length)").Index
Cbx_name(1) = frmFoilPanel.cbxSupplier
Cbx_name(2) = frmFoilPanel.cbxFoilDescription
Cbx_name(3) = frmFoilPanel.cbxFoilBrand
Cbx_name(4) = frmFoilPanel.cbxColorNumber
Cbx_name(5) = frmFoilPanel.cbxFoilWidth
Cbx_name(6) = frmFoilPanel.cbxUOMfw
Cbx_name(7) = frmFoilPanel.cbxFoilLength
Cbx_name(8) = frmFoilPanel.cbxUOMfl
For i = 1 To Cbx_count
Cbx_name(i).Clear
Next i
For i = 1 To Cbx_count
With dict
For Each dCell In ThisWorkbook.Worksheets("List_Box").Range(Col_Letter(i) & "2:" & Col_Letter(i) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
If Not .exists(dCell.Value) Then
.Add dCell.Value
End If
Next dCell
Cbx_name(i).List = .keys
Set dict = Nothing
End With
Next i
End Sub
excel vba excel-vba
add a comment |
I get an Invalid Qualifier
error in the Cbx_name(i).Clear
line. This code is placed in a separate module which i'll use: Call Dynamic_cbx
on the UserForms events. I believe I referenced it right in the module by using the form [UserForm Name].[Combo Box Name]
, but i'm not sure.
Option Explicit
Sub Dynamic_cbx()
Dim dCell As Range
Dim dict As Scripting.Dictionary
Dim Cbx_count As Long, i As Long
Cbx_count = 8
ReDim Cbx_loop(1 To Cbx_count) As Long, Cbx_name(1 To Cbx_count) As Long
Cbx_loop(1) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index
Cbx_loop(2) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL DESCRIPTION").Index
Cbx_loop(3) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("BRAND").Index
Cbx_loop(4) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("COLOR NUMBER").Index
Cbx_loop(5) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL WIDTH").Index
Cbx_loop(6) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("UOM (Foil Width)").Index
Cbx_loop(7) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL LENGTH").Index
Cbx_loop(8) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("UOM (Foil Length)").Index
Cbx_name(1) = frmFoilPanel.cbxSupplier
Cbx_name(2) = frmFoilPanel.cbxFoilDescription
Cbx_name(3) = frmFoilPanel.cbxFoilBrand
Cbx_name(4) = frmFoilPanel.cbxColorNumber
Cbx_name(5) = frmFoilPanel.cbxFoilWidth
Cbx_name(6) = frmFoilPanel.cbxUOMfw
Cbx_name(7) = frmFoilPanel.cbxFoilLength
Cbx_name(8) = frmFoilPanel.cbxUOMfl
For i = 1 To Cbx_count
Cbx_name(i).Clear
Next i
For i = 1 To Cbx_count
With dict
For Each dCell In ThisWorkbook.Worksheets("List_Box").Range(Col_Letter(i) & "2:" & Col_Letter(i) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
If Not .exists(dCell.Value) Then
.Add dCell.Value
End If
Next dCell
Cbx_name(i).List = .keys
Set dict = Nothing
End With
Next i
End Sub
excel vba excel-vba
add a comment |
I get an Invalid Qualifier
error in the Cbx_name(i).Clear
line. This code is placed in a separate module which i'll use: Call Dynamic_cbx
on the UserForms events. I believe I referenced it right in the module by using the form [UserForm Name].[Combo Box Name]
, but i'm not sure.
Option Explicit
Sub Dynamic_cbx()
Dim dCell As Range
Dim dict As Scripting.Dictionary
Dim Cbx_count As Long, i As Long
Cbx_count = 8
ReDim Cbx_loop(1 To Cbx_count) As Long, Cbx_name(1 To Cbx_count) As Long
Cbx_loop(1) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index
Cbx_loop(2) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL DESCRIPTION").Index
Cbx_loop(3) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("BRAND").Index
Cbx_loop(4) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("COLOR NUMBER").Index
Cbx_loop(5) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL WIDTH").Index
Cbx_loop(6) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("UOM (Foil Width)").Index
Cbx_loop(7) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL LENGTH").Index
Cbx_loop(8) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("UOM (Foil Length)").Index
Cbx_name(1) = frmFoilPanel.cbxSupplier
Cbx_name(2) = frmFoilPanel.cbxFoilDescription
Cbx_name(3) = frmFoilPanel.cbxFoilBrand
Cbx_name(4) = frmFoilPanel.cbxColorNumber
Cbx_name(5) = frmFoilPanel.cbxFoilWidth
Cbx_name(6) = frmFoilPanel.cbxUOMfw
Cbx_name(7) = frmFoilPanel.cbxFoilLength
Cbx_name(8) = frmFoilPanel.cbxUOMfl
For i = 1 To Cbx_count
Cbx_name(i).Clear
Next i
For i = 1 To Cbx_count
With dict
For Each dCell In ThisWorkbook.Worksheets("List_Box").Range(Col_Letter(i) & "2:" & Col_Letter(i) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
If Not .exists(dCell.Value) Then
.Add dCell.Value
End If
Next dCell
Cbx_name(i).List = .keys
Set dict = Nothing
End With
Next i
End Sub
excel vba excel-vba
I get an Invalid Qualifier
error in the Cbx_name(i).Clear
line. This code is placed in a separate module which i'll use: Call Dynamic_cbx
on the UserForms events. I believe I referenced it right in the module by using the form [UserForm Name].[Combo Box Name]
, but i'm not sure.
Option Explicit
Sub Dynamic_cbx()
Dim dCell As Range
Dim dict As Scripting.Dictionary
Dim Cbx_count As Long, i As Long
Cbx_count = 8
ReDim Cbx_loop(1 To Cbx_count) As Long, Cbx_name(1 To Cbx_count) As Long
Cbx_loop(1) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index
Cbx_loop(2) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL DESCRIPTION").Index
Cbx_loop(3) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("BRAND").Index
Cbx_loop(4) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("COLOR NUMBER").Index
Cbx_loop(5) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL WIDTH").Index
Cbx_loop(6) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("UOM (Foil Width)").Index
Cbx_loop(7) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("FOIL LENGTH").Index
Cbx_loop(8) = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("UOM (Foil Length)").Index
Cbx_name(1) = frmFoilPanel.cbxSupplier
Cbx_name(2) = frmFoilPanel.cbxFoilDescription
Cbx_name(3) = frmFoilPanel.cbxFoilBrand
Cbx_name(4) = frmFoilPanel.cbxColorNumber
Cbx_name(5) = frmFoilPanel.cbxFoilWidth
Cbx_name(6) = frmFoilPanel.cbxUOMfw
Cbx_name(7) = frmFoilPanel.cbxFoilLength
Cbx_name(8) = frmFoilPanel.cbxUOMfl
For i = 1 To Cbx_count
Cbx_name(i).Clear
Next i
For i = 1 To Cbx_count
With dict
For Each dCell In ThisWorkbook.Worksheets("List_Box").Range(Col_Letter(i) & "2:" & Col_Letter(i) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))
If Not .exists(dCell.Value) Then
.Add dCell.Value
End If
Next dCell
Cbx_name(i).List = .keys
Set dict = Nothing
End With
Next i
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 23 '18 at 6:46
Pherdindy
asked Nov 23 '18 at 6:38
PherdindyPherdindy
96221
96221
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
It's an array. To empty the content at an index do
Cbx_name(i) = vbNullString
There is no .Clear method for an array index.
Oh right, but there is a.Clear
method for comboboxes right? Does theCbx_name(i) = vbNullString
clear the contents of the combobox similarly tocbx.Clear
? or does it just clear the array's space. I am quite confused how it works if I reference the combobox object to an array
– Pherdindy
Nov 23 '18 at 6:50
No. It puts an empty string literal at that index position so replaces whatever is in that index with "". If you want to empty the entire cbx then call .Clear on that.
– QHarr
Nov 23 '18 at 6:51
Didn't realize the blunder I made lol by thinking.Clear
will clear the comboboxes when it's using an array. Thanks that means i'll have to declare 2 arrays one for clearing the comboboxes and one for referencing the combobox name. In my case is there a way to hit 2 in 1 like how I was planning to do it in the code?
– Pherdindy
Nov 23 '18 at 6:53
1
If you are looping yes. If you are assigning to the object in one go you can use an array as produced by .Keys
– QHarr
Nov 23 '18 at 7:13
1
Ok. I may pop by Code Review for a peek
– QHarr
Nov 23 '18 at 8:36
|
show 10 more comments
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%2f53441717%2finvalid-qualifier-error-on-an-array-variable-with-combobox-object-names%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
It's an array. To empty the content at an index do
Cbx_name(i) = vbNullString
There is no .Clear method for an array index.
Oh right, but there is a.Clear
method for comboboxes right? Does theCbx_name(i) = vbNullString
clear the contents of the combobox similarly tocbx.Clear
? or does it just clear the array's space. I am quite confused how it works if I reference the combobox object to an array
– Pherdindy
Nov 23 '18 at 6:50
No. It puts an empty string literal at that index position so replaces whatever is in that index with "". If you want to empty the entire cbx then call .Clear on that.
– QHarr
Nov 23 '18 at 6:51
Didn't realize the blunder I made lol by thinking.Clear
will clear the comboboxes when it's using an array. Thanks that means i'll have to declare 2 arrays one for clearing the comboboxes and one for referencing the combobox name. In my case is there a way to hit 2 in 1 like how I was planning to do it in the code?
– Pherdindy
Nov 23 '18 at 6:53
1
If you are looping yes. If you are assigning to the object in one go you can use an array as produced by .Keys
– QHarr
Nov 23 '18 at 7:13
1
Ok. I may pop by Code Review for a peek
– QHarr
Nov 23 '18 at 8:36
|
show 10 more comments
It's an array. To empty the content at an index do
Cbx_name(i) = vbNullString
There is no .Clear method for an array index.
Oh right, but there is a.Clear
method for comboboxes right? Does theCbx_name(i) = vbNullString
clear the contents of the combobox similarly tocbx.Clear
? or does it just clear the array's space. I am quite confused how it works if I reference the combobox object to an array
– Pherdindy
Nov 23 '18 at 6:50
No. It puts an empty string literal at that index position so replaces whatever is in that index with "". If you want to empty the entire cbx then call .Clear on that.
– QHarr
Nov 23 '18 at 6:51
Didn't realize the blunder I made lol by thinking.Clear
will clear the comboboxes when it's using an array. Thanks that means i'll have to declare 2 arrays one for clearing the comboboxes and one for referencing the combobox name. In my case is there a way to hit 2 in 1 like how I was planning to do it in the code?
– Pherdindy
Nov 23 '18 at 6:53
1
If you are looping yes. If you are assigning to the object in one go you can use an array as produced by .Keys
– QHarr
Nov 23 '18 at 7:13
1
Ok. I may pop by Code Review for a peek
– QHarr
Nov 23 '18 at 8:36
|
show 10 more comments
It's an array. To empty the content at an index do
Cbx_name(i) = vbNullString
There is no .Clear method for an array index.
It's an array. To empty the content at an index do
Cbx_name(i) = vbNullString
There is no .Clear method for an array index.
answered Nov 23 '18 at 6:47
QHarrQHarr
36.4k82144
36.4k82144
Oh right, but there is a.Clear
method for comboboxes right? Does theCbx_name(i) = vbNullString
clear the contents of the combobox similarly tocbx.Clear
? or does it just clear the array's space. I am quite confused how it works if I reference the combobox object to an array
– Pherdindy
Nov 23 '18 at 6:50
No. It puts an empty string literal at that index position so replaces whatever is in that index with "". If you want to empty the entire cbx then call .Clear on that.
– QHarr
Nov 23 '18 at 6:51
Didn't realize the blunder I made lol by thinking.Clear
will clear the comboboxes when it's using an array. Thanks that means i'll have to declare 2 arrays one for clearing the comboboxes and one for referencing the combobox name. In my case is there a way to hit 2 in 1 like how I was planning to do it in the code?
– Pherdindy
Nov 23 '18 at 6:53
1
If you are looping yes. If you are assigning to the object in one go you can use an array as produced by .Keys
– QHarr
Nov 23 '18 at 7:13
1
Ok. I may pop by Code Review for a peek
– QHarr
Nov 23 '18 at 8:36
|
show 10 more comments
Oh right, but there is a.Clear
method for comboboxes right? Does theCbx_name(i) = vbNullString
clear the contents of the combobox similarly tocbx.Clear
? or does it just clear the array's space. I am quite confused how it works if I reference the combobox object to an array
– Pherdindy
Nov 23 '18 at 6:50
No. It puts an empty string literal at that index position so replaces whatever is in that index with "". If you want to empty the entire cbx then call .Clear on that.
– QHarr
Nov 23 '18 at 6:51
Didn't realize the blunder I made lol by thinking.Clear
will clear the comboboxes when it's using an array. Thanks that means i'll have to declare 2 arrays one for clearing the comboboxes and one for referencing the combobox name. In my case is there a way to hit 2 in 1 like how I was planning to do it in the code?
– Pherdindy
Nov 23 '18 at 6:53
1
If you are looping yes. If you are assigning to the object in one go you can use an array as produced by .Keys
– QHarr
Nov 23 '18 at 7:13
1
Ok. I may pop by Code Review for a peek
– QHarr
Nov 23 '18 at 8:36
Oh right, but there is a
.Clear
method for comboboxes right? Does the Cbx_name(i) = vbNullString
clear the contents of the combobox similarly to cbx.Clear
? or does it just clear the array's space. I am quite confused how it works if I reference the combobox object to an array– Pherdindy
Nov 23 '18 at 6:50
Oh right, but there is a
.Clear
method for comboboxes right? Does the Cbx_name(i) = vbNullString
clear the contents of the combobox similarly to cbx.Clear
? or does it just clear the array's space. I am quite confused how it works if I reference the combobox object to an array– Pherdindy
Nov 23 '18 at 6:50
No. It puts an empty string literal at that index position so replaces whatever is in that index with "". If you want to empty the entire cbx then call .Clear on that.
– QHarr
Nov 23 '18 at 6:51
No. It puts an empty string literal at that index position so replaces whatever is in that index with "". If you want to empty the entire cbx then call .Clear on that.
– QHarr
Nov 23 '18 at 6:51
Didn't realize the blunder I made lol by thinking
.Clear
will clear the comboboxes when it's using an array. Thanks that means i'll have to declare 2 arrays one for clearing the comboboxes and one for referencing the combobox name. In my case is there a way to hit 2 in 1 like how I was planning to do it in the code?– Pherdindy
Nov 23 '18 at 6:53
Didn't realize the blunder I made lol by thinking
.Clear
will clear the comboboxes when it's using an array. Thanks that means i'll have to declare 2 arrays one for clearing the comboboxes and one for referencing the combobox name. In my case is there a way to hit 2 in 1 like how I was planning to do it in the code?– Pherdindy
Nov 23 '18 at 6:53
1
1
If you are looping yes. If you are assigning to the object in one go you can use an array as produced by .Keys
– QHarr
Nov 23 '18 at 7:13
If you are looping yes. If you are assigning to the object in one go you can use an array as produced by .Keys
– QHarr
Nov 23 '18 at 7:13
1
1
Ok. I may pop by Code Review for a peek
– QHarr
Nov 23 '18 at 8:36
Ok. I may pop by Code Review for a peek
– QHarr
Nov 23 '18 at 8:36
|
show 10 more comments
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.
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%2f53441717%2finvalid-qualifier-error-on-an-array-variable-with-combobox-object-names%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