Enregistrement en VBA

Daniel

14/11/2005 à 13:53

Bonjour.
Mets :
Application.DisplayAlerts = False
avant l'enregistrement.
Cordialement.
Daniel
"Fuentes Mickael" a écrit dans le message de news:

Bonjour à tous,

je viens solicitrer votre aide à nouveau pour un petit probleme
d'enregistrement sous VBA.
En effet j'ouvre un nouveau document à l'aide d'un model (mon model
s'appel
test.xlt et donc mon nouveau document s'appel test1).Dans mon nouveau
document j'ai une touche de mise à jour de mon document modele. Lorsque je
fait cette mise à jour le systeme me demande l'autorisation d'ecraser mon
model et je voudrais que cela se fasse automatiquement sans intervention
de
ma part.

Mon code actuel est :

ChDir "C:Program FilesMicrosoft OfficeModèles"
myFileName = "test.xlt"
enreg = Application.GetSaveAsFilename(myFileName)

Application.EnableEvents = False
ThisWorkbook.SaveAs enreg
Application.EnableEvents = True

ActiveWorkbook.Close

Merci d'avance
Micky

gach

14/11/2005 à 14:14

Bonjour,

Petite question de debutant :
Je souhaite forcer la taille des mes cellules.
elle doivent faire 6 cm par exemple, comment modifier l'unité de mesure
de exel pour avoir le cm ?

Merci

michdenis

14/11/2005 à 15:35

Bonjour Gach,

Voici une macro publiée par ChrisV émanant du site de Microsoft

Tu lances cette macro après fait la section des colonnes concernées :

'================================= Sub ColumnWidthInCentimeters()

Dim cm As Single, points As Integer, savewidth As Integer
Dim lowerwidth As Integer, upwidth As Integer, curwidth As Integer
Dim Count As Integer

' Turn screen updating off.
Application.ScreenUpdating = False
' Ask for the width in inches wanted.
cm = Application.InputBox("Enter Column Width in Centimeters", _
"Column Width (cm)", Type:=1)
' If cancel button for the input box was pressed, exit procedure.

If cm = False Then Exit Sub
' Convert the inches entered to points.
points = Application.CentimetersToPoints(cm)
' Save the current column width setting.
savewidth = ActiveCell.ColumnWidth
' Set the column width to the maximum allowed.
ActiveCell.ColumnWidth = 255
' If the points desired is greater than the points for 255
' characters...
If points > ActiveCell.Width Then
' Display a message box because the size specified is too
' large and give the maximum allowed value.
MsgBox "Width of " & cm & " is too large." & Chr(10) & _
"The maximum value is " & _
Format(ActiveCell.Width / 28.3464566929134, _
"0.00"), vbOKOnly + vbExclamation, "Width Error"
' Reset the column width back to the original.
ActiveCell.ColumnWidth = savewidth
' Exit the Sub.
Exit Sub
End If
' Set the lowerwidth and upper width variables.
lowerwidth = 0
upwidth = 255
' Set the column width to the middle of the allowed character
' range.
ActiveCell.ColumnWidth = 127.5
curwidth = ActiveCell.ColumnWidth
' Set the count to 0 so if it can't find an exact match it won't
' go on indefinitely.
Count = 0
' Loop as long as the cell width in is different from width
' wanted and the count (iterations) of the loop is less than 20.
While (ActiveCell.Width <> points) And (Count < 20)
' If active cell width is less than desired cell width.
If ActiveCell.Width < points Then
' Reset lower width to current width.
lowerwidth = curwidth
' set current column width to the midpoint of curwidth
' and upwidth.
Selection.ColumnWidth = (curwidth + upwidth) / 2
' If active cell width is greater than desired cell width.
Else
' Set upwidth to the curwidth.
upwidth = curwidth
' Set column width to the mid point of curwidth and lower

