Pb dans le code [Résolu]

Signaler
Messages postés
10
Date d'inscription
mardi 15 juillet 2008
Statut
Membre
Dernière intervention
22 juillet 2008
-
Messages postés
228
Date d'inscription
mercredi 13 décembre 2000
Statut
Membre
Dernière intervention
8 août 2011
-
re bonjour

j'ai a nouveau un bout de code qui pose problème je ne connais pas la syntaxe exacte... le pb est en rouge
je voudrais qu'il ne reste qu'une seule valeur par ligne pour les colone E à I...

      Function Case2(s_Row As Integer)



      Application.ScreenUpdating = False



          aIf Cells(s_Row, i_column2).Value = 1 Then
              s_Formula = """"""
              s_Operator = xlBetween
              s_Type = xlValidateCustom

              b_DeleteValue = True
        
          End If
      For b = i_FirstColumn1 To i_LastColumn1
           If b <> i_column2 Then
               With Cells(s_Row, b).Validation
                  .Delete
                  .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
                  .IgnoreBlank = True
                  .InCellDropdown = True
                  .InputTitle = ""
                  .ErrorTitle = ""
                  .InputMessage = ""
                  .ErrorMessage = ""
                  .ShowInput = True
                  .ShowError = True
              End With
          End If
      Next b          If b_DeleteValue True Then Cells(s_Row, i_column1).Value "" and Cells(s_Row, i_column3).Value = "" and Cells(s_Row, i_column4).Value = "" and Cells(s_Row, i_column5).Value = "" and 

      End Function

3 réponses

Messages postés
1229
Date d'inscription
vendredi 16 novembre 2007
Statut
Membre
Dernière intervention
16 juin 2009
1
Bonjour,

Function Case2(s_Row As Integer) --> ta fonction renvoi quoi comme information??? de quel type???      Application.ScreenUpdating = False

          aIf Cells(s_Row, i_column2).Value = 1 Then   --> i_column2 est défini quelque part??? idem pour s_Formula, s_Operator, s_Type, b_Delete...
              s_Formula = """"""                             
              s_Operator = xlBetween
              s_Type = xlValidateCustom
              b_DeleteValue = True
        
          End If
      For b = i_FirstColumn1 To i_LastColumn1
           If b <> i_column2 Then
               With Cells(s_Row, b).Validation
                  .Delete
                  .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
                  .IgnoreBlank = True
                  .InCellDropdown = True
                  .InputTitle = ""
                  .ErrorTitle = ""
                  .InputMessage = ""
                  .ErrorMessage = ""
                  .ShowInput = True
                  .ShowError = True
              End With
          End If
      Next b
          If b_DeleteValue = True Then             Cells(s_Row, i_column1).Value "" and Cells(s_Row, i_column3).Value "" and Cells(s_Row, i_column4).Value = "" and Cells(s_Row, i_column5).Value = "" and --> et après????

      End Function

C'est du vba??? si c'est le cas tu t'es trompé de section...
Peux tu nous dire ce que tu souhaites faire avec cette fonction?

S.L.B.

<hr />*********************************************************************************
Pensez à valider les réponses... Pensez aux autres...
*********************************************************************************
Messages postés
228
Date d'inscription
mercredi 13 décembre 2000
Statut
Membre
Dernière intervention
8 août 2011

Vous êtes ici : Thèmes / Visual Basic 6 / Divers / Débutants

Il y a une section pour le Code VBA ;)
Messages postés
10
Date d'inscription
mardi 15 juillet 2008
Statut
Membre
Dernière intervention
22 juillet 2008

Merci pour ta réponse, je vais essayer de corriger tt ca et de répondre à tes interrogations ( désolé je ne connais pas beaucoup j'ai bidouillé ce que j'ai pu)

1) Function Case2(s_Row As Integer) --> ta fonction renvoi quoi comme information??? de quel type???


      j'ai pas compris la question...je debute...j'ai trouver cette fonction sur une autre marco mais je sais pas ce que ca veut dire.


2) Oui également pour les variable elles sont bien definis plus haut.
               Const i_column1 = 5
            Const i_column2 = 6
            Const i_column3 = 7
            Const i_column4 = 8
            Const i_column5 = 9


            Dim s_Formula, s_Operator, s_Type, i, s_Row, a, b, c, d, e
            Dim b_DeleteValue As Boolean

3) Oui oui c'est du vba, c'est une macro pour excel.
 
4) le dernier "and" est en trop
correction => 
 If b_DeleteValue = True Then        Cells(s_Row, i_column1).Value "" and Cells(s_Row, i_column3).Value "" and Cells(s_Row, i_column4).Value = "" and Cells(s_Row, i_column5).Value = "" 
en gros je voudrais que les cellules de la ligne comprise entre les colonnes E et I soient supprimés sauf la cellules ou je veux la valeur...

voici le code que j'ai dans sa totalité:


 



Option Explicit



Const s_Refusee = "Refusée"
Const s_Attente = "Mise en attente"
Const s_Rien = ""
Const i_ColumnEtat = 2
Const i_FirstColumn1 = 5
Const i_LastColumn1 = 9
Const i_FirstColumn2 = 10
Const i_LastColumn2 = 13
Const i_column1 = 5
Const i_column2 = 6
Const i_column3 = 7
Const i_column4 = 8
Const i_column5 = 9



