Changer de cellule active

Question

Bonjour, je suis en train de preparer une feuille de calcul et j'aimerai pouvoir le curseur de la cellule active que j'ai rempli &#224; la nouvelle cellule &#224; remplir en pressant le bouton entr&#233;e.
sachant que celle ci ne se suivent pas ou ne sont pas toutes sur la meme ligne ou colonne.

comment coder cela en VBA? ou sinon, comment le faire en utilisant les utilitaires de xls?
merci!:!

valtrase · Answer

Lut,
Voila un bout de code pour d&#233;buter, bien sur tu dois le paufiner
par exemple rajouter  du code pour supprimer des c&#233;llules dans ta liste ou les fairent monter ou descendre dans la liste, intercepter la touche suppress  ect...
Si tu veux des explications n'h&#233;sites pas
Tu dois mettre un CommandButton sur ta feuille (le plus simple pour appeler ta UserForm)
Tu dois aussi cr&#233;er une UserForm
y ajouter : Une zone de liste, deux boutons
  
'****************************** 


'*** CODE A METTRE AU NIVEAU DE TA USERFORM 


'****************************** 


Private Sub CommandButton1_Click() 


  Dim RefCell As Range: Dim SaveTabs As String: Dim i 


  On Error Resume Next 


  Set RefCell =  Application.InputBox(prompt:= "S&#233;lectionnez une nouvelle c&#233;llule pour la  


&#187; tabulation" , _ 


       Type:=  8 ) 


  On Error GoTo 0 


  If RefCell.Cells.Count > 1 Then MsgBox "vous devez s&#233;lectionner qu'une c&#233;llule &#224; la fois"  


&#187; : Exit Sub 


  ListBox1.AddItem RefCell.Address 'Replace(RefCell.Address, "$", "") 


  For i  = LBound (ListBox1.List) To UBound (ListBox1.List) 


    SaveTabs =  SaveTabs & ListBox1.List(i, 0 ) & ";" 


  Next 


  If Right (SaveTabs, 1 ) ";" Then SaveTabs Left (SaveTabs, Len (SaveTabs) - 1 ) 


  SaveSetting Application.Name & " " & ActiveWorkbook.Name, "Options" , "Tabs" , SaveTabs 


End Sub 


Private Sub CommandButton2_Click() 


  Unload Me 


End Sub 


