The code below is for a userform that is created at runtime. The form lists all of the possible selections that a user may chosen in the Data Entry sheet and he can enter the order he would like to see the results in a report sheet. I would like to improve the design of the form by changing the backcolor of the label of the unused items, clear and disable the corresponding textbox. The following code changes the backcolor but I am stumped getting to the corresponding textbox. Can anyone help?
Sub PrepUserform(num As Integer)
'*******************************************************************
' Show the Active/Inactive data items
'*******************************************************************
Dim cell As Control, cell2 As Control
Dim findItem As range
Dim iNum As Integer
Dim tbName As String
' Mark all Inactive labels, clear and disable the appropriate textbox
For Each cell In UserForm1.Controls
If Mid(cell.Name, 1, 1) = "L" Then
If Not (cell.Name = "Label1" Or cell.Name = "Label2" Or cell.Name = "Label3") Then
Set findItem = Sheet4.range("A1:A200").Find(cell.Caption)
If findItem Is Nothing Then
cell.BackColor = &HFFC0C0
tbName = "TextBox" & Mid(cell.Name, 6, 1) - 3 'The textbox number is 3 less that the label number
' Here is where I want to get the appropriate textbox to change its properties.
End If
End If
End If
Next cell
'Show the form
UserForm1.Show
End Sub
Bookmarks