Strange. Have you tried putting a doevents or a 5-second timer in after that
statement, to see if maybe Excel just hasn't finished processing the request?
"gab1972" wrote:
> On Jul 20, 2:23 pm, ker_01 <ke...@discussions.microsoft.com> wrote:
> > This works as expected on my installation of XL2003, hiding then showing the
> > formulabar.
> >
> > Sub test1()
> > Application.DisplayFormulaBar = False
> > End Sub
> >
> > Sub test2()
> > Application.DisplayFormulaBar = True
> > End Sub
> >
> > I wasn't able to see anything wrong in your code, but without knowing
> > everything else you are doing with the workbook, I'm not sure what to suggest
> > you start looking.
> >
> >
> >
> > "gab1972" wrote:
> > > I've been using this coding for a while. I like it because it limits
> > > my users to tinkering with the cells when I don't want them to, but it
> > > has never given me back my Formula Bar after I close the sheet. I've
> > > tried various different edits of the coding and I can't get it to
> > > bring the formula bar back. It hides it, but when I close, it doesn't
> > > come back. Any clues?
> >
> > > Option Base 1
> > > Dim MoveAfterReturn As Boolean
> > > Dim MoveAfterReturnDirection As XlDirection
> > > Dim CBvisible() As Boolean
> >
> > > Private Sub Workbook_Open()
> >
> > > Dim i As Integer
> >
> > > 'Hide all commandbars, including formula bar, but not Worksheet Menu
> > > Bar
> >
> > > With Application
> >
> > > ReDim CBvisible(.CommandBars.Count)
> >
> > > For i = 1 To .CommandBars.Count
> > > CBvisible(i) = .CommandBars(i).Visible 'save original
> > > visibility state
> > > If .CommandBars(i).Name <> "Worksheet Menu Bar" Then
> > > If .CommandBars(i).Visible Then .CommandBars(i).Visible =
> > > False
> > > End If
> > > Next i
> >
> > > .DisplayFormulaBar = False
> >
> > > With .CommandBars("Worksheet Menu Bar")
> > > For i = 1 To .Controls.Count
> > > Select Case .Controls(i).Caption
> > > Case "&File", "&Help"
> > > Case Else
> > > .Controls(i).Visible = False
> > > End Select
> > > Next i
> > > End With
> >
> > > 'save current settings so they can be restored later,
> > > 'then set enter key to move down
> > > MoveAfterReturn = Application.MoveAfterReturn
> > > MoveAfterReturnDirection = Application.MoveAfterReturnDirection
> > > .MoveAfterReturn = True
> > > .MoveAfterReturnDirection = xlToRight
> > > End With
> >
> > > 'Turn off row and column headings
> > > ActiveWindow.DisplayHeadings = False
> >
> > > Sheets("Lists").Range("I2").Value = ""
> > > Sheets("Home").Select
> > > PermitTrackerSplash.Show
> >
> > > End Sub
> >
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > Dim i As Integer
> >
> > > 'Unhide all commandbars, including formula bar, but not Worksheet
> > > Menu Bar
> >
> > > With Application
> >
> > > For i = 1 To .CommandBars.Count
> > > If .CommandBars(i).Visible <> CBvisible(i) Then
> > > .CommandBars(i).Visible = CBvisible(i)
> > > End If
> > > Next i
> >
> > > .DisplayFormulaBar = True
> >
> > > With .CommandBars("Worksheet Menu Bar")
> > > For i = 1 To .Controls.Count
> > > .Controls(i).Visible = True
> > > Next i
> > > End With
> >
> > > 'restore move-after-enter original settings
> > > .MoveAfterReturn = MoveAfterReturn
> > > .MoveAfterReturnDirection = MoveAfterReturnDirection
> > > End With
> >
> > > 'Turn on row and column headings
> > > ActiveWindow.DisplayHeadings = True
> >
> > > End Sub
>
> I discovered if I use File, Exit...everything works as it should.
> However, if I use my Exit macro button, it does everything button show
> the formula bar.
>
> Sub ExitProg()
> ActiveWorkbook.Close SaveChanges:=True
> End Sub
>
|
Similar Threads
