execution de macro sous condition...

[ph.vanne]

bonjour ,
je n'ai pas trouvé sur le site une reponse a ma question:

j'ai une macro qui fonctionne correctement mais j'aimerais avoir ''un bout de code''! pour l'empecher de s'executer si la cellule ''a1'' et la cellule ""b1"" sont vide avec ,pourquoi pas ,un message d'erreur du style :" plage 'nom du client' vide""
merci d'avance.
phil.

 

[Pierrot93]

Re : execution de macro sous condition...

bonjour

essaye peut être en testant ainsi :

If IsEmpty(Range("A1").Value) And IsEmpty(Range("B1").Value) Then
    MsgBox "cellules a1 et b1 vides."
Else
    'ton code
End If

bonne journée
@+

 

[vbacrumble]

Re : execution de macro sous condition...

Bonjour tous

Deux autres solutions

Sub videoupas()
Set r = Range("A1:B1")
MsgBox "A1 et/ou B1 " & (IIf(Application.CountBlank(r) > 1, "sont", "ne sont pas")) & " vides."
End Sub

Plus précis dans le message

Sub macro2()
Set r = Range("A1:B1")
MsgBox Choose(CInt(Application.CountBlank(r)) + 1, "A1 et B1 non vides", "A1 ou  B1 vide", "A1 et B1 vides")
End Sub

A+

 

[ph.vanne]

Re : execution de macro sous condition...

bonjour et merci , pour votre réponse trés rapide !,
mais il reste cependant un petit probleme! j'ai bien le message d'erreur si les cellules sont vides, mais je ne peux pas les remplir tant que le message est a l'ecran et si je fais 'fermer' la macro démarre , si je fait ''ok '' elle demarre aussi!

 

[PascalXLD]

Re : execution de macro sous condition...

Bonjour

Si tu appliques le code de Pierrot et que tu mets bien ton code après le else il ne se passera rien ta macro ne partira pas

sinon

if range("A1") ="" and range("B1")="" then
msgbox "on remplit !!!"
exit sub
end if

 

[ph.vanne]

Re : execution de macro sous condition...

je doit etre un peu bourrin!
je bloque!
si je place votre code comme ça marche pas ...?




Sub macro_impression_deux_pages_et_ajouter_1()

' Macro1 Macro
' Macro enregistrée le 04/10/2007 par UTILISATEUR


If Range("d9") = "" Then
MsgBox "on remplit !!!"









'gele l'ecran pendant le deroulement de la macro'

Application.ScreenUpdating = False

' ouvrir le dossier "factures" du mois en cour


Workbooks.Open Filename:="C:\Documents and Settings\Utilisateur\Mes documents\DOSSIERS HOTEL\SECRETARIAT\FACTURES HOTEL\factures sous excel\factures_fev_09.xls"

'revenir sur "ed de fature" et copier l'apercu de la facture


Windows("ed fact 2008-12.xls").Activate


