Change several controls name at once

Question

Good morning,

I am looking for a way to change controls names on a form using VBA, this is only a one time change so no needs to have it run later by users.

So on the form I have 80 buttons named INPRD1 to INPRD80, If I want to change the name to INPRD321 to INPRD400 what could I do... I have this that I need to be completed:

Private Sub ChangeControlsNames_Click()
Dim i As Integer
For i = 1 To 30
Me.Controls ("INEMP" & i) = ??????
Next i

End Sub

Of couse you guess that the ?????? is where I need to change the controls (321 to 400)

Thanks

Claude from Québec, Canada

---

Claude Larocque

Answer 1

E.g. in a public module:

Option Compare Database
Option Explicit

Public Sub ChangeControls()

  DoCmd.OpenForm "Form1", acDesign
  
  Dim Count As Long
  
  For Count = 0 To Forms("Form1").Controls.Count - 1
    Debug.Print Forms("Form1").Controls(Count).Name
    Forms("Form1").Controls(Count).Name = "myButton" & LTrim(CStr(80 + Count))
  Next Count
  
  DoCmd.Close acForm, "Form1", acSaveYes
    
End Sub

Answer 2

Thanks for your quick response Stefan,

Here is the code I use and a temporary form to test your code:

Public Sub ChangeControls()

  DoCmd.OpenForm "FormChangecontrolsTemp", acDesign
  
  Dim Count As Long
  
  For Count = 0 To Forms("FormChangecontrolsTemp").Controls.Count - 1
    Debug.Print Forms("FormChangecontrolsTemp").Controls(Count).Name
    Forms("FormChangecontrolsTemp").Controls(Count).Name = "INPRD" & LTrim(CStr(81 + Count))
  Next Count
  
  DoCmd.Close acForm, "FormChangecontrols", acSaveYes
    
End Sub

It seems to change all the controls name correctly, in that example to 1 to 80 were changed to 81 to 160, however even if the tabulation was correct, because the controls are in order, the names changes randomly in the form and I don't see any sense on how they assign the new name, I run the code from the execution window and make sure that the tab were ok before, I have 10 buttons on each row and 8 rows.

First row INPRD1 To INPRD10

Second row INPRD11 to INPRD20

up to

INPRD71 to INPRD80

It replace the INPRD1 by the INPRD84, I thought I found the sequence logic but it does not apply it to the end of the changes INPRD1 should be INPRD81, INPRD2 should be INPRD82 etc..

It seems that it replace in that order...

INPRD4

INPRD2

INPRD3

INPRD1

INPRD5

INPRD15

INPRD11

INPRD13

INPRD12

INPRD14

INPRD23

INPRD22

INPRD25

INPRD24

INPRD21

INPRD33

INPRD35

INPRD32

INPRD34

INPRD31

INPRD41

INPRD44

INPRD45

INPRD43

INPRD42

INPRD9

INPRD7

INPRD8

INPRD6

INPRD10

INPRD20

INPRD16

INPRD18

INPRD17

INPRD19

INPRD28

INPRD27

INPRD30

INPRD29

INPRD26

INPRD38

INPRD40

INPRD37

INPRD39

INPRD36

INPRD46

INPRD49

INPRD50

INPRD48

INPRD47

INPRD53

INPRD52

INPRD55

INPRD54

INPRD51

INPRD63

INPRD65

INPRD62

INPRD64

INPRD61

INPRD71

INPRD74

INPRD75

INPRD73

INPRD72

INPRD58

INPRD57

INPRD60

INPRD59

INPRD56

INPRD68

INPRD70

INPRD67

INPRD69

INPRD66

INPRD76

INPRD79

INPRD80

INPRD78

INPRD77

---

Claude Larocque

Answer 3

hmm, use the old name as offset:

Option Compare Database
Option Explicit

Public Sub ChangeControls()

  Const FORM_NAME As String = "Copy Of Form1"

  DoCmd.OpenForm FORM_NAME, acDesign
  
  Dim Count As Long
  Dim ControlNumber As Long
  
  For Count = 0 To Forms(FORM_NAME).Controls.Count - 1
    Debug.Print Forms(FORM_NAME).Controls(Count).Name
    ' Extract number from control name.
    ' My test controls are named CommandXXX.
    ControlNumber = Mid(Forms(FORM_NAME).Controls(Count).Name, 8)
    Forms(FORM_NAME).Controls(Count).Name = "INPRD" & CStr(81 + ControlNumber)
  Next Count
  
  DoCmd.Close acForm, FORM_NAME, acSaveYes
    
End Sub

Answer 4

Thanks again, I tried this and I received an execution error 13 on the line ControlNumber = ...

