Excel Scroll bar not initiating change event

Simon Raine
Simon Raine used Ask the Experts™
on
I have set up a change event macro if cell a1 is changed, this then runs other macros to set displayed values in a chart.

At present I have linked a drop down list to update value A1. This triggers the change event and runs the macros.

However when I link cell A1 to a scroll bar, the cell value seems to change but the macros do not run.

How can I make the scroll bar change the value in A1 and trigger the change event macro?



Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("a1")) Is Nothing Then graphselector2
End Sub

Sub graphselector2()
Dim choice
choice = Range("a1")

If choice = 1 Then
Run "aGraph1"

ElseIf choice = 2 Then
Run "aGraph2"

ElseIf choice = 3 Then
Run "aGraph3"

ElseIf choice = 4 Then
Run "aGraph4"

ElseIf choice = 5 Then
Run "aGraph5"

ElseIf choice = 6 Then
Run "aGraph6"

ElseIf choice = 7 Then
Run "aGraph7"

ElseIf choice = 8 Then
Run "aGraph8"

ElseIf choice = 9 Then
Run "aGraph9"

ElseIf choice = 10 Then
Run "aGraph10"

ElseIf choice = 11 Then
Run "aGraph11"

ElseIf choice = 12 Then
Run "aGraph12"




End If
End Sub
Capture.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
Simon

Why not assign the macro graphselector2 to the scrollbar?

Then it should get triggered whenever a change is made to the scrollbar.

PS You could make that code a little shorter.
Sub graphselector2()
Dim choice As Long

    choice = Range("a1")

    Select Case choice
        Case 1 To 12
            Run "aGraph" & choice
        Case Else
            MsgBox "You made a bad choice!", vbInformation, "Bad choice"
    End Select
    
End Sub

Open in new window

Author

Commented:
excellent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial