View original (French)

Run an Excel macro with the Enter key

Solved
Splity -  
 Splity -
Hello everyone,
It's been a little while since I've been browsing the forums but I can't find an answer to my problem, which is as follows:

I wrote a VBA macro to search in a table. The user enters their 6 criteria in 6 defined cells and then launches the search with a button.
Alongside the button, I would like to be able to launch the macro (which already exists and works) using the Enter key only if one of the input cells is active.

Here are two of my attempts, which do not work...

Private Sub Worksheet_Change(ByVal target As Range)
If KeyAscii = "13" Then
macrorecherche
End If
End Sub

Private Sub Worksheet_Change(ByVal target As Range)
If ActiveCell.Range("A13") = True And Chr$(13) = True Then
macrorecherche
End If
End Sub

Thank you for your help!
Configuration: Windows XP Internet Explorer 6.0

7 answers

  1.
    gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    To make this work, simply enter text in the text boxes inserted into the relevant cells.
    --

    Always zen
    0
  2. Splity
     
    Pour insérer un TextBox ou un ListBox dans une cellule, vous pouvez suivre ces étapes selon l'application que vous utilisez (comme Excel ou un environnement de développement similaire) :

    1. **Excel :**
    - Accédez à l'onglet "Développeur". Si cet onglet n'est pas visible, vous devez l'activer dans les options.
    - Cliquez sur "Insérer" dans le groupe "Contrôles".
    - Choisissez le contrôle "Zone de texte" ou "Liste déroulante" et dessinez-le sur la feuille.
    - Vous pouvez ensuite ajuster la taille et la position du contrôle pour qu’il s'adapte à la cellule souhaitée.

    2. **VBA :**
    - Ouvrez l'éditeur VBA (ALT + F11).
    - Dans le projet, insérez un nouveau UserForm.
    - Ajoutez un TextBox ou un ListBox à ce formulaire.
    - Utilisez le code pour afficher le UserForm à partir d'une cellule spécifique.

    3. **Autres environnements :**
    - Consultez la documentation de l'environnement que vous utilisez, car les étapes peuvent varier.

    Assurez-vous que le contrôle est bien lié à la cellule si nécessaire pour enregistrer les données.
    0
  3. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    You select the textbox in the "controls toolbox", you hold down alt and you click and drag over the chosen cell and your textbox will take the size of your cell.

    Then you use the change function of your textbox to trigger your search.
    --

    Always stay calm
    0
  4. Splity
     
    ok, thanks for the text boxes, the input area looks more presentable!

    However, I had already tried to validate with the "Enter" key in text boxes but it didn't work either. Here is the code I found on another forum and used by inserting it into my page containing the text boxes.

    Private Sub tbfamille_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    KeyAscii = Asc(UCase(Chr(KeyAscii)))

    If KeyAscii = "15" Then
    macrorecherche
    End If

    End Sub

    Could someone tell me what is wrong? What should I change?

    Thank you and thank you gbinforme!
    0
  6. gilou
     
    bonjour
    je ne connais pas grand-chose mais essaie de remplacer worksheet_change par worksheet_selection_change
    0
  7. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    Can someone tell me what is wrong? What should I change?


    Just look at the Excel help! 
    A KeyPress event can be triggered when one of the following keys is pressed: Any printable character on the keyboard. CTRL combined with a standard alphabet character. CTRL combined with a special character. SPACE ESC. A KeyPress event does not trigger when the user presses one of the following keys: TAB. ENTER. A directional key. A key that moves focus from one control to another.

    So ctrl + enter gives the code 10 and not 15.
    --

    Always zen
    0
  8. Splity
     
    It means that it is not possible to run a macro from a textbox by simply pressing the Enter key?

    I wanted to add this feature because it is intuitive for users and avoids having to click on the "Search" button.

    If that’s the case, too bad, but thank you all for your help and well done for this site!
    0