Astuces Excel
  1. Astuces Excel
  3. VBA – Ouvrez un UserForm en cliquant n'importe où dans une colonne spécifique
Intereting Posts
Gamme d'index – critères multiples – un qui est max. PHP Spreadsheet_Excel_Reader toujours la date de lecture le 30 novembre 1999 Fichiers sqlite3 ou CSV Excel DLL d'appel VBA développé à l'aide de MPI Copier la dernière ligne d'une feuille et coller dans la dernière ligne d'une feuille différente VBA Tri de Excel via Word ~ ​​error 1004 Impossible d'installer VST0 Excel addin pour tous les users dans Excel 2007 Comment puis-je quantifier les valeurs dans une colonne Excel en fonction d'un mois donné? Supprimez le encoding dur du 'path du file' dans Excel en appelant l'autre classur Table complexe rowspan et colspan Remplir Excel avec des données de LINQ à la requête SQL Table Excel utilisant Powershell Erreur dans mon code de contrôle de qualité statistique Code VB Obtention du message "Erreur d'logging du file" (ou) System.OutOfMemoryException (ou) ContextSwitchDeadlock a été détecté Excel VBA renvoie des résultats étranges avec de gros calculs

VBA – Ouvrez un UserForm en cliquant n'importe où dans une colonne spécifique

Je voudrais créer un makro dans VBA qui ouvre un UserForm lorsque je clique sur une cellule dans une colonne spécifique, pour plus de détails, regardez ici .

Avec ce code (de Mr.Burns ): 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then If Not Intersect(Target, Range("A1")) Is Nothing Then 'name of userform .Show End If End If End Sub

J'ai pu ouvrir le UserForm en cliquant dans la cellule A1, mais pas en cliquant dans n'importe quelle cellule dans la colonne A.

J'ai essayé de résoudre ce problème avec ce code: 

 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub Private Sub Worksheet_SelectionChange (ByVal Target As Range) Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub Si Selection.Count = 1 Then Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub Dim vérifier comme boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub check = True Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub Si check Then Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub Dim i As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub Pour i = 1 à 100000 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub Si Non Intersect (Target, Range ("A" & i)) Rien n'est alors Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub UserForm1.Show Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub check = False Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then Dim check As Boolean check = True If check Then Dim i As Long For i = 1 To 100000 If Not Intersect(Target, Range("A" & i)) Is Nothing Then UserForm1.Show check = False End If Next End If End If End Sub

Cela fonctionne bien, mais c'est très lent, y a-t-il une meilleure possibilité de résoudre cela?

Pour afficher le formulaire lorsqu'une cellule est sélectionnée dans la colonne A: 

 Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' if target is one cell and in column A If Target.Columns.count = 1 And Target.Rows.count = 1 And Target.Column = 1 Then UserForm1.Show End If End Sub Private Sub Worksheet_SelectionChange (ByVal Target As Range) Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' if target is one cell and in column A If Target.Columns.count = 1 And Target.Rows.count = 1 And Target.Column = 1 Then UserForm1.Show End If End Sub 'si la cible est une cellule et dans la colonne A Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' if target is one cell and in column A If Target.Columns.count = 1 And Target.Rows.count = 1 And Target.Column = 1 Then UserForm1.Show End If End Sub Si Target.Columns.count = 1 et Target.Rows.count = 1 et Target.Column = 1 Then Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' if target is one cell and in column A If Target.Columns.count = 1 And Target.Rows.count = 1 And Target.Column = 1 Then UserForm1.Show End If End Sub UserForm1.Show Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' if target is one cell and in column A If Target.Columns.count = 1 And Target.Rows.count = 1 And Target.Column = 1 Then UserForm1.Show End If End Sub

Vous pouvez utiliser la propriété .count et .column avec AND et cela deviendra tellement simple et rapide. Le code suivant triggers le pop-up si vous click la colonne A sur la feuille active 

 Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo errorhandler If Target.Count = 1 And Target.Column = 1 Then '.count to check if only one cell is selected and .column to check if it is a first column 'UserForm1.Show 'Do whatever you want to do here like opening User form MsgBox "You clicked in column A" End If errorhandler: End Sub Private Sub Worksheet_SelectionChange (ByVal Target As Range) Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo errorhandler If Target.Count = 1 And Target.Column = 1 Then '.count to check if only one cell is selected and .column to check if it is a first column 'UserForm1.Show 'Do whatever you want to do here like opening User form MsgBox "You clicked in column A" End If errorhandler: End Sub On Error GoTo handheld d'erreur Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo errorhandler If Target.Count = 1 And Target.Column = 1 Then '.count to check if only one cell is selected and .column to check if it is a first column 'UserForm1.Show 'Do whatever you want to do here like opening User form MsgBox "You clicked in column A" End If errorhandler: End Sub Si Target.Count = 1 et Target.Column = 1 Ensuite, '.count pour vérifier si une seule cellule est sélectionnée et .column pour vérifier si c'est une première colonne Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo errorhandler If Target.Count = 1 And Target.Column = 1 Then '.count to check if only one cell is selected and .column to check if it is a first column 'UserForm1.Show 'Do whatever you want to do here like opening User form MsgBox "You clicked in column A" End If errorhandler: End Sub 'UserForm1.Show Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo errorhandler If Target.Count = 1 And Target.Column = 1 Then '.count to check if only one cell is selected and .column to check if it is a first column 'UserForm1.Show 'Do whatever you want to do here like opening User form MsgBox "You clicked in column A" End If errorhandler: End Sub «Faites ce que vous voulez faire ici comme ouverture du formulaire user Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo errorhandler If Target.Count = 1 And Target.Column = 1 Then '.count to check if only one cell is selected and .column to check if it is a first column 'UserForm1.Show 'Do whatever you want to do here like opening User form MsgBox "You clicked in column A" End If errorhandler: End Sub MsgBox "Vous avez cliqué dans la colonne A" Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo errorhandler If Target.Count = 1 And Target.Column = 1 Then '.count to check if only one cell is selected and .column to check if it is a first column 'UserForm1.Show 'Do whatever you want to do here like opening User form MsgBox "You clicked in column A" End If errorhandler: End Sub tueur à erreurs: Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo errorhandler If Target.Count = 1 And Target.Column = 1 Then '.count to check if only one cell is selected and .column to check if it is a first column 'UserForm1.Show 'Do whatever you want to do here like opening User form MsgBox "You clicked in column A" End If errorhandler: End Sub