`Invalid Qualifier` error on an Array variable with Combobox object names












1















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









share|improve this question





























    1















    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









    share|improve this question



























      1












      1








      1








      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









      share|improve this question
















      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 23 '18 at 6:46







      Pherdindy

















      asked Nov 23 '18 at 6:38









      PherdindyPherdindy

      96221




      96221
























          1 Answer
          1






          active

          oldest

          votes


















          0














          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.






          share|improve this answer
























          • 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











          • 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











          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
          });


          }
          });














          draft saved

          draft discarded


















          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









          0














          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.






          share|improve this answer
























          • 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











          • 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
















          0














          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.






          share|improve this answer
























          • 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











          • 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














          0












          0








          0







          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.






          share|improve this answer













          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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 23 '18 at 6:47









          QHarrQHarr

          36.4k82144




          36.4k82144













          • 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











          • 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













          • 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




















          draft saved

          draft discarded




















































          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.




          draft saved


          draft discarded














          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





















































          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







          這個網誌中的熱門文章

          Xamarin.form Move up view when keyboard appear

          Post-Redirect-Get with Spring WebFlux and Thymeleaf

          Anylogic : not able to use stopDelay()