none
Change several controls name at once RRS feed

  • 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

    Monday, October 5, 2015 8:15 AM

Answers

  • 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
    

    Monday, October 5, 2015 8:40 AM
  • 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
    Monday, October 5, 2015 9:37 AM
  • You have to change

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

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

    Monday, October 5, 2015 10:41 AM
  • 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

    Monday, October 5, 2015 12:53 PM

All replies

  • 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
    

    Monday, October 5, 2015 8:40 AM
  • 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


    Monday, October 5, 2015 9:15 AM
  • 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
    Monday, October 5, 2015 9:37 AM
  • 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

    Monday, October 5, 2015 9:52 AM
  • You have to change

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

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

    Monday, October 5, 2015 10:41 AM
  • 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.

     

    Monday, October 5, 2015 11:45 AM
  • 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

    Monday, October 5, 2015 12:53 PM
  • 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

    Monday, October 5, 2015 4:50 PM
  • 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.

    Monday, October 5, 2015 5:25 PM
  • 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

    Tuesday, October 6, 2015 9:06 AM
  • 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.

    Tuesday, October 6, 2015 10:02 AM