Hiding a Scroll Bar in VBA

[fordgale]

Hi,
I am trying to hide rows which I have named as a range "NumberNights". I have managed to do this successfully except that within these rows is a scroll bar which is not being hidden with the rows. I have tried naming the scroll bar as another range "avgcostnightscrollbar". I gather from previous posts that I have to hide the entire row that the scroll bar is on (which is fine - if it is one of the rows I am trying to hide anyway). My code is as follows (for which I am getting an error on the 6th line):

Private Sub TotalExpend_Click()
If TotalExpend.Value = True Then
Range("OtherHolidayChoice").Value = 1
Range("TotalExpenditure").EntireRow.Hidden = False
Range("NumberNights").EntireRow.Hidden = True
Range("avgcostnightscrollbar").EntireRow.Hidden = True
End If
End Sub

I have tried putting in Sheet name, Object instead of Range etc, but can't get anything to work. Can anyone help me with the correct syntax?
Thanks.

 

[Jaafar Tribak]

If you are using a scrollbar from the Forms toolbar then set its Visible Property to False via code.
If however you are using an ActiveX Scrollbar then enter edit mode, right click the scrolllbar, choose Format Control, Properties tab and check the Move and Size with cells option.

 

[fordgale]

Hi Jaafar,
Thank you so much for your response - I really appreciate it.

In your response, do you mean the other way around, i.e. change code for the ActiveX scroll bar not the forms scroll bar? If so, I am using the Forms scroll bar, and the option to move and size with cells is not highlighted on my spreadsheet, therefore not available to me. Is there a way to make it available, or should I change it to an ActiveX scroll bar?

I see what you are saying about changing the "visible" property to false on an ActiveX scroll bar and can see how I would do it manually. However, could I be so cheeky as to ask you if the following coding is anything like correct for automating the change before I go and change the scroll bar type and recode? FYI, "avgcostnightscrollbar" is the range name I have given my scroll bar, otherwise it goes by Scroll Bar 67.

If TotalExpend.Value = True Then
Range("avgcostnightscrollbar").Properties.Visible = False

Thank you so much for your help
Lucinda.

 

[Jaafar Tribak]

Hi Jaafar,
Thank you so much for your response - I really appreciate it.

In your response, do you mean the other way around, i.e. change code for the ActiveX scroll bar not the forms scroll bar? If so, I am using the Forms scroll bar, and the option to move and size with cells is not highlighted on my spreadsheet, therefore not available to me. Is there a way to make it available, or should I change it to an ActiveX scroll bar?

I see what you are saying about changing the "visible" property to false on an ActiveX scroll bar and can see how I would do it manually. However, could I be so cheeky as to ask you if the following coding is anything like correct for automating the change before I go and change the scroll bar type and recode? FYI, "avgcostnightscrollbar" is the range name I have given my scroll bar, otherwise it goes by Scroll Bar 67.

If TotalExpend.Value = True Then
Range("avgcostnightscrollbar").Properties.Visible = False

Thank you so much for your help
Lucinda.

Maybe something along these lines :

Private Sub TotalExpend_Click()
    If TotalExpend.Value = True Then
        ActiveSheet.Shapes("Scroll Bar 67").Visible = True
    End If
End Sub