VBA - Control Array

[agmorgan]

Remember this is using VBA in Excel!

I have a form with 5 RefEdit controls on it and 5 associated labels.
There is also a textbox with a spinbutton (UpDown control?)
which regulates how many of the RefEdit controls are enabled.

A control array would be better than this select case, but control
arrays aren't available in VBA.
How can I improve?

Private Sub spnLines_Change()
 
' Change the Value of the textbox with
' the UpDown control's Value property.
txtLines.Text = Format(spnLines.Value, "0")
 
 
End Sub
 
 
 
 
 
Private Sub txtLines_Change()
'Make sure entry is a number, if not, beep and put in last value.
    If Not IsNumeric(txtLines.Text) Then
        Beep
        txtLines.Text = Format(spnLines.Value, "0")
        Cancel = True
        Exit Sub
    End If
'Check values are within accepted range, exit if not.
    If Val(txtLines.Text) > spnLines.Max Or Val(txtLines.Text) < spnLines.Min Then
        MsgBox "Number Of Lines Must Be Between 1 and 5"
    Exit Sub
    End If
 
    spnLines.Value = Val(txtLines.Text)
    
Select Case CInt(txtLines.Text)
 
    Case 1
        RefEdit2.Enabled = False
        RefEdit3.Enabled = False
        RefEdit4.Enabled = False
        RefEdit5.Enabled = False
        lblLine2.Enabled = False
        lblLine3.Enabled = False
        lblLine4.Enabled = False
        lblLine5.Enabled = False
    Case 2
        RefEdit2.Enabled = True
        RefEdit3.Enabled = False
        RefEdit4.Enabled = False
        RefEdit5.Enabled = False
        lblLine2.Enabled = True
        lblLine3.Enabled = False
        lblLine4.Enabled = False
        lblLine5.Enabled = False
    Case 3
        RefEdit2.Enabled = True
        RefEdit3.Enabled = True
        RefEdit4.Enabled = False
        RefEdit5.Enabled = False
        lblLine2.Enabled = True
        lblLine3.Enabled = True
        lblLine4.Enabled = False
        lblLine5.Enabled = False
    Case 4
        RefEdit2.Enabled = True
        RefEdit3.Enabled = True
        RefEdit4.Enabled = True
        RefEdit5.Enabled = False
        lblLine2.Enabled = True
        lblLine3.Enabled = True
        lblLine4.Enabled = True
        lblLine5.Enabled = False
    Case 5
        RefEdit2.Enabled = True
        RefEdit3.Enabled = True
        RefEdit4.Enabled = True
        RefEdit5.Enabled = True
        lblLine2.Enabled = True
        lblLine3.Enabled = True
        lblLine4.Enabled = True
        lblLine5.Enabled = True
        
End Select
 
End Sub
 
Private Sub txtLines_Enter()
' Start highlight before first character.
txtLines.SelStart = 0
' Highlight to end of text.
txtLines.SelLength = Len(txtLines.Text)
End Sub

[alex_read]

[size=1]Private Sub spnLines_Change()
    ' Change the Value of the textbox with the UpDown control's Value property.
    txtLines.Text = CStr(spnLines.Value)
End Sub
 
 
Private Sub txtLines_Change()
    'Make sure entry is a number, if not, beep and put in last value.
    If Not IsNumeric(txtLines.Text) Then
        Beep
        txtLines.Text = Format(spnLines.Value, "0")
        Cancel = True
    
    'Check values are within accepted range, exit if not.
    ElseIf Val(txtLines.Text) > spnLines.Max Or Val(txtLines.Text) < spnLines.Min Then
        MsgBox "Number Of Lines Must Be Between 1 and 5"
 
    ' Call below procedure to enable/disable relavent form controls.
    Else
        spnLines.Value = Val(txtLines.Text)
        DisableFormControls (txtLines.Text)
    End If
End Sub
 
 
Private Sub DisableFormControls(ByVal strNumberEntered As String)
    Dim ctlFormControl As Control
    
    For Each ctlFormControl In UserForm1.Controls
        If Not (Right(ctlFormControl.Name, 1) = strNumberEntered) And _
        Not (ctlFormControl.Name = "txtLines") Then
            If (Mid(ctlFormControl.Name, 4, 4) = "Edit") Or _
            (Mid(ctlFormControl.Name, 4, 4) = "Line") Then
                ctlFormControl.Enabled = False
            End If
        Else
            ctlFormControl.Enabled = True
        End If
    Next ctlFormControl
End Sub
 
 
Private Sub txtLines_Enter()
    ' Start highlight before first character, & highlight to end of text.
    With txtLines
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
End Sub[/size]

[agmorgan]

Genius!
I modified the disable procedure slightly

Private Sub DisableFormControls(ByVal strNumberEntered As String)
    Dim ctlFormControl As Control
    
    For Each ctlFormControl In frmMain.Controls
        If Not (Right(ctlFormControl.Name, 1) <= strNumberEntered) And _
        Not (ctlFormControl.Name = "txtLines") And _
        Not (ctlFormControl.Name = "lblLines") And _
        Not (ctlFormControl.Name = "spnLines") Then
            If (Mid(ctlFormControl.Name, 4, 4) = "Edit") Or _
            (Mid(ctlFormControl.Name, 4, 4) = "Line") Then
                ctlFormControl.Enabled = False
            End If
        Else
            ctlFormControl.Enabled = True
        End If
    Next ctlFormControl
End Sub

Now there is <= and also a couple of other controls excluded

Thanks!

[agmorgan]

OK. I have tried to take Alexs example and modify another piece of my code
Have I done it right or is there still some tweaking?
I was using the following to check if all the data ranges were full

Private Sub cmdOK_Click()
 
If RefEdit1.Value = "" Then
    MsgBox "Don't forget to select a data range!"
    Exit Sub
End If
 
If RefEdit2.Enabled = True And RefEdit2.Value = "" Then
    MsgBox "Don't forget to select a data range for the second line!"
    Exit Sub
End If
 
If RefEdit3.Enabled = True And RefEdit3.Value = "" Then
    MsgBox "Don't forget to select a data range for the third line!"
    Exit Sub
End If
 
If RefEdit4.Enabled = True And RefEdit4.Value = "" Then
    MsgBox "Don't forget to select a data range for the fourth line!"
    Exit Sub
End If
 
If RefEdit5.Enabled = True And RefEdit5.Value = "" Then
    MsgBox "Don't forget to select a data range for the fifth line!"
    Exit Sub
End If
 
'......
'......
 
End Sub

Now I have changed it to this

Private Sub cmdOK_Click()
 
Dim blnRange As Boolean
Call CheckControls(blnRange)
If blnRange = False Then
    Exit Sub
End If
 
'......
'......
 
End Sub
 
 
Private Sub CheckControls(ByRef blnRange As Boolean)
    Dim ctlFormControl As Control
    
    blnRange = True
    For Each ctlFormControl In frmMain.Controls
        If Left(ctlFormControl.Name, 7) = "RefEdit" Then
            If ctlFormControl.Enabled = True And _
                    ctlFormControl.Value = "" Then
                MsgBox "Don't forget to select a data range for line " & Right(ctlFormControl.Name, 1) & "!"
                blnRange = False
                Exit Sub
            End If
 
        End If
    Next ctlFormControl
End Sub