Control Array in VBA? (Excel 97 and >)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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:

 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

Add a normal module with the following macro:

 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

Run the macro. I

Reply | Quote

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

Reply | Quote

Sammy

Wow !!! I owe you one. Now I need to understand how it works and then get going.

Reply | Quote

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 , but I learned a lot. (Mostly that textboxes were not as well designed as command buttons! ) Have fun! –Sam

Reply | Quote

Sam

The link you have in this thread, is broken , can you resend it again. I scanned J-Walk’s site and could not find anything.

Wassim

Reply | Quote

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%5B/url%5D

Reply | Quote