Dim s_Formula, s_Operator, s_Type, i, s_Row, a, b, c, d, e
Dim b_DeleteValue As Boolean
--------------------------------------------------------------------------------------------------------------



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
    If Target.Column = i_ColumnEtat Then
        ChangeValidation (Target.Row)
   
    ElseIf Target.Column = i_column1 Then
        Case1 (Target.Row)
       
    ElseIf Target.Column = i_column2 Then
        Case2 (Target.Row)
       
    ElseIf Target.Column = i_column3 Then
        Case3 (Target.Row)
       
    ElseIf Target.Column = i_column4 Then
        Case4 (Target.Row)
       
    ElseIf Target.Column = i_column5 Then
        Case5 (Target.Row)
    End If
   
End Sub



--------------------------------------------------------------------------------------------------------------------
Function ChangeValidation(s_Row As Integer)



    Application.ScreenUpdating = False    If Cells(s_Row, i_ColumnEtat).Value s_Refusee Or Cells(s_Row, i_ColumnEtat).Value s_Attente Then
        s_Formula = """"""
        s_Operator = xlBetween
        s_Type = xlValidateCustom
        b_DeleteValue = True
       
    Else
        s_Formula = "1"
        s_Operator = xlEqual
        s_Type = xlValidateList
        b_DeleteValue = False
    End If
   
    For i = i_FirstColumn1 To i_LastColumn1
        With Cells(s_Row, i).Validation
            .Delete
            .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With        If b_DeleteValue True Then Cells(s_Row, i).Value ""
    Next
        If Cells(s_Row, i_ColumnEtat).Value s_Refusee Or Cells(s_Row, i_ColumnEtat).Value s_Attente Then
        s_Formula = """"""
        s_Operator = xlBetween
        s_Type = xlValidateCustom
        b_DeleteValue = True
   
    Else
        s_Formula = "x"
        s_Operator = xlEqual
        s_Type = xlValidateList
        b_DeleteValue = False
    End If
   
    For i = i_FirstColumn2 To i_LastColumn2
        With Cells(s_Row, i).Validation
            .Delete
            .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With        If b_DeleteValue True Then Cells(s_Row, i).Value ""
    Next
    Application.ScreenUpdating = True
End Function



---------------------------------------------------------------------------------------------------------------------



Function Case1(s_Row As Integer)



Application.ScreenUpdating = False



    If Cells(s_Row, i_column1).Value = 1 Then
        s_Formula = """"""
        s_Operator = xlBetween
        s_Type = xlValidateCustom
        b_DeleteValue = True
       
    End If
For a = i_column2 To i_LastColumn1
    With Cells(s_Row, a).Validation
            .Delete
            .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
    End With    If b_DeleteValue True Then Cells(s_Row, a).Value ""
Next a
End Function
---------------------------------------------------------------------------------------------------------------------



Function Case2(s_Row As Integer)



Application.ScreenUpdating = False



    If Cells(s_Row, i_column2).Value = 1 Then
        s_Formula = """"""
        s_Operator = xlBetween
        s_Type = xlValidateCustom
        b_DeleteValue = True
       
    End If
For b = i_FirstColumn1 To i_LastColumn1
     If b <> i_column2 Then
         With Cells(s_Row, b).Validation
            .Delete
            .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End If
Next b
   If b_DeleteValue = True Then        Cells(s_Row, i_column1).Value "" and Cells(s_Row, i_column3).Value "" and Cells(s_Row, i_column4).Value = "" and Cells(s_Row, i_column5).Value = ""  
         
End Function
-----------------------------------------------------------------------------------------------------------------------



Function Case3(s_Row As Integer)



Application.ScreenUpdating = False



    If Cells(s_Row, i_column3).Value = 1 Then
        s_Formula = """"""
        s_Operator = xlBetween
        s_Type = xlValidateCustom
        b_DeleteValue = True
       
    End If
For c = i_FirstColumn1 To i_LastColumn1
     If c <> i_column3 Then
         With Cells(s_Row, c).Validation
            .Delete
            .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End If   ' If b_DeleteValue True Then Cells(s_Row, i).Value ""
Next c
End Function
---------------------------------------------------------------------------------------------------------------------------



Function Case4(s_Row As Integer)



Application.ScreenUpdating = False



    If Cells(s_Row, i_column4).Value = 1 Then
        s_Formula = """"""
        s_Operator = xlBetween
        s_Type = xlValidateCustom
        b_DeleteValue = True
       
    End If
For d = i_FirstColumn1 To i_LastColumn1
    If d <> i_column4 Then
        With Cells(s_Row, d).Validation
            .Delete
            .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End If    'If b_DeleteValue True Then Cells(s_Row, i).Value ""
Next d
End Function



----------------------------------------------------------------------------------------------------------------------------
Function Case5(s_Row As Integer)



Application.ScreenUpdating = False



    If Cells(s_Row, i_column5).Value = 1 Then
        s_Formula = """"""
        s_Operator = xlBetween
        s_Type = xlValidateCustom
        b_DeleteValue = True
       
    End If
For e = i_FirstColumn1 To i_column4
        With Cells(s_Row, e).Validation
            .Delete
            .Add Type:=s_Type, AlertStyle:=xlValidAlertStop, Operator:=s_Operator, Formula1:=s_Formula
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
    End With    'If b_DeleteValue True Then Cells(s_Row, i).Value ""
Next e
End Function