Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm looking for a way to safely combine all this code. The is issue is repeated BeforeClose names that have to be avoided. I was a little confused how to get around this.

//full screen

Private Sub Workbook_Open()
Application.DisplayFullScreen = True
Application.CommandBars("Full Screen").Visible = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
Application.CommandBars("Full Screen").Visible = True
End Sub

//formula bar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFormulaBar = True
End Sub

Private Sub Workbook_Open()
Application.DisplayFormulaBar = False
End Sub

//headings

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayHeadings = True
End Sub

Private Sub Workbook_Open()
Application.DisplayHeadings = False
End Sub

//drag and drop

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    chgflag = "Y"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   If chgflag <> "Y" Then
     MsgBox ("You are Closing this before Generating Your Target Docs")
   End If
   Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

Many Thanks for your ideas.

share|improve this question
    
Is chgflag a public variable you have declared somewhere else? –  Excellll Nov 21 '12 at 15:10

1 Answer 1

The Workbook_Open or Workbook_BeforeClose(Cancel As Boolean) is the trigger to execute the commands you desire at those given times. So, put all the commands between the Private Sub and End Sub statements. For instance;

Private Sub Workbook_Open()
  Application.DisplayFullScreen = True
  Application.CommandBars("Full Screen").Visible = False
  Application.DisplayFormulaBar = False
  Application.DisplayHeadings = False
  Application.CellDragAndDrop = False
End Sub

Do the same for the BeforeClose event.

share|improve this answer
    
Thanks will give this a go –  Koto Nov 21 '12 at 16:36
    
Private Sub Workbook_AfterSave(ByVal Success As Boolean) chgflag = "Y" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If chgflag <> "Y" Then MsgBox ("You are Closing this before Generating Your Target Docs") End If Application.CellDragAndDrop = True End Sub Private Sub Workbook_Open() Application.DisplayFullScreen = True Application.CommandBars("Full Screen").Visible = False Application.DisplayHeadings = False Application.CellDragAndDrop = False End Sub –  Koto Nov 21 '12 at 16:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.