Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Control Array in VBA? (Excel 97 and >)

    Folks

    I don't know how to describe what I want to do, but I can give you an example and it is right dead on the money.

    If you start the CONCATENATE formula, Excel will give you a dialog with two "Text Boxes", when you enter something in the second "Text Box" Excel gives you a 3rd and then a 4th and then a 5th one and then a scrollbar appears.

    I am looking to emulate that behavior on a UserForm, any ideas what is that control?

    I know VBA does not support Control Arrays, but I wanted to describe it and that is the best I could come up with.

    TIA

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Control Array in VBA? (Excel 97 and >)

    The control you are referring to is similar to if not the actual RefEdit control. It seeems to be dynamically added by code as required, and may not need to an actual array.

    Andrew C

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Control Array in VBA? (Excel 97 and >)

    Andrew

    Yes the RefEdit does have some of the properties that I am looking for, but how do I keep adding more and more controls, and then get the scroll bar when the number exceeds what I can display?

    Also could I remove the button on the side of the RefEdit control?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Array in VBA? (Excel 97 and >)

    Wassim, you owe me big time for this gem, but I hope that it works on XL97:
    Create a UserForm named frmData with one textbox at the top of the form.
    Add a class module, clsText with the following code:
    <pre> Option Explicit
    ' Class Module to handle TextBox Click Events

    Public WithEvents TextCtrl As MSForms.TextBox

    ' Change event
    ' This event will be triggered by each TextBox wrapped in this class
    ' The event updates the spreadsheet and make sure there is a blank
    ' textbox at the bottom of the UserForm

    Private Sub TextCtrl_Change()
    Dim txtData As New clsText ' New textbox in class wrapper
    Dim intControl As Integer ' Current control number
    intControl = Right(Me.TextCtrl.Name, Len(Me.TextCtrl.Name) - 7)
    ' Update worksheet
    ActiveSheet.Cells(intControl, 1) = Me.TextCtrl.Text
    If intControl = colText.Count Then ' If last line then make new one
    Set txtData.TextCtrl = frmDataEntry.Controls.Add("Forms.TextBox.1", , True)
    With txtData.TextCtrl
    .Left = Me.TextCtrl.Left
    .Top = Me.TextCtrl.Top + Me.TextCtrl.Height + 5
    .Width = Me.TextCtrl.Width
    .Height = Me.TextCtrl.Height
    frmDataEntry.Height = .Top + .Height + 38
    End With
    colText.Add txtData ' Add textbox to the collection
    End If
    End Sub</pre>

    Add a normal module with the following macro:
    <pre> Option Explicit
    Option Base 1

    Public colText As New Collection ' of textboxes

    Public Sub DataEntry()
    Dim txtData As New clsText ' Textbox container
    ' Add initial textbox from UserForm
    Set txtData.TextCtrl = frmDataEntry.TextBox1
    colText.Add txtData
    frmDataEntry.Show ' Display UserForm
    End Sub</pre>

    Run the macro. I
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Array in VBA? (Excel 97 and >)

    Already found 2 problems with my attachment. First, it doesn't work in IE6, so save it to a file and open it with Excel. Second, the sheet says to run the ShowDialog macro, it's actually called DataEntry. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Control Array in VBA? (Excel 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Sammy

    Wow !!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> I owe you one. Now I need to understand how it works and then get going.
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Control Array in VBA? (Excel 97 and >)

    Sam

    The link you have in this thread, is broken <img src=/S/clip.gif border=0 alt=clip width=15 height=15> , can you resend it again. I scanned J-Walk's site and could not find anything.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Array in VBA? (Excel 97 and >)

    This page from John Walkenbach was where I started, because I knew that I didn't want to have a separate event for each textbox. From there it was a lot of searches and <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>, but I learned a lot. (Mostly that textboxes were not as well designed as command buttons! ) Have fun! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Array in VBA? (Excel 97 and >)

    Thanks, Wassim, don't know what happened, but I edited it and it seems OK now. The link was for http://j-walk.com/ss/excel/tips/tip44.htm
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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