VBA help needed ...

Jed Shields

Jed Shields

Jed Shields

Associate
Joined
23 Apr 2007
Posts
1,782
Location
Cardiff-ish, Wales
Hi guys and gals,

Any VBA gurus out there?

I'm trying to get some code working that will go through each sheet and turn off the scrollbars etc. It's intended to be part of some code that will get a dashboard ready for a slideshow and I want to maximise the real estate of the charts on the screen. Anyway, I'm getting a "Object doesn't support this property or method" message. What have I done wrong?

Thanks in advance,

Jed.



Code: 
Sub ShowMenus()

        Sheets("Leads Today").Select
        ActiveWindow.DisplayScrollBars = True
        ActiveWindow.DisplayFormulaBar = True
        ActiveWindow.DisplayStatusBar = True
        ActiveWindow.DisplayHeadings = True
        
        Sheets("Order Intake Today").Select
        ActiveWindow.DisplayScrollBars = True
        ActiveWindow.DisplayFormulaBar = True
        ActiveWindow.DisplayStatusBar = True
        ActiveWindow.DisplayHeadings = True
        
        Sheets("Leads Month").Select
        ActiveWindow.DisplayScrollBars = True
        ActiveWindow.DisplayFormulaBar = True
        ActiveWindow.DisplayStatusBar = True
        ActiveWindow.DisplayHeadings = True
        
        Sheets("Order Intake Month").Select
        ActiveWindow.DisplayScrollBars = True
        ActiveWindow.DisplayFormulaBar = True
        ActiveWindow.DisplayStatusBar = True
        ActiveWindow.DisplayHeadings = True
             
        Sheets("Leads Today").Select
        
        
End Sub
 
Sorted! I recorded a macro and it came up with a different structure. I then added the Status Bar line, that I couldn't see within the manual menu option and repeated for all the sheets. I'm sure there's a prettier way of doing it, but this works and will do for now :D

Code: 
Sub ShowMenus()

   Sheets("Leads Today").Select
   Application.DisplayFormulaBar = True
   Application.DisplayStatusBar = True
    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayWorkbookTabs = True
    End With
    ActiveWindow.DisplayHeadings = True
    
   Sheets("Order Intake Today").Select
   Application.DisplayFormulaBar = True
   Application.DisplayStatusBar = True
    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayWorkbookTabs = True
    End With
    ActiveWindow.DisplayHeadings = True
    
   Sheets("Leads Month").Select
   Application.DisplayFormulaBar = True
   Application.DisplayStatusBar = True
    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayWorkbookTabs = True
    End With
    ActiveWindow.DisplayHeadings = True
    
   Sheets("Order Intake Month").Select
   Application.DisplayFormulaBar = True
   Application.DisplayStatusBar = True
    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayWorkbookTabs = True
    End With
    ActiveWindow.DisplayHeadings = True
    
    Sheets("Leads Today").Select
    
End Sub
 
Not sure why you'd have to repeat so much. Wouldn't this do the same?

Code: 
Sub ShowMenus()
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayWorkbookTabs = True
        .DisplayHeadings = True
    End With
  End Sub
 
Last edited:
For some reason the DisplayHeadings part wouldn't work when it was within the With ActiveWindow bit.

Ideally I'd just have that one piece of code wrapped within a loop function, but that's beyond my skill ... at the moment ;) Feel free to help me out though if you can :D

Huh... just noticed you're in Maidenhead, I'm in the Travelodge, working at Three during the week. Small world :)
 
Dim ws as worksheet
Dim wb as workbook

Set wb = thisworkbook

For each ws in wb.worksheets

''put code doing stuff in in here

Next ws

set wb = nothing

That will loop through every worksheet in your workbook

but as you have found out scroll bars, menu bars, formula bars etc are at the application level so you can just set them in one go:)
 
Jed Shields said:
For some reason the DisplayHeadings part wouldn't work when it was within the With ActiveWindow bit.

Ideally I'd just have that one piece of code wrapped within a loop function, but that's beyond my skill ... at the moment ;) Feel free to help me out though if you can :D

Huh... just noticed you're in Maidenhead, I'm in the Travelodge, working at Three during the week. Small world :)
Click to expand...

lol I work opposite three in Sita. i was underneath the travelodge in the sainsburys earlier to get my lunch.
 
You must log in or register to reply here.
Back
Top Bottom