Dims for entire Userform?

[Big Lar]

My UserForm has 33 TextBoxes<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
To add Textbox values to Sheet2, I use...<o></o>
<o></o>

[FONT=Verdana][SIZE=1]Private Sub TextBox3_AfterUpdate()<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Dim iRow As Long<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Dim WS As Worksheet<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Set WS = Worksheets("Sheet2")<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]'find first empty row in database<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]iRow = WS.Cells(Rows.Count, 1) _<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]            .End(xlUp).Offset(1, 0).Row<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]WS.Cells(iRow, 3).Value = Me.TextBox3.Value<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]End Sub<o:p></o:p>[/SIZE][/FONT]

<o></o>
<o></o>
Is there a way to code the Dims for the entire UserForm…so I don’t have to Dim each Sub?<o></o>

 

[MickG]

If you mean you don't want to write a sub for each TextBox, then try this:-
Paste the code below into your Userform module.

Option Explicit
[COLOR=navy]Dim[/COLOR] cTx() [COLOR=navy]As[/COLOR] cTxtBox
Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
  [COLOR=navy]Dim[/COLOR] Ctrl [COLOR=navy]As[/COLOR] Object, p [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
    ReDim cTx(1 To Me.Controls.Count)
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Ctrl [COLOR=navy]In[/COLOR] Me.Controls
       [COLOR=navy]If[/COLOR] TypeName(Ctrl) = "TextBox" [COLOR=navy]Then[/COLOR]
            p = p + 1
            [COLOR=navy]Set[/COLOR] cTx(p) = New cTxtBox
            [COLOR=navy]Set[/COLOR] cTx(p).STBox = Ctrl
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Ctrl
    ReDim Preserve cTx(1 To p)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Then creates a Class Module:-
Right click a space in the "Project Window" (Left Hand Pane in the VB Window.i.e "Ctrl +R")
Select "Insert" , "Class Module".
Paste code below into Window.
Select the New Class Name in the "Project window" which will say "Class1".
Click "F4" . New left Hand Pane appears (Properties of Class1) Change the name to "cTxtBox"
Close vb Editer Window.
Run code to open userform, Enter data:- Data entered should now appear on sheet.

Public WithEvents STBox [COLOR=navy]As[/COLOR] MSForms.TextBox
Private [COLOR=navy]Sub[/COLOR] StBox_Change()
[COLOR=navy]Dim[/COLOR] iRow [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] WS [COLOR=navy]As[/COLOR] Worksheet
Set WS = ActiveSheet [B][COLOR=green]'Change to [/COLOR][COLOR=green]Worksheets("Sheet2") as[/COLOR][COLOR=green] req'ed[/COLOR][/B]
 iRow = WS.Cells(rows.Count, 3) _
            .End(xlUp).Offset(1, 0).row
WS.Cells(iRow, 3).value = STBox.value
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick