Excel VBA Hide ScrollBars

[TheFIDDLER]

Can anyone explain to me why the following code works sometime and not at other times.

If ActiveWindow.DisplayHorizontalScrollBar = False Then ActiveWindow.DisplayHorizontalScrollBar = True

If ActiveWindow.DisplayVerticalScrollBar = False Then ActiveWindow.DisplayVerticalScrollBar = True

I am trying to have the scroll bars activate when I activate a sheet. I use similar code to deactivate them once I leave the sheet.

I have tried this in both the Sheet.activate module and then in a separate module in a macro that takes me from to the sheet.

It seems that often, the very first time I run it, I get an error message saying I am unable to set the property. ALso happens when I switch from a workbook back to this one, (usually one sheet before this sheet, and then press my command button to take me to the sheet). Funny thing is that if I press the button again, (right after pressing ok to clear the error message), it works.

I can even include an On Error Resume Next statement, and still get my error message.

Any suggestions?

[TheFIDDLER]

Did some trial on this and realized the cause.
If my focus is not a worksheet, that's when I get the error.

Usually happens if my focus is on a combo box prior to changing to the new sheet.

But I still want to hide the scrollbars.

Can I access this without going through the activewindow object?

[Shattered]

Originally posted by TheFIDDLER

I am trying to have the scroll bars activate when I activate a sheet. I use similar code to deactivate them once I leave the sheet.

Just an observation.. When excel activates and deactivates the scroll bar it does it for the actual workbook, not just that individual worksheet.

You can remove the scrollbars whenever your workbook is loaded and remove them when its exited but not on each sheet within the book..

[TheFIDDLER]

The point is to get past the global setting and simulate worksheet level controls.

I want to tie this to a worksheet activate and a worksheet deactivate subroutine, which in essence turns on or off the workbook global setting.

Problem arises when my focus goes from a non sheet object, such as a combo box to the new sheet. Seems to run the activewindow.displayhorizontalscrollbar = true while still on the combo box and not on the new sheet. Hence I get an error message as the combobox (and not the new sheet) cannot support the displayhorizontalscrollbar property.

And were it not for the combobox, this whole thing would work fine.

[Shattered]

hmm, would be fascinated to see how this could work. As it stands excel uses the active window to affect the scroll bars, not the workbook object or worksheet object, therefore seperating it out seems to be a fruitless task. One possible way around it is to use vb to launch an instance of the object and utilise the api calls to remove the scroll bars.

Not sure why you would want to but will be interested to see if anyone does manage it..

[WorkHorse]

I can't duplicate the error?

In the Sheet1 object module I have:

Option Explicit
 
Private Sub Worksheet_Activate()
    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
    End With
End Sub
 
Private Sub Worksheet_Deactivate()
    With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
End Sub

Works just fine. I can't see how the ActiveWindow properties would not apply to the active workbook window in the Activate event? You could always resort to something like

Workbooks(1).Windows(1).DisplayHorizontalScrollBar

[TheFIDDLER]

Found a partial solution.

I can do this when using a command button to switch between sheets. All I need to do is add a "Range("A1").Select" line to my code prior to the scroll bar display command. This quick reference to part of the sheet sets the focus to the sheet and then I can access the sheet's properties.

However, I still can't get something to work for the sheet.activate and sheet.deactivate subroutines. Also still cannot get a working model if I go direct from a combo or list box to a sheet tab.

Will have to try adding a Range("A1").select line to the combobox change subroutine. Maybe forcing the focus back after each combo box change. Only this messes up the ability to tab between different combo and list boxes that may be visible on the screen at the same time.

As to the why of what I am doing - I have an application done in excel, but want to hide some of the excel look. For my main menu screens, all my intro fits on one screen, and it looks sharper if the scroll bars are gone. But for my data screen, the user needs to be able to move around in the data so hence a need for scroll bars.

[TheFIDDLER]

To duplicate what I have:

Set up a combobox on your sheet.
Values are irrelevant, fill it with whatever.

Set up the following code in the worksheet.activate routine for sheet(2) the sheet you will be moving to.

If ActiveWindow.DisplayHorizontalScrollBar = False Then ActiveWindow.DisplayHorizontalScrollBar = True
If ActiveWindow.DisplayVerticalScrollBar = False Then ActiveWindow.DisplayVerticalScrollBar = True

Click on the combo box so that the focus changes to the combo box.

Try - Click on a sheet tab. - error message to follow.

Or - Code a command button with a one liner to move to another sheet. Providing the focus is not returned back to my sheet, pressing the command button while the combobox is activated gives me an error message.

[WorkHorse]

Nope. No problems at all. Works like a charm. What version of Excel are you using?

[TheFIDDLER]

Excel 97 SR-1


Also - not sure if relevant -
but the starting sheet has scrollbars disabled.