Solved: For Loop to Enable Labels

[CreganTur]

I've got a UserForm created that opens with all labels and textboxes set to Enabled=False. There are 2 option buttons that the User can interact with. Depending on which button they click will enable certain labels and textboxes.

I wanted to create a For loop structure to handle this, but it's not working (because I know my logic is faulty). Here's what I currently have (just for labels):
Private Sub optMkrOnly_Click()
Dim i As Integer
Dim Label As Label

For i = 2 To 4
With Label & i
.Enabled = True
End With
Next

End Sub

The error message I get tells me that I can't use the variable Label with 'With' because it's not the right object type.

[Norie]

Randy

For a start you can't construct variable names like that.

Try this.

With Me.Controls("Label" & I)

[CreganTur]

Randy

Try this.

With Me.Controls("Label" & I)


That did it. Thanks!

[fumei]

And just as pointer/hint, having a variable name as "Label" is not a good idea. Variable names should not be the same as the object type.

Dim Label As Label

vs

Dim oLabel As Label

Further, the code:

With Me.Controls("Label" & I)

does not, in fact, use the declared object Label. It is using a string - "Label" and the numeric variable i. You can, in fact, get rid of the Dim Label As Label, as you are not using it.

[CreganTur]

You can, in fact, get rid of the Dim Label As Label, as you are not using it.

You're exactly right; thanks:thumb

[fumei]

You may also consider something like:

Dim oTB As Control
For Each oTB In Me.Controls()
If TypeOf oTB Is MSForms.TextBox Then
oTB.Enabled = True
End If
Next

[CreganTur]

You may also consider something like:

How can I use something similar to this to set the result of ActiveDocument.FormFields to = "" ?

I know that ActiveDocument.Formfields(Name).result = "" will change the value of the named formfield to empty string. I'd like to do that for all formfields in a document.

[fumei]

Simple. Basically, the same thing. Make an object of the Collection and then a For Each.

Dim oFF As FormField
For Each oFF In ActiveDocument.FormFields
If oFF.Type = wdFieldFormTextInput Then
oFF.Result = ""
End If
Next

I am assuming you may have other formfields, which is why there is a test for the Type.

You can also - and this can be very useful - make an object of the whole formfield collection.

Dim DocFF As FormFields
' notice the plural, this makes the object
' ALL the formfields

Set DocFF = ActiveDocument.FormFields

Now you can use that object. So instead of something like:

ActiveDocument.FormFields("ClientName").Result

You can use:

DocFF("ClientName").Result

Or, going back to the first code:
Dim DocFF As FormFields
Dim oFF As FormField

Set DocFF = ActiveDocument.FormFields
For Each oFF in DocFF
' do whatever to each formfield

So again for the example:

Dim DocFF As FormFields
Dim oFF As FormField

Set DocFF = ActiveDocument.FormFields
For Each oFF in DocFF
If off.Type = wdFieldFormTextInput Then
oFF.Result = ""
End If
Next

[CreganTur]

:bug: It boggles the mind how elegant your solutions are... but I guess that's born of 25 years experience.

I was able to use that idea to also clear out a collection of bookmarks I use in the same document (where strBookmark is the only bookmark I do not want to clear):

For Each objBkmrk In ActiveDocument.Bookmarks()
If Not objBkmrk.Name = strBookmark Then
ActiveDocument.Bookmarks(objBkmrk.Name).Range.Text = ""
End If
Next

I really appreciate the help.

[fumei]

And there ya go. Good for you.

I know I harp on and on about using/understanding the Object Model, but really, that is the key to using VBA fully.

It does use objects, and NOT using them means missing out on most of the functionality and power inherent in the Object Model.

Again, good for you. I like it when someone takes a piece of information and extrapolates it into other actions. It means they understand the concept. And understanding concepts is far more important than rattling off code.

[fumei]

While most definitely not required to do what you do in your code, just to hammer home the idea of an object being a collection....
Dim Doc_BM As Bookmarks
Set Doc_BM = ActiveDocument.Bookmarks

For Each objBkmrk In Doc_BM
If Not objBkmrk.Name = strBookmark Then
Doc_BM(objBkmrk.Name).Range.Text = ""
End If
Next

The reason I mention this is, say you a few procedures that perform actions regarding the document bookmarks. You could make a global variable for the document bookmarks.

Option Explicit
Public Doc_BM As Bookmarks

Sub Document_Open()
Set Doc_BM = ActiveDocument.Bookmarks
End Sub

The result? Whenever the document is opened, a global variable is set for ALL the bookmarks. Now you can use THAT variable anywhere, in any procedure that you use to action bookmarks. For example:


Sub yaddaBlah()
Msgbox Doc_BM("HooHa").Range.Text
End Sub


Sub NotThatOne(strBookmark As String)
Dim objBkmrk As Bookmark
For Each objBkmrk In Doc_BM
If Not objBkmrk.Name = strBookmark Then
Doc_BM(objBkmrk.Name).Range.Text = ""
End If
Next
End Sub

Sub EveryThingBut()
Dim strKeepMe As String
strKeepMe = Inputbox("Keep which one?")
Call NotThatOne strKeepMe
End Sub



Or if you have multiple procedures that do various actions on formfields, by making an global variable object of the whole collection, you can use that in all the procedures without declaring it over and over.