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]

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

[agmorgan]

Genius! :bigyello:
I modified the disable procedure slightlyPrivate 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 fullPrivate 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 thisPrivate 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