' width.
Selection.ColumnWidth = (curwidth + lowerwidth) / 2
End If
' Set curwidth to the width of the column now.
curwidth = ActiveCell.ColumnWidth
' Increment the count counter.
Count = Count + 1
Wend
End Sub
'=================================

Salutations!

"gach" a écrit dans le message de news:
Bonjour,

Petite question de debutant :
Je souhaite forcer la taille des mes cellules.
elle doivent faire 6 cm par exemple, comment modifier l'unité de mesure
de exel pour avoir le cm ?

Merci

Bonjour Gach,

Voici une macro publiée par ChrisV émanant du site de Microsoft

Tu lances cette macro après fait la section des colonnes concernées :

'================================= Sub ColumnWidthInCentimeters()

Dim cm As Single, points As Integer, savewidth As Integer
Dim lowerwidth As Integer, upwidth As Integer, curwidth As Integer
Dim Count As Integer

' Turn screen updating off.
Application.ScreenUpdating = False
' Ask for the width in inches wanted.
cm = Application.InputBox("Enter Column Width in Centimeters", _
"Column Width (cm)", Type:=1)
' If cancel button for the input box was pressed, exit procedure.

If cm = False Then Exit Sub
' Convert the inches entered to points.
points = Application.CentimetersToPoints(cm)
' Save the current column width setting.
savewidth = ActiveCell.ColumnWidth
' Set the column width to the maximum allowed.
ActiveCell.ColumnWidth = 255
' If the points desired is greater than the points for 255
' characters...
If points > ActiveCell.Width Then
' Display a message box because the size specified is too
' large and give the maximum allowed value.
MsgBox "Width of " & cm & " is too large." & Chr(10) & _
"The maximum value is " & _
Format(ActiveCell.Width / 28.3464566929134, _
"0.00"), vbOKOnly + vbExclamation, "Width Error"
' Reset the column width back to the original.
ActiveCell.ColumnWidth = savewidth
' Exit the Sub.
Exit Sub
End If
' Set the lowerwidth and upper width variables.
lowerwidth = 0
upwidth = 255
' Set the column width to the middle of the allowed character
' range.
ActiveCell.ColumnWidth = 127.5
curwidth = ActiveCell.ColumnWidth
' Set the count to 0 so if it can't find an exact match it won't
' go on indefinitely.
Count = 0
' Loop as long as the cell width in is different from width
' wanted and the count (iterations) of the loop is less than 20.
While (ActiveCell.Width <> points) And (Count < 20)
' If active cell width is less than desired cell width.
If ActiveCell.Width < points Then
' Reset lower width to current width.
lowerwidth = curwidth
' set current column width to the midpoint of curwidth
' and upwidth.
Selection.ColumnWidth = (curwidth + upwidth) / 2
' If active cell width is greater than desired cell width.
Else
' Set upwidth to the curwidth.
upwidth = curwidth
' Set column width to the mid point of curwidth and lower

' width.
Selection.ColumnWidth = (curwidth + lowerwidth) / 2
End If
' Set curwidth to the width of the column now.
curwidth = ActiveCell.ColumnWidth
' Increment the count counter.
Count = Count + 1
Wend
End Sub
'=================================

Salutations!

"gach" <gach.news@laposte.net> a écrit dans le message de news: OMx9j1R6FHA.3976@TK2MSFTNGP15.phx.gbl...
Bonjour,

Petite question de debutant :
Je souhaite forcer la taille des mes cellules.
elle doivent faire 6 cm par exemple, comment modifier l'unité de mesure
de exel pour avoir le cm ?

Merci

Vous avez filtré cet utilisateur ! Consultez son message

Bonjour Gach,

Voici une macro publiée par ChrisV émanant du site de Microsoft

Tu lances cette macro après fait la section des colonnes concernées :

'================================= Sub ColumnWidthInCentimeters()

Dim cm As Single, points As Integer, savewidth As Integer
Dim lowerwidth As Integer, upwidth As Integer, curwidth As Integer
Dim Count As Integer

