Tidying up the following code

[Katherine]

Part of the code for a simple userform: (see below for query)

~~~~~~~~~~~~~~~~~~~~
Private Sub cmdNext_Click()
nSet = txtBox.Value
If optRent = True Then
strSaleRent = "For Rent"
Else:
strSaleRent = "For Sale"
End If
With ActiveDocument
.Bookmarks("bmkStatus" & nSet).Range _
.InsertBefore strSaleRent
.Bookmarks("bmkPrice" & nSet).Range _
.InsertBefore txtPrice
.Bookmarks("bmkAdtext" & nSet).Range _
.InsertBefore txtAdtext
.Bookmarks("bmkDistrict" & nSet).Range _
.InsertBefore txtDistrict
End With
If strSaleRent = "For Rent" Then strTenure = "Rental"
If strSaleRent = "For Sale" Then strTenure = "Freehold"
ActiveDocument.Bookmarks("bmkTenure" & nSet).Range _
.InsertBefore strTenure
txtBox = Null
txtDistrict = Null
optSale = True
txtPrice = Null
txtAdtext = Null
txtPrice.Enabled = False
txtAdtext.Enabled = False
lblPrice.Enabled = False
lblAdtext.Enabled = False
End Sub
~~~~~~~~~~~~~~~~~~~

What I need to do is give a visual representation of which box numbers have
already been submitted so I've created a series of labels, corresponding to
the possible box numbers called lblBox1, lblBox2 etc, the idea being that
once that box number has been used the label will become greyed out to show
this.

The box number is the value entered into txtBox and the nSet variable has
the same value as this text box, so I could do this with the following lines:

If nSet = 1 then lblBox1.enabled = False
If nSet = 2 then lblBox2.enabled = False etc

Problem is, potentially nSet could be anything from 1 to 48 and writing that
out 48 times seems rather clumsy. Des anyone have any suggestions as how I
could tidy the code for this up?

 

[Helmut Weber]

Hi Katherine,
haven't me met before here?
If you really want to tidy up you code, I think, you have to start
from scratch. Unlike VB, VBA doesn't provide for arrays of controls
automatically. However, one can force it, by defining arrays of
controls, like (global):
Dim arTxt() As TextBox
Dim arLbl() As Label
The index will refer to the sequence, in which the were created.
Assigning each control to a indexed control object:
Private Sub UserForm_Initialize()
Dim i As Integer
Dim oCnt As Control
For Each oCnt In Me.Controls ' count textboxes = count lables
If TypeOf oCnt Is MSForms.TextBox Then
iCnt = iCnt + 1
End If
Next
ReDim arTxt(iCnt) As TextBox
ReDim arLbl(iCnt) As Label
iCnt = 0
For Each oCnt In Me.Controls
If TypeOf oCnt Is MSForms.TextBox Then
iCnt = iCnt + 1
Set arTxt(iCnt) = oCnt
End If
Next
iCnt = 0
' assign individual controls to the array of controls
For Each oCnt In Me.Controls
If TypeOf oCnt Is MSForms.Label Then
iCnt = iCnt + 1
Set arLbl(iCnt) = oCnt
End If
Next
End Sub
---
From now on it's easy:
---
Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To iCnt
If arTxt(i) <> "" Then
arLbl(i) = "done"
End If
Next
End Sub
---
HTH, keep on!
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98