Public Sub ChangeControls2()
  Const FORM_NAME As String = "FormChangecontrolsTemp"

  DoCmd.OpenForm FORM_NAME, acDesign
  
  Dim Count As Long
  Dim ControlNumber As Long
  
  For Count = 0 To Forms(FORM_NAME).Controls.Count - 1
    Debug.Print Forms(FORM_NAME).Controls(Count).Name
    ' Extract number from control name.
    ' My test controls are named CommandXXX.
    ControlNumber = Mid(Forms(FORM_NAME).Controls(Count).Name, 8)
    Forms(FORM_NAME).Controls(Count).Name = "INPRD" & CStr(81 + ControlNumber)
  Next Count
  
  DoCmd.Close acForm, FORM_NAME, acSaveYes
End Sub

Claude

---

Claude Larocque

Answer 5

You have to change

ControlNumber = Mid(Forms(FORM_NAME).Controls(Count).Name, 8)

into ControlNumber = Mid(Forms(FORM_NAME).Controls(Count).Name, 6)

Answer 6

I am looking for a way to change controls names on a form using VBA, this is only a one time change so no needs to have it run later by users.

So on the form I have 80 buttons named INPRD1 to INPRD80, If I want to change the name to INPRD321 to INPRD400 what could I do... I have this that I need to be completed:

Hi Claude,

Not an answer to your question, but an other thought.

Probably you need a different form to handle the INPRD's from 321 to 400, and therefore you have copied the form for INPRD's t to 80.

You could also use the original form thereby reducing the high number INPRD's to low number INPRD's using:

    low number = (high number MOD 80).

This latter is more or less my way of working in which I have reduced the number of used forms considerably.

Imb.

 

Answer 7

Seems to me that a modification of your original code should work

Dim i As Integer
For i = 1 To 80
Me.Controls ("INEMP" & i).Name = "INPRD" & i + 320
Next i

Answer 8

Thanks Alphonse, it works like a charm, of course I am convince with Imb and Stefan code in could work, but with my original code, something was getting on the way.

For future users that might needs to change all the controls name, here is the final code:

Public Sub ChangeControls()

  DoCmd.OpenForm "0000-a-Menu Rapide 7A", acDesign
  
Dim i As Integer
For i = 321 To 400
Forms![0000-a-Menu Rapide 7A].Controls("INPRD" & i).Name = "INPRD" & i + 160
Next i
    DoCmd.Close acForm, "0000-a-Menu Rapide 7A", acSaveYes
    
End Sub

Thanks all to help me

Claude

---

Claude Larocque

Answer 9

Public Sub ChangeControls()

  DoCmd.OpenForm "0000-a-Menu Rapide 7A", acDesign
  
Dim i As Integer
For i = 321 To 400
Forms![0000-a-Menu Rapide 7A].Controls("INPRD" & i).Name = "INPRD" & i + 160
Next i
    DoCmd.Close acForm, "0000-a-Menu Rapide 7A", acSaveYes
    
End Sub

Hi Claude,

In your code above you name the controls from 481 to 560. Was that your intention?

Imb.

Answer 10

Yes Imb,

I have 42 forms with 1A to 42A and these are necessary for the moment, because my program is a cash register software with 80 buttons on each one for a total of 3360 buttons. Each one is related to a product and I know I can use only 1 form but I have also code be able to change the color on each button by the users.

So far this is what I have, but I am open to find a way to shorten the use of all these forms if you want to help. It is working fine at the moment and you have to keep in mind what is the function of the program. When a user is entering an order, everything has to be very fast, so when I load the invoicing form, I load these 42 forms in the background (Hidden) so the users when he wants to choose a product he or she can't wait even 2 seconds that the program is loading. For example on form 7A the buttons names are INPRD481 to INPRD560, if the form is not loaded, then it takes 2 or 3 seconds to load, if it is loaded, then the products appears instantly.

But again if you want to help me please let me know and I can open a new discussion on this topics.

Sincerely,

Claude

---

Claude Larocque

Answer 11

I have 42 forms with 1A to 42A and these are necessary for the moment, because my program is a cash register software with 80 buttons on each one for a total of 3360 buttons. Each one is related to a product and I know I can use only 1 form but I have also code be able to change the color on each button by the users.

...

But again if you want to help me please let me know and I can open a new discussion on this topics.

Hi Claude,

When I read this, I would invite you to pass by when you are here in the neighborhood.

I am convinced that you can reduce the 42 forms to only 1. User specific conditions can be stored in some kind of definition table. Using such an approach I use only a few different forms in any application, that are tuned by definition tables.

But to be of any help on this moment, I need to understand the whole process flow with regard to these forms.

Imb.