Topic: Iterating through Controls in Forms and Object Name

[ML]

Hi

I can iterate through a collection of controls on a form and get to the typeof control that I need but can someone help me do a string filter?

In other words:
If I have 20 command buttons for example and the are named CMBOrigname1, CMBOrigname2, CMBOrigname3 etc
Then
I wanted to change them to CMBNewname1, CMBNewname2, CMBNewname3 etc

I guess I need an Instr function of some sort?

With that I will be able to hopefully do something like this

Code: [Select]

For Each Ctrl In Me.Controls
  If TypeOf Ctrl Is CommandButton Then
     If Instr (Need help here) Then
      Ctrl.name = CMBNewname
  End If
 Next Ctrl

Along with The Instr function, I may need a Find and Replace Function as well

Any ideas how can I do this?

Thanks

Mark

[David Hall]

I dont think you can do what you want.  You CAN create controls on the fly thru code, but I think that you CANNOT rename them once you begin running your code

[ML]

No CM,

You are misunderstanding what I am asking;
I created the controls manually and now I decide that I want to give them all different names, I don't want to have to rename them all manually.

So all I am saying is loop through the form for the control type that you want and based on an unique character, be able to change the control name.

For example

If I have 20 command buttons:      CMBItem1L - CMBItem20L
and I want to rename them           CMBItem1R - CMBItem1R

I don't want to have to do this manually but rather through code

So, with my above code (which gets the control type) all we need to say (Sudo Code) is If Instr ("L") Find and Replace L with R

Does this make sense? So basically I need some code that searches the string for an unique character, then prompts me for the character I want to replace it with and make the change on all instances.

I know it can be done and I can probably do it, I was just hoping someone had something.

Thanks

Mark

[David Hall]

yes, i understood what you want, and I dont think it can be done b/c the control name is readonly once created UNLESS you manually rename it

[ML]

Hummmmm

That's interesting.

Let's take a look, shall we?       

[SomeCallMeDave]

You can use  VBA Extensibility to rename the controls.

Add a reference to Microsoft Visual Basic for Applications Extensibility (v5.3 on my system)  and try this code

Code: [Select]


Option Explicit
Public Sub Test()
  RenameControls "UserForm1"

End Sub

Public Sub RenameControls(pFormName As String)

Dim vb As VBE
Dim varComp As VBComponent
Dim varForm As VBComponent
Dim frmForm As UserForm
Dim cControl As Control
Dim i As Integer


i = 1
Set vb = ThisDrawing.Application.VBE

For Each varComp In vb.SelectedVBComponent.Collection
    If varComp.Name = pFormName Then
       Set varForm = varComp
    End If
       
   
Next


For Each cControl In varForm.Designer.controls
    cControl.Name = "Renamed" & i 
    i = i + 1
Next
End Sub



You will have to use the VB string functions to create the revised name.

I haven't tested this fully so use at your own risk.

Also,  if you already have code referencing the controls,  those calls will have to be changed too, I imagine. But you may be able to use VBE for that too.

[ML]

Hey Some

Thank you, I sort of get the code you have posted but I was trying something alittle more simpler like this:

Code: [Select]

Dim SearchString As String, SearchChar As String, NewSearchChar As String

SearchString = "CMB*****L"   ' String to search in.
SearchChar = "L"    ' Search for Character
NewSearchChar = "R" ' Replace with New Character


For Each Ctrl In Userform1.Controls
  If TypeOf Ctrl Is CommandButton Then
   If InStr(1, SearchString, SearchChar) Then
     Replace(SearchString, SearchChar, NewSearchChar) As String
   End If
  End If
 Next Ctrl

I am able to grab all of the command buttons and if I just use the Instr function; it is returning all command buttons with this search string
however, I can not get the replace function to work. Can somebody please tell me where I am going wrong?

CM may very well be right in that the names are read only, still I am curious to see if this will work

Thank you,

Mark

[David Hall]

It might work thru VBE, but my question is Is it worth it?  That's a 1 time function, that you wont use again (on this project probably)

[ML]

If I have to use The Extensibility feature, then probably not
If I could get it working my way, it would save LOTS of time as we have been doing a lot of form programming.
CM, today I had to rename 80 controls manually. Not that I mind but there is a lot of other good reasons why this method would be great.
That is, if you use a proper naming convention with your controls.

Mark

[SomeCallMeDave]

Something like

Code: [Select]


Public Sub Test()
  RenameControls "UserForm1"

End Sub

Public Sub RenameControls(pFormName As String)
  Dim vb As VBE
  Dim vbComBars As Object
  Dim varComp As VBComponent
  Dim varForm As VBComponent
  Dim frmForm As UserForm
  Dim cControl As Control
 

  Dim SearchString As String, SearchChar As String, NewSearchChar As String

   SearchString = "CMB*****L"   ' String to search in. 
   SearchChar = "L"    ' Search for Character
   NewSearchChar = "R" ' Replace with New Character

  Set vb = ThisDrawing.Application.VBE

  For Each varComp In vb.SelectedVBComponent.Collection
      If varComp.Name = pFormName Then
         Set varForm = varComp
      End If
  Next

  For Each cControl In varForm.Designer.controls
    If TypeOf cControl Is CommandButton Then
      If InStr(1, SearchString, SearchChar) Then
        'use the control name here, not a preset variable.  Return of Replace function will be the new name
        cControl.Name = Replace(cControl.Name, SearchChar, NewSearchChar)
     End If
   End If
 Next

End Sub



[ML]

SOME! You are the man!
Thank you, that worked great!

My last question would be can we (through code) have the sub check to see if Microsoft Visual Basic for Applications Extensibility type library is set? If it isn't can we have the code set it?

Thank you,

Mark

[SomeCallMeDave]

You're welcome.  I'm glad it worked for you.  It may not always work since the VBE can be strange sometimes.  So more error checking and such would be required to make it more robust.

My last question would be can we (through code) have the sub check to see if Microsoft Visual Basic for Applications Extensibility type library is set? If it isn't can we have the code set it?

I don't think so.  I believe VBE would be needed to perform that check. So it would need to be checked to check if it were checked.. checked.. checked...  ... checked

[ML]

I would think there would be a way to set the type library for people but that is a bonus that I can look into later.

I used a test project and it worked great as far as replacing The L with an R on the end

Later I will try more complex searches such as changing characters in the middle of the name, wildcards etc. but this a great start.

Mark