pb dans le codeRésolu

Question

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

lillith212 · Accepted Answer

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.

********************************************************************************* Pensez à valider les réponses... Pensez aux autres... *********************************************************************************

cs_julienb25 · Answer

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

bonjourcheval · Answer

Vous êtes ici : Thèmes / Visual Basic 6 / Divers / Débutants

Il y a une section pour le Code VBA ;)

Pb dans le code

3 réponses

Votre réponse

Discussions similaires