MsgBox

Bonjour Seve95

Si l'action doit etre validée par un bouton ok , tu peux essayer d'utiliser la methode inputbox à la place d'un msgbox
Comme tu n'as pas donné d'infos sur ton formatage de tableau , je ne sais pas trop ce que ça va donner dans ta procédure

Sub formater()
Dim cible As Range
Set cible = Application.InputBox("Sélectionnez le tableau à formatter", Type:=8)
cible.Interior.ColorIndex = 17
End Sub

quand la boite de dialogue s'affiche , tu sélectionnes les cellules cible : leur reference s'inscrit dans le champs de saisie

et tu cliques sur ok pour poursuivre la procedure


Bonne soirée
Michel

Bonjour Michel et merci beaucoup, en fait voici ma macro, attention elle ne peut fonctionner que si tu as 5 colonnes avec du texte,

Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 29/07/2003 par seve
'
'

With Selection.Interior
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
Selection.Rows(1).Select
With Selection.Rows
.RowHeight = 12
End With
With Selection.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
.Bold = True
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
Selection.Rows(2).Select
With Selection.Rows
.RowHeight = 12
End With
With Selection.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
.Bold = True
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
Selection.Rows(2).Select
With Selection.Rows
.RowHeight = 12
End With
With Selection.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
Selection.Rows(2).Select
With Selection.Rows
.RowHeight = 27.75
End With
With Selection.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
.Bold = True
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
Selection.Rows(2).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Rows
.RowHeight = 12
End With
Selection.Resize(Selection.Rows.Count - 1).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(A1);2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 53
With Selection.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
Selection.CurrentRegion.Select
Selection.Rows(5).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Font
.Name = "LucidaSans"
.Size = 7
End With
Selection.Columns(1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Columns
.ColumnWidth = 4
End With
Selection.Columns(2).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Columns
.ColumnWidth = 20
End With
Selection.Columns(2).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Columns
.ColumnWidth = 10
End With
Selection.Columns(2).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Columns
.ColumnWidth = 5.38
End With
Selection.Columns(2).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Columns
.ColumnWidth = 6
End With
Selection.CurrentRegion.Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.ColorIndex = 15
End With
With Selection.Font
.Bold = True
End With
Selection.CurrentRegion.Select
Selection.Find(What:="SG", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Selection.Find("SG").Rows.Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
.Bold = False
End With
Selection.End(xlDown).Select
Selection.Rows(2).Select
Dim maSource As String
maSource = InputBox("Entrer le nom de la source")
End With
End Sub


Ta sélection fonctionne mais avec l'instruction "cible.Interior.ColorIndex = 17" je ne sais pas comment faire pour la redéfinir vers ma macro...

rebonjour,

ou alors il suffit peut être de définir la couleur en tant que transparente pour que mes couleurs restent les seules appliquées après l'exécution de ton instruction...
qu'en penses-tu ?

en effet, en indiquant ... colorindex = 0, ça marche !!

a plus tard, je vais avoir d'autres interrogations je pense...

Merci !

bonsoir Seve95

la ligne cible.Interior.ColorIndex = 17 était juste un exemple .
si cela ne correspond pas à ton projet tu supprimes tout simplement cette ligne

en voyant l'ensemble de ta procedure , je ne vois pas trop ce que t'apporte l'inputbox
si cela fonctionne je ne l'aurai pas fait exprès ...;o)...

Bonne soirée
Michel

Merci beaucoup !!

mais en supprimant la fameuse ligne, j'avais un bug...

Encore merci

Séverine

Rebonsoir,

en fait c'est pas ça du tout, si je veux que la inputbox fonctionne je suis obligée de sélectionner d'abord le tableau, ce que je veux c'est qu'après la inputbox on puisse selectionner normalement le tableau, sans les pointillés, juste avec une selection noire sinon ma macro ne fonctionne pas...

Si quelqu'un pouvait m'aider là-dessus ce serait vraiment sympa

d'avance, merci, je n'ai rien trouvé dans mon bouquin...

rebonsoir Seve

je suis désolé mais je ne vois toujours pas ce que tu veux faire et à quoi peut servir le msgbox ( ou l'inputbox ).... essaies tout de meme cette version de la procedure... avec un peu de chance ......le formatage de la plage fonctionne chez moi...
quand l'input box s'affiche , tu selectionne la zone et ensuite tu cliques sur ok ( nota : les lettres "SG" doivent apparaitre dans une des cellules selectionnées pour que cela fonctionne )


Sub Macro1()

Dim source As Range
Set source = Application.InputBox("Sélectionnez le tableau à formatter", Type:=8)

application.screenupdating=false

With source.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
With source.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
source.Rows(1).Select
With source.Rows
.RowHeight = 12
End With
With source.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
.Bold = True
End With
With source
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
source.Rows(2).Select
With source.Rows
.RowHeight = 12
End With
With source.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
.Bold = True
End With
With source
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
source.Rows(2).Select
With source.Rows
.RowHeight = 12
End With
With source.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
End With
With source
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With source.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
source.Rows(2).Select
With source.Rows
.RowHeight = 27.75
End With
With source.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
.Bold = True
End With
With source
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With source.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
source.Rows(2).Select
Range(source, source.End(xlDown)).Select
With source.Rows
.RowHeight = 12
End With
source.Resize(source.Rows.Count - 1).Select
source.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(A1);2)=1"
source.FormatConditions(1).Interior.ColorIndex = 53
With source.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With source.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 32
End With
source.CurrentRegion.Select
source.Rows(5).Select
Range(source, source.End(xlDown)).Select
With source.Font
.Name = "LucidaSans"
.Size = 7
End With
source.Columns(1).Select
With source
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With source.Columns
.ColumnWidth = 4
End With
source.Columns(2).Select
With source
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With source.Columns
.ColumnWidth = 20
End With
source.Columns(2).Select
With source
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With source.Columns
.ColumnWidth = 10
End With
source.Columns(2).Select
With source
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With source.Columns
.ColumnWidth = 5.38
End With
source.Columns(2).Select
With source
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With source.Columns
.ColumnWidth = 6
End With
source.CurrentRegion.Select
source.End(xlDown).Select
Range(source, source.End(xlToRight)).Select
With source.Interior
.ColorIndex = 15
End With
With source.Font
.Bold = True
End With
source.CurrentRegion.Select
source.Find(What:="SG", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
source.Find("SG").Rows.Select
Range(source, source.End(xlToRight)).Select
With source.Font
.Name = "LucidaSans"
.Size = 7
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
.Bold = False
End With
source.End(xlDown).Select
source.Rows(2).Select

application.screenupdating=true


End Sub



bonne soirée
Michel

bonsoir Seve

je viens de retester ce que je t'ai écrit plus haut ...cela ne fonctionne pas ...;o( ...

je suis désolé

bon courage
et bonne soirée

Michel

Bonour,

Je viens de tester, ça ne marche pas non plus, je continue de chercher..
En tout cas, merci beaucoup,

A bientot peut être

Bye