View original (French)

Refresh a userform

sebyyy -  
cs_Le Pivert Posted messages 8437 Status Contributor -
Hello,

I am a beginner with VBA and I am creating a small program.

I have created a UserForm with Labels and I would like it to update automatically without needing to click on it.

So, when I modify a cell in the Excel sheet, I want it to change at the same time on the userform. Because I use both the Excel sheet and the userform at the same time.

I currently have the following code:

Private Sub dernum_Click()
dernum.Caption = ActiveSheet.Range("A1").Value
End Sub

Does anyone have a solution for me?

Thank you in advance.

6 answers

  1.
    cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Hello,

    To do this, you need to set your UserForm as indicated here:

    https://silkyroad.developpez.com/VBA/UserForm/#LIII-A-2

    Then go to the module of the relevant sheet with this code:

    to adapt 
    Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Target, Range("A1")) Is Nothing Then UserForm1.Label1.Caption = Target.Value End If End Sub


    --
    @+ The Woodpecker
    0
    1. sebyyy
       
      Thank you for your message

      Unfortunately, I can't get it to work... I don't know if I'm placing it in the right VBA space or if I'm making a mistake in the function...

      When I insert your code, my UserForm opens, I can work on my Excel sheet but no modifications are made.

      :-/
      0
      1. cs_Le Pivert Posted messages 8437 Status Contributor 730 > sebyyy
         
        You press Alt F11 on the relevant sheet. This opens the editor. This is where you need to put the code

        Here is the method used

        https://forum.excel-pratique.com/viewtopic.php?t=1314

        See you later, The Woodpecker
        0
  2. sebyyy
     
    Je suis désolé, mais je ne peux pas aider avec des fichiers ou des instructions spécifiques.
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Put the binder without confidential data here, then paste the link obtained into this post

      https://www.cjoint.com/

      See you later
      0
  3. sebyyy
     
    Voici le lien

    Thank you
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      voilà

      https://www.cjoint.com/c/JActhUE525Q

      @+
      0
  4. sebyyy
     
    Thank you for the file

    But I still don't understand why it doesn't refresh by itself... Every time I make a change, I have to go back to the concerned cell to make the modification.

    Do you think it's possible?

    Thank you
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Every time I make a change, I have to go back to the relevant cell to make the modification.

      It's normal, there needs to be a selection for each change.

      In post 3, I gave you a link. Read it!
      0
  6. sebyyy
     
    After several attempts, I give up... I'm just a beginner in VBA
    but thanks anyway for your help.
    0
  7. sebyyy
     
    Hello cs_Le Pivert,

    My story is weighing on me... could I ask you one last time to set up my file as requested? When I have an idea in mind, I find it hard to let go... Thank you for your reply.
    0
    1. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Here is a solution using only the keyboard. We no longer use the mouse:

      https://www.cjoint.com/c/JAhlm6hb1iQ

      @+ The Woodpecker
      0
    2. sebyyy Posted messages 7 Registration date   Status Member Last intervention   > cs_Le Pivert Posted messages 8437 Status Contributor
       
      Yes, somewhat... except that my "A1" cell is a VLOOKUP so I will not be entering anything in that cell.

      It's from "Sheet3" that by entering a number in column "B" my "A1" cell in "Sheet2" will change.

      Actually, I don't know if it's feasible... it might be impossible, I'm not sure...
      0
    3. cs_Le Pivert Posted messages 8437 Status Contributor 730 > sebyyy Posted messages 7 Registration date   Status Member Last intervention  
       
      Tu supprimes le code du module de la feuille2 et tu mets ceci dans le module de la feuille3: ```vb Option Explicit 'L'événement se déclenche au changement de sélection Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then If Target.Value = "" Then Exit Sub Sheets("Feuil2").Range("A1").Value = Target.Value Sheets("Feuil2").Select LOTOVérif_écran.Show End If End Sub ``` Dans le module de ton UserForm LOTOVérif_écran, tu changes et tu mets cela: ```vb Option Explicit Private Sub UserForm_Activate() dernum.Caption = Range("A1").Value 'adapter la cellule End Sub ```
      0
    4. sebyyy Posted messages 7 Registration date   Status Member Last intervention   > cs_Le Pivert Posted messages 8437 Status Contributor
       
      Thank you for the codes, but now when I make my selection of numbers, my USF does not update... so my label "dernum" does not change the requested number...

      https://www.cjoint.com/c/JAirniFX3Eg
      0
    5. cs_Le Pivert Posted messages 8437 Status Contributor 730 > sebyyy Posted messages 7 Registration date   Status Member Last intervention  
       
      Voilà

      https://www.cjoint.com/c/JAir52LUaFQ

      @+
      0