Private Sub UserForm_Initialize() 


  ListBox1.List = Split( GetSetting (Application.Name & " " & ActiveWorkbook.Name, "Options" , 


&#187; "Tabs" , "" ), ";" ) 


  With CommandButton1 


    .Caption = "D&#233;finir" 


    .Accelerator = "D" 


  End With 


  With CommandButton2 


    .Caption = "Sortir" 


    .Accelerator = "S" 


  End With 


End Sub 

'*****************************
'*** CODE A METTRE AU NIVEAU DE TA FEUILLE
'***************************** 
 Private Sub  CommandButton1_Click() 


  UserForm1.Show 


End Sub 


Private Sub Worksheet_Change( ByVal Target As Range) 


  Dim i 


  Dim xlTabs 


  'Static xlChange As Boolean 


  xlTabs  = Split( GetSetting (Application.Name & " " & ActiveWorkbook.Name, "Options" , "Tabs"  


&#187; , "" ), ";" ) 


  For i = LBound (xlTabs) To UBound (xlTabs) 


    If Target.Address = xlTabs(i) Then 


      i = i + 1 


      If i > UBound (xlTabs) Then Exit Sub 


      ActiveSheet.Range(xlTabs(i)).Select 


      'xlChange = True 


    End If 


  Next 


End Sub 

Cordialement, Jean-Paul  
______________________________________________________________________

Le Savoir n'a de valeur que s'il est partag&#233;

patrick003 · Answer

j'ai commenc&#233; la programmation et je bloque au niveau de l'aspect de lancement automatique du chargement et de l'execution automatique de mon code sous le userform.
sous la feuille qui dois executer les codes du userform, j'ai &#231;a:


Private Sub Bouton_Calculer_Click()
    Call General
    Sheets("simu").Select
    If controle_ko = 0 Then
        Range("I3").Select
    End If
    
End Sub


Private Sub Bouton_Initialiser_Click()
  Call initialisation_page
  
End Sub


Private Sub Worksheet_Activate()
    Cells(3, 9).Select
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Load FormSelect
    FormSelect.Show
End Sub


Private Sub Worksheet_Deactivate()
    FormSelect.Vider.TakeFocusOnClick = True
    Unload FormSelect
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Unload FormSelect
    'FormSelect.Hide
End Sub

sous le userform, j'ai ceci:

Public Sub Maliste_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim RefCel As Range
    last = Maliste.List(0, 0)
    If KeyAscii = 13 Then
        Application.Range(last).Activate
        Maliste.RemoveItem 0
        Maliste.AddItem last
    Else
        Exit Sub
    End If
    


End Sub


 Sub Loadlist()
    Dim RefCell As Range: Dim SaveTabs As String: Dim i
    Dim j1, j2
    Maliste.ColumnCount = 1
    On Error Resume Next
    Sheets("simu").slect
    For j2 = 1 To 46
        For j1 = 4 To 9
            If j2 >= ActiveCell.Row And j1 >= ActiveCell.Column Then
                Set RefCell = Cells(j2, j1)
            Else
                Set RefCell = Nothing
            End If
            On Error GoTo 0
            If RefCell.Interior.ColorIndex = 36 Then
                Maliste.AddItem RefCell.Address 'Replace(RefCell.Address, "$", "")
                On Error GoTo 0
            End If
        Next
    Next
  'Range("plage").Select
  '
   ' Set RefCell = Range("plage").Cells(j2, j1)
   ' On Error GoTo 0
    'If RefCell.Cells.Count > 1 Then MsgBox "vous devez s&#233;lectionner qu'une c&#233;llule &#224; la fois"
': Exit Sub
 '   If RefCell.Interior.ColorIndex = 36 Then
 '       Maliste.AddItem RefCell.Address 'Replace(RefCell.Address, "$", "")
  '      On Error Resume Next
  '  Else
  '      Resume Next
  '  End If
  '  Next
  'Next
  
  For i = LBound(Maliste.List) To UBound(Maliste.List)
    SaveTabs = SaveTabs & Maliste.List(i, 0) & ";"
  Next  If Right(SaveTabs, 1) ";" Then SaveTabs Left(SaveTabs, Len(SaveTabs) - 1)
  SaveSetting Application.Name & " " & ActiveWorkbook.Name, "Options", "Tabs", SaveTabs


End Sub


Private Sub UserForm_Click()
    Exit Sub
End Sub


Public Sub UserForm_Initialize()
    FormSelect.Hide
    Maliste.List = Split(GetSetting(Application.Name & " " & ActiveWorkbook.Name, "Options", " ", ""), ";")
    Call Loadlist
End Sub


Private Sub Vider_Click()
    Unload Me
End Sub



 


 LIONS

valtrase · Answer

Lut,
Dans mon code la UserForm ne sert qu'a param&#232;trer les tabulations tu ne dois pas l'appeller dans l'&#233;v&#232;nement Change de ta Feuille.
Cr&#233;e un nouveau fichier excel colle mon code poses des points d'arrets et regarde le fonctionnement, tu pouras plus facilement l'adapter &#224; ton cas.

Cordialement, Jean-Paul  
______________________________________________________________________

Le Savoir n'a de valeur que s'il est partag&#233;

patrick003 · Answer

merdci, j'ai re&#233;cri mon code et tout marche, je me deplace dans les cellules que j'ai predefi sans souci.

le voici le code: (si possibilites d'am&#233;liorations, je suis preneur.)

""""" sous le userform
Public last As String


 Sub Loadlist()
    Dim RefCell As Range: Dim SaveTabs As String: Dim i
    Dim j1, j2
    Maliste.ColumnCount = 1
    On Error Resume Next
    Sheets("simu").slect
    For j2 = 1 To 46
        For j1 = 4 To 9
            Set RefCell = Cells(j2, j1)
            On Error GoTo 0
            If RefCell.Interior.ColorIndex = 36 Then
                Maliste.AddItem RefCell.Address 'Replace(RefCell.Address, "$", "")
                On Error GoTo 0
            End If
        Next
    Next
    For i = LBound(Maliste.List) To UBound(Maliste.List)
        SaveTabs = SaveTabs & Maliste.List(i, 0) & ";"
    Next    If Right(SaveTabs, 1) ";" Then SaveTabs Left(SaveTabs, Len(SaveTabs) - 1)
        SaveSetting Application.Name & " " & ActiveWorkbook.Name, "Options", "Tabs", SaveTabs


End Sub


Private Sub Maliste_Click()


End Sub


Public Sub UserForm_Initialize()
     Maliste.List = Split(GetSetting(Application.Name & " " & ActiveWorkbook.Name, "Options", "Tabs", ""), ";")
    Call Loadlist
    FormSelect.Hide
End Sub


Sub ViderLIST()
    Unload Me
End Sub


"""""""  Sous la feuille qui change

Private Sub Bouton_Calculer_Click()
    Call General
    Sheets("simu").Select
    If controle_ko = 0 Then
        Range("I3").Select
    End If
    
End Sub


Private Sub Bouton_Initialiser_Click()
  Call initialisation_page
  
End Sub


Private Sub Worksheet_Activate()
    Cells(3, 9).Select
   
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    
   
    If Target.Address = "$I$3" Then     'Cells(3, 9)
        pas_i = 1
    Else    If Target.Address "$D$5" Then     'Target Cells(5, 4)
        pas_i = 2
    Else
    If Target.Address = "$D$6" Then
        pas_i = 3
    Else
    If Target.Address = "$I$6" Then
        pas_i = 4
    Else
    If Target.Address = "$D$7" Then
        pas_i = 5
    Else
    If Target.Address = "$D$8" Then
        pas_i = 6
    Else
    If Target.Address = "$I$8" Then
        pas_i = 7
    Else
    If Target.Address = "$D$9" Then
        pas_i = 8
    Else
    If Target.Address = "$D$10" Then
      pas_i = 9
    Else
    If Target.Address = "$I$10" Then
      pas_i = 10
    Else
    If Target.Address = "$D$11" Then
      pas_i = 11
    Else
    If Target.Address = "$D$12" Then
       pas_i = 12
    Else
    If Target.Address = "$D$14" Then
      pas_i = 13
    Else
    If Target.Address = "$D$15" Then
      pas_i = 14
      
    Else
    If Target.Address = "$D$16" Then
       pas_i = 15
    Else
    If Target.Address = "$D$17" Then
       pas_i = 16
    Else
    If Target.Address = "$I$17" Then
        pas_i = 17
    Else
    If Target.Address = "$D$18" Then
       pas_i = 18
    Else
    If Target.Address = "$I$18" Then
      pas_i = 19
    Else
    If Target.Address = "$D$19" Then
        pas_i = 20
    Else
    If Target.Address = "$D$20" Then
        pas_i = 21
    Else
    If Target.Address = "$D$23" Then
       pas_i = 22
    Else
    If Target.Address = "$I$23" Then
        pas_i = 23
    Else
    If Target.Address = "$D$25" Then
        pas_i = 24
    Else
    If Target.Address = "$D$26" Then
        pas_i = 25
    Else
    
    If Target.Address = "$D$27" Then
        pas_i = 26
    Else
    If Target.Address = "$I$27" Then
        pas_i = 27
    Else
    If Target.Address = "$D$28" Then
        pas_i = 28
    Else
    If Target.Address = "$I$28" Then
       pas_i = 29
    Else
    If Target.Address = "$D$29" Then
       pas_i = 30
    Else
    If Target.Address = "$I$29" Then
        pas_i = 31
    Else
    If Target.Address = "$D$30" Then
        pas_i = 32
    Else
    If Target.Address = "$I$30" Then
        pas_i = 33
    Else
    If Target.Address = "$D$32" Then
        pas_i = 34
    Else
    If Target.Address = "$I$33" Then
      pas_i = 35
    Else
    If Target.Address = "$D$35" Then
        pas_i = 36
    Else
    If Target.Address = "$I$35" Then
        pas_i = 37
    Else
    If Target.Address = "$D$36" Then
        pas_i = 38
    Else
    If Target.Address = "$I$36" Then
        pas_i = 39
    Else
    If Target.Address = "$D$43" Then
        pas_i = 40
    Else
    If Target.Address = "$D$44" Then
        pas_i = 41
    Else
    If Target.Address = "$D$45" Then
        pas_i = 42
    Else
         pas_i = 0
    End If: End If: End If: End If: End If: End If: End If: End If: End If: End If: End If
    End If: End If: End If: End If: End If: End If: End If: End If: End If: End If: End If
    End If: End If: End If: End If: End If: End If: End If: End If: End If: End If: End If: End If
    End If: End If: End If: End If: End If: End If: End If: End If


FormSelect.Maliste.List = Split(GetSetting(Application.Name & " " & ActiveWorkbook.Name, "Options", "Tabs", ""), ";")


FormSelect.Show vbModeless
    ActiveSheet.Range(FormSelect.Maliste.List(pas_i)).Select


FormSelect.Hide


End Sub


 LIONS

Changer de cellule active

4 réponses

Discussions similaires