voila mon pb je souhaiter cacher les barre outil à l ouverture et les
remettres a la fermeture
------ dans mon module
Public bars()
Public x
Sub EnlèveCommandeBar()
x = 1
Application.DisplayFormulaBar = False
For Each cmb In CommandBars
If cmb.Visible = True Then
x = x + 1
ReDim Preserve bars(x)
bars(x) = cmb.Name
On Error Resume Next
cmb.Visible = False
End If
Next
End Sub
-----------------
donc a l'ouverture ca marche
---------- mais a la fermeture , j ai un pb
j ai erreur de compilation ou projets introuvable
j ai FOR I : SURLIGNE en jaune
voici le module
---------------
Sub RemetCommandeBar()
Application.DisplayFormulaBar = True
For i = 1 To x 'CEST ICI QUE J AI ERREUR DE COMPILATION
QUE FAIRE
On Error Resume Next
CommandBars(bars(i)).Visible = True
Next
End Sub
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Starwing
Voici un code de John Walkenbach qui enlève toutes les barres du menu Outils et les remets à la fermeture du classeur: Dans le Thisworkbook:
Private Sub Workbook_Open() Call HideAllToolbars End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call RestoreToolbars End Sub
Dans un module Standard:
Sub HideAllToolbars() Dim TB As CommandBar Dim TBNum As Integer Dim TBSheet As Worksheet Set TBSheet = Sheets("TBSheet") Application.ScreenUpdating = False
' Clear the sheet TBSheet.Cells.Clear
' Hide all visible toolbars and store ' their names TBNum = 0 For Each TB In CommandBars If TB.Type = msoBarTypeNormal Then If TB.Visible Then TBNum = TBNum + 1 TB.Visible = False TBSheet.Cells(TBNum, 1) = TB.Name End If End If Next TB Application.ScreenUpdating = True End Sub
Sub RestoreToolbars() Dim TBSheet As Worksheet Dim cell As Range
Set TBSheet = Sheets("TBSheet") Application.ScreenUpdating = False
' Unhide the previously displayed the toolbars On Error Resume Next For Each cell In TBSheet.Range("A:A") _ .SpecialCells(xlCellTypeConstants) CommandBars(cell.Value).Visible = True Next cell Application.ScreenUpdating = True End Sub
Starwing
Voici un code de John Walkenbach qui enlève toutes les barres du menu Outils
et les remets à la fermeture du classeur:
Dans le Thisworkbook:
Private Sub Workbook_Open()
Call HideAllToolbars
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RestoreToolbars
End Sub
Dans un module Standard:
Sub HideAllToolbars()
Dim TB As CommandBar
Dim TBNum As Integer
Dim TBSheet As Worksheet
Set TBSheet = Sheets("TBSheet")
Application.ScreenUpdating = False
' Clear the sheet
TBSheet.Cells.Clear
' Hide all visible toolbars and store
' their names
TBNum = 0
For Each TB In CommandBars
If TB.Type = msoBarTypeNormal Then
If TB.Visible Then
TBNum = TBNum + 1
TB.Visible = False
TBSheet.Cells(TBNum, 1) = TB.Name
End If
End If
Next TB
Application.ScreenUpdating = True
End Sub
Sub RestoreToolbars()
Dim TBSheet As Worksheet
Dim cell As Range
Set TBSheet = Sheets("TBSheet")
Application.ScreenUpdating = False
' Unhide the previously displayed the toolbars
On Error Resume Next
For Each cell In TBSheet.Range("A:A") _
.SpecialCells(xlCellTypeConstants)
CommandBars(cell.Value).Visible = True
Next cell
Application.ScreenUpdating = True
End Sub
Voici un code de John Walkenbach qui enlève toutes les barres du menu Outils et les remets à la fermeture du classeur: Dans le Thisworkbook:
Private Sub Workbook_Open() Call HideAllToolbars End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call RestoreToolbars End Sub
Dans un module Standard:
Sub HideAllToolbars() Dim TB As CommandBar Dim TBNum As Integer Dim TBSheet As Worksheet Set TBSheet = Sheets("TBSheet") Application.ScreenUpdating = False
' Clear the sheet TBSheet.Cells.Clear
' Hide all visible toolbars and store ' their names TBNum = 0 For Each TB In CommandBars If TB.Type = msoBarTypeNormal Then If TB.Visible Then TBNum = TBNum + 1 TB.Visible = False TBSheet.Cells(TBNum, 1) = TB.Name End If End If Next TB Application.ScreenUpdating = True End Sub
Sub RestoreToolbars() Dim TBSheet As Worksheet Dim cell As Range
Set TBSheet = Sheets("TBSheet") Application.ScreenUpdating = False
' Unhide the previously displayed the toolbars On Error Resume Next For Each cell In TBSheet.Range("A:A") _ .SpecialCells(xlCellTypeConstants) CommandBars(cell.Value).Visible = True Next cell Application.ScreenUpdating = True End Sub