Sheets("A5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True
Sheets("APERCU DE LA FACTURE").Select
Range("A1:G36").Select
Selection.Copy

' creer une nouvelle feuille dans le dossier archivage des factures '

Windows("factures_fev_09.xls").Activate
Sheets("0").Select
Sheets.Add


'mise en forme de la facture enregistrée
'

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C7").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1

Columns("A:A").ColumnWidth = 10.29
Columns("B:B").ColumnWidth = 30.29
Columns("C:C").ColumnWidth = 7.29
Columns("D😀").ColumnWidth = 1.71
Columns("E:E").ColumnWidth = 12
Columns("F:F").ColumnWidth = 18
Columns("G:G").ColumnWidth = 8.57
Range("G3").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Columns("G:G").ColumnWidth = 9.43
Range("F2").Select
Selection.NumberFormat = "d/m/yy;@"
Range("G3").Select
Selection.NumberFormat = "d/m;@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E8:F11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B9:B10").Select
Selection.NumberFormat = "d/m/yy;@"
Columns("D😀").ColumnWidth = 2.29
ActiveWindow.SmallScroll Down:=9
Range("C29:C30").Select
Selection.Style = "Currency"
Range("F18:F35").Select
Selection.Style = "Currency"
ActiveWindow.SmallScroll Down:=-24
Windows("ed fact 2008-12.xls").Activate
Range("F3").Select
Windows("factures_fev_09.xls").Activate
Range("D1").Select
Selection.ClearContents
Range("A9:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("E8:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.SmallScroll Down:=3
Range("B13:F35").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Range("E15:F34,C29:C30").Select
Range("C29").Activate
Selection.Style = "Currency"
Selection.Style = "Currency"
Selection.NumberFormat = "#,##0.00 €"
End With
With Range("B9:B10").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Range("B11").Select
Selection.NumberFormat = "General"
Range("E3").Select
ActiveCell.FormulaR1C1 = "FACTURE N°"
Range("F3").Select
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("B11").Select
Selection.NumberFormat = "General"" nuitée(s)"""
Range("G3").Select
Selection.ClearContents
Range("F2").Select
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").ColumnWidth = 36.29
Columns("B:B").ColumnWidth = 38.14
Columns("B:B").ColumnWidth = 41.71
Columns("B:B").ColumnWidth = 40.71
Range("B9:B11").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("C5").Select
Columns("F:F").ColumnWidth = 15.29
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
Columns("A:A").ColumnWidth = 8.43
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End With
With Range("E8:F11").Select
Selection.Font.Bold = True
Range("A9:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1:B7").Select
Selection.Font.Bold = True
Range("E2:F3").Select
Selection.Font.Bold = True
Cells.Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("B4").Select
Columns("B:B").ColumnWidth = 44
End With


With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B5").Select
Columns("B:B").ColumnWidth = 32.86
Range("B27").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B5").Select
Columns("B:B").ColumnWidth = 32
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"remise exeptionnelle (sur hebergement) :"
With ActiveCell.Characters(Start:=1, Length:=54).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"remise exeptionnelle (sur hebergement) :"
With ActiveCell.Characters(Start:=1, Length:=61).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("E8:F11").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With


'nommer la feulle crée par le n° de la facture'

With Range("B1").Select
ActiveSheet.Name = Range("f3").Value

'on enregistre le dossier factures et on ferme'

With Windows("factures_fev_09.xls").Activate

ActiveWorkbook.Save

ActiveWindow.Close

'on retourne sur saisie des infos sur ed de facture'


Windows("ed fact 2008-12.xls").Activate
Range("B8").Select
Sheets("SAISIE DES INFORMATIONS").Select
Range("C2:E2").Select
End With


'incrementer de 1'

With Sheets("SAISIE DES INFORMATIONS").Select
Range("C2:E2").Select

Sheets("apercu de la facture").Range("f3").Value = Sheets("apercu de la facture").Range("f3").Value + 1

'et remetre à zero la feulle de saisie'

Range("C2").Select
ActiveCell.FormulaR1C1 = ""
Range("C3").Select
ActiveCell.FormulaR1C1 = ""
Range("C4").Select
ActiveCell.FormulaR1C1 = ""
Range("D9").Select
ActiveCell.FormulaR1C1 = ""
Range("E9").Select
ActiveCell.FormulaR1C1 = ""
Range("F9").Select
ActiveCell.FormulaR1C1 = ""
Range("G9").Select
ActiveCell.FormulaR1C1 = ""
Range("H9").Select
ActiveCell.FormulaR1C1 = ""
Range("I9").Select
ActiveCell.FormulaR1C1 = ""
Range("J9").Select
ActiveCell.FormulaR1C1 = ""
Range("H18").Select
ActiveCell.FormulaR1C1 = ""
Range("G18").Select
ActiveCell.FormulaR1C1 = ""
Range("E18").Select
ActiveCell.FormulaR1C1 = ""
Range("D17").Select
ActiveCell.FormulaR1C1 = ""
Range("C2").Select
Range("j16").Select
ActiveCell.FormulaR1C1 = ""
Range("C2:E2").Select

End With





End With
Application.ScreenUpdating = True


End If
End Sub

 

[PascalXLD]

Re : execution de macro sous condition...

RE

Perso j'avai smis un exit sub et un end if que tu n'as pas mis
Pierrot avait mis un else que tu n'as pas mis

 

[ph.vanne]

Re : execution de macro sous condition...

et oui bien sur ...j'ai honte !
merci beaucoup!

 

[ph.vanne]

Re : execution de macro sous condition...

je me suis servi de votre code (qui marche impecablement!)
mais que j'ai voulu ''adapter" !!
j'ai voulu rajouter ce code pour verifier si deux cellules ne sont egales affichage d'une fenetre (ça ça marche !)mais si elle sont egale ça ne marche pas non plus!

If Range("c5") <> ("j13") Then

MsgBox " verifier le nombre total de personne(s)!"
Exit Sub
End If

 

[PascalXLD]

Re : execution de macro sous condition...

re

il faut

If Range("c5") <> Range("j13") Then

 

[ph.vanne]

Re : execution de macro sous condition...

bonsoir,
merci beaucoup!
phil