add scrollbar to chart using vba problem

[johncblacker]

I am adding a scrollbar to a chart using vba. I'v been successful getting the scrollbar added; however when I view the chart, the scrollbar has the "sizing handles" around it, and the "bar" (object that moves back and forth) is missing until I resize the scrollbar manually. I've tried changing the initial size of the scrollbar to no avail. I've also set the visible property to True and the enabled property to True, also to no avail. Does anyone have a clue as to what I need to do? I'd like for the scrollbar to be fully functional after the vba code has run.
Thanks in advance for any help!

[LogheadLeech]

Hello Johncblacker,

Could you please post the vba code that you are using to add a scrollbar to your charts to further aid in answering your question.

Thanks,
Paul

[johncblacker]

OK, here's my code...I hope I'm following the rules correctly for including code...if not, please correct my error!
[code]

ActiveSheet.ScrollBars.Add(624763.5, 4271616, 5095730.25, 189166.5).Select
With Selection
.Value = 60
.Min = SMin
.Max = SMax
.SmallChange = 1
.LargeChange = 1
.LinkedCell = "Spikes!$O$5"
.Left = 0#
.Width = 680.32
.Top = 0#
.Height = 15
.Visible = True
.Enabled = True
.Display3DShading = True
End With
ActiveChart.Shapes("Scroll Bar 1").Select
Selection.OnAction = "ScrollBarChange"

[end code]

[LogheadLeech]

Hi John,

You need to edit your post and change [end code] to [/code]

It appears that you're adding your scroll way down in the bottom right of the sheet and then trying to move it back up to the top left, is that correct?

.left = 0# 
.Top = 0# 

I think the following code should do what you are looking for

Sheet1.ScrollBars.Add(624763.5, 4271616, 5095730.25, 189166.5).Select 
 
With Selection 
    .Value = 60 
    .Min = SMin 
    .Max = SMax 
    .SmallChange = 1 
    .LargeChange = 1 
    .LinkedCell = "Sheet1!$O$5" 
    .Display3DShading = True 
     
    With .ShapeRange 
        .Left = 0 
        .Width = 680.32 
        .Top = 0 
        .Height = 15 
    End With 
     
    .OnAction = "ScrollBarChange" 
End With 

Assuming that SMax and SMin are valid integers. If you get an error, first thing I would do is change SMin to 0 and SMax to 100 and see if this helps. Then go from there.

Any problems, let me know

Thanks,
Paul

[johncblacker]

Looks like a winner thanks...btw, how do i go back and edit a previous post to change the [end code] to [/code] ?