' Turn screen updating off.
Application.ScreenUpdating = False
' Ask for the width in inches wanted.
cm = Application.InputBox("Enter Column Width in Centimeters", _
"Column Width (cm)", Type:=1)
' If cancel button for the input box was pressed, exit procedure.

If cm = False Then Exit Sub
' Convert the inches entered to points.
points = Application.CentimetersToPoints(cm)
' Save the current column width setting.
savewidth = ActiveCell.ColumnWidth
' Set the column width to the maximum allowed.
ActiveCell.ColumnWidth = 255
' If the points desired is greater than the points for 255
' characters...
If points > ActiveCell.Width Then
' Display a message box because the size specified is too
' large and give the maximum allowed value.
MsgBox "Width of " & cm & " is too large." & Chr(10) & _
"The maximum value is " & _
Format(ActiveCell.Width / 28.3464566929134, _
"0.00"), vbOKOnly + vbExclamation, "Width Error"
' Reset the column width back to the original.
ActiveCell.ColumnWidth = savewidth
' Exit the Sub.
Exit Sub
End If
' Set the lowerwidth and upper width variables.
lowerwidth = 0
upwidth = 255
' Set the column width to the middle of the allowed character
' range.
ActiveCell.ColumnWidth = 127.5
curwidth = ActiveCell.ColumnWidth
' Set the count to 0 so if it can't find an exact match it won't
' go on indefinitely.
Count = 0
' Loop as long as the cell width in is different from width
' wanted and the count (iterations) of the loop is less than 20.
While (ActiveCell.Width <> points) And (Count < 20)
' If active cell width is less than desired cell width.
If ActiveCell.Width < points Then
' Reset lower width to current width.
lowerwidth = curwidth
' set current column width to the midpoint of curwidth
' and upwidth.
Selection.ColumnWidth = (curwidth + upwidth) / 2
' If active cell width is greater than desired cell width.
Else
' Set upwidth to the curwidth.
upwidth = curwidth
' Set column width to the mid point of curwidth and lower

' width.
Selection.ColumnWidth = (curwidth + lowerwidth) / 2
End If
' Set curwidth to the width of the column now.
curwidth = ActiveCell.ColumnWidth
' Increment the count counter.
Count = Count + 1
Wend
End Sub
'=================================

Salutations!

"gach" a écrit dans le message de news:
Bonjour,

Petite question de debutant :
Je souhaite forcer la taille des mes cellules.
elle doivent faire 6 cm par exemple, comment modifier l'unité de mesure
de exel pour avoir le cm ?

Merci

Modeste

14/11/2005 à 22:37

Bonsour®
gach wrote:

Je souhaite forcer la taille des mes cellules.
elle doivent faire 6 cm par exemple, comment modifier l'unité de mesure
de exel pour avoir le cm ?

Fournie ici recement 5/11/2005 ;o)))

Selectionner une zone puis activer la macro

Sub LargeurEn_mm()
Dim WPChar As Double
Dim DInch As Double
Dim Temp As String

Temp = InputBox("largeur en mm?")
DInch = Val(Temp) / 25.4
If DInch > 0 And DInch < 2.5 Then
i = 0
For Each col In ActiveWindow.RangeSelection.Columns
i = i + 1
If i = 1 Then
col.EntireColumn.AutoFit
WPChar = col.Width / col.ColumnWidth
End If
col.ColumnWidth = ((DInch * 72) / WPChar)
Next col
'-----pour les lignes
'----For Each lig In ActiveWindow.RangeSelection.Rows
'----lig.RowHeight = (DInch * 72)
'----Next lig
End If
End Sub

--
n'oubliez pas les FAQ :
http://www.excelabo.net http://dj.joss.free.fr/faq.htm
http://www.faqoe.com http://faqword.free.fr
--
Feed Back
http://viadresse.com/?94912042

Enregistrement en VBA

4 réponses

Veuillez sélectionner un problème