Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Control Arrays in VBA... Anyone?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In an earlier response to dantb, Juan posted this message:

    Why ? well, in VBA you have Userforms, not forms, they don't have a Load event, they have an Initialize event. You can't (Directly) have array of controls (the optSize looks like OptionButtons).

    (Directly) has my interest...

    With some of the stuff I do at work, it would be a huge help to me to be able to use control arrays in VBA.

    I've heard you can create a form in standalone VB and then call it as a dll.
    Do you know of a better alternative?

    Thanks

    And I appreciate all of the great info I am getting here.

    I sure wish I knew about this place a year ago.




    [ This Message was edited by: TsTom on 2002-03-23 18:55 ]

    [ This Message was edited by: TsTom on 2002-03-24 03:54 ]

    [ This Message was edited by: TsTom on 2002-03-24 05:04 ]

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom

    Quote:
    Why ? well, in VBA you have Userforms, not forms, they don't have a Load event, they have an Initialize event.

    What's the difference? The "Initialize" Event will only fire when the UserForm is Loaded!


    Quote:
    You can't (Directly) have array of controls (the optSize looks like OptionButtons).
    Don't understand.

    Tom, you are making the assumption we are all familiar with your project, which of course we are not.




  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the reply.
    I'll give an example here

    On our userform we have 8 text boxes.

    In standalone VB you are able to use one name for all 8 by creating a control array.

    txtOneName(0).text = "Same Name"
    txtOneName(1).text = "Same Name"
    txtOneName(2).text = "Same Name"
    txtOneName(3).text = "Same Name"
    txtOneName(4).text = "Same Name"
    txtOneName(5).text = "Same Name"
    txtOneName(6).text = "Same Name"
    txtOneName(7).text = "Same Name"

    This is very convenient because one block of code can take care of all like operations for each text box by simply passing the index number.

    As it stands in VBA, I would have to have 8 different names and 8 different blocks of code.

    If there is a way to use a control array in VBA, I would like to know how.


  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, There are a few ways this can be done, but really depends on the reasons. One very easy way is to simply have some code like:

    Sheet1.Range("A1")=TextBox1

    In the Change Event for any or all Textboxes. Then simply set the ControlSource Property of each to this cell.

    I also covered something similar in my latest Newsletter using the Tag Property of TextBoxes. See:
    http://www.ozgrid.com/News/Archive.htm
    RE: Issue 11



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •