VBA - trigger an event witout code in activesheet

[Rasm]

I want to add a scrollbar to a worksheet using code - lets call it Scrollbar1

However I want the event Scrollbar1_click to reside in my module or class module - not in the sheet code.

Any code example would be appreciated - does not have to be a scrollbar - it can be any of the commonly used OCXs.

What I am trying to do is place a scrolbar left of the activechart and below the activechart - the idea is that I use the scrolbars to change my minimum and maximum scales for the chart. I did attempt this using a userform (with only a scrollbar on the userform) - but the minimum Width of a userform is 99 (grrrrr MS) - also a userform has a header and blue ribbon that I dont know how to remove.

 

[AlphaFrog]

Could you use Form type controls from the Form toolbar instead. You can assign macros from other modules to those controls without the need for Sheet event macro code.

    [COLOR="Green"]' Add "Form" type ScrollBar control to cells C4:E4 and assign a macro from Module1 called scrZoomX[/COLOR]
    With Range("C4")
    ActiveSheet.ScrollBars.Add(.Left, .Top, .Resize(, 3).Width, .Height).Select
    End With
    Selection.OnAction = "Module1.scrZoomX"

 

[Rasm]

ahhhh - this looks like what I need - I will try that - thank you

 

[Rasm]

AlphaFrog - Hmmm - never worked with one of these guys before (I guess this is a form control not an activeX control) - your code works fine -BUT

1) How do I delete the scrollbar from my sheet - lol - not from design view by the looks of it. I feel bad asking.

2) How do I set the name of the Scroolbar or how do I get the "properties" - that is if these have guys have properties. I want to read the steps of scrollbar. Also I will have 4 scrollbars in the end - I assume they each have some sort of name rather than just a position on the sheet.

3) How do you stop the slider from black flashing.

Sorry to ask these real dum questions - But I am new to VBA - I have been using VB in Visual Studio. Excel sure has a lot of it's own stuff.

PS - if you are shaking your head - cuz I dont know these Form thingies - just say so - then I study - then ask questions.

 

[AlphaFrog]

1.) Right-click on it. Then left-click on it to get rid of the pop-up menu. Then delete. Or Ctrl+Click on it to select.

2.) You can change its name in the Name Box just to the left of the Formula Bar. Other properties can be found by right-clicking on it and select Format Control (see the Control tab). All these "properties" can be read\write from VBA of course.

3.)Don't think you'll have a "flashing" issue with the Form scroll bar. Don't know why the ActiveX scroll bar flashes the way it does.

 

[Rasm]

AlphaFrog -Ok - I am trying to get my head around these form control - is there anyway I can add an activeX control in the same fashion as in your code below and still use the .OnAction event. That thing is cool.

If I have to use the Form controls - what is the best way to read from code the name of the control just added. I did find out how to do it via the xl GUI - but I want to do it by code. The reason I want the Name is so I can populate my ListBox/ComboBox. But maybe these Form contorls are just different animals.

I figured I could just replace scrollbars with say ListBox or ComboBox - but nope - so what is the coomand to add a ListBox or a Combox.

Could you use Form type controls from the Form toolbar instead. You can assign macros from other modules to those controls without the need for Sheet event macro code.

Code:


' Add "Form" type ScrollBar control to cells C4:E4 and assign a macro from Module1 called scrZoomX With Range("C4") ActiveSheet.ScrollBars.Add(.Left, .Top, .Resize(, 3).Width, .Height).Select End With Selection.OnAction = "Module1.scrZoomX"
</PRE>

<!-- / message --><!-- sig -->__________________

 

[AlphaFrog]

...is there anyway I can add an activeX control in the same fashion as in your code below and still use the .OnAction event.

As best I can tell, no.



As an example, the code below adds two Forms-type scroll bars and buttons to the worksheet below the currently active XY Scatter chart. Select your chart and then run the Zoomer_Initialize macro. Put all the code in the same module.

' Original settings
Private MajUnit As Double
Private MajTickCount As Long
Private MaxScale As Double
Private MinScale As Double
Private ScaleRange As Double
Private cht As Chart

' Forms objects
Private scrZX As ScrollBar  ' X axis scale zoom
Private lblZ As Label       ' X axis "Zoom" label
Private scrRX As ScrollBar  ' X axis scale range
Private lblR As Label       ' X axis "Range" label
Private btnReset As Button  ' Reset button
Private btnClose As Button  ' Close button


Sub Zoomer_Initialize()

    Dim rngSeed As Range, ChartCellWidth As Integer

    Set cht = ActiveChart
    
    ' ref points on sheet for controls
    Set rngSeed = ActiveSheet.Cells(cht.Parent.BottomRightCell.Row + 2, cht.Parent.TopLeftCell.Column)
    ChartCellWidth = cht.Parent.BottomRightCell.Column - cht.Parent.TopLeftCell.Column
    
    ' Add controls
    With rngSeed
        Set lblZ = ActiveSheet.Labels.Add(.Left, .Top, .Width, .Height)
        lblZ.Caption = "Zoom"
        With .Offset(1)
            Set lblR = ActiveSheet.Labels.Add(.Left, .Top, .Width, .Height)
            lblR.Caption = "Range"
        End With
        With .Offset(, 1).Resize(, ChartCellWidth - 2)
            Set scrZX = ActiveSheet.ScrollBars.Add(.Left, .Top, .Width, .Height)
            scrZX.OnAction = "scrZX_Change"
        End With
        With .Offset(1, 1).Resize(, ChartCellWidth - 2)
            Set scrRX = ActiveSheet.ScrollBars.Add(.Left, .Top, .Width, .Height)
            scrRX.OnAction = "scrRX_Change"
        End With
        With .Offset(, ChartCellWidth - 1).Resize(2)
            Set btnReset = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
            btnReset.Caption = "Reset"
            btnReset.OnAction = "btnReset_Click"
        End With
        With .Offset(, ChartCellWidth).Resize(2)
            Set btnClose = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
            btnClose.Caption = "Close"
            btnClose.OnAction = "btnClose_Click"
        End With
    End With
    
    With cht.Axes(xlCategory)

        '.MinimumScaleIsAuto = True
        MaxScale = .MaximumScale
        MinScale = .MinimumScale
        ScaleRange = .MaximumScale - .MinimumScale
        MajUnit = .MajorUnit
        MajTickCount = ScaleRange / MajUnit

        ' Zoom X
        scrZX.Max = MajTickCount
        scrZX.Min = 1
        scrZX.SmallChange = 1
        scrZX.LargeChange = 1
        scrZX.Value = 1

        ' Range X
        scrRX.Max = 1
        scrRX.Min = 1
        scrRX.SmallChange = 1
        scrRX.LargeChange = 1
        scrRX.Value = 1

    End With

End Sub

Private Sub scrRX_Change()

    With cht.Axes(xlCategory)
        .MinimumScale = MinScale + (MajUnit * (scrRX.Value - 1))
        .MaximumScale = .MinimumScale + (MajUnit * (MajTickCount - scrRX.Max + 1))
    End With

End Sub

Private Sub scrZX_Change()

    With cht.Axes(xlCategory)
        
        .MaximumScaleIsAuto = False
        scrRX.Max = scrZX.Value
        
        .MinimumScale = MinScale + (MajUnit * (scrRX.Value - 1))
        .MaximumScale = .MinimumScale + (MajUnit * (MajTickCount - scrRX.Max + 1))
                
    End With

End Sub

Private Sub btnReset_Click()

    With cht.Axes(xlCategory)
        .MaximumScale = MaxScale
        .MinimumScale = MinScale
        .MajorUnit = MajUnit
    End With
    
    scrZX.Value = 1
    scrRX.Max = 1
    scrRX.Min = 1
    scrRX.Value = 1
    
End Sub

Private Sub btnClose_Click()
    
    btnReset_Click
    scrZX.Delete
    scrRX.Delete
    btnReset.Delete
    btnClose.Delete
    lblZ.Delete
    lblR.Delete
    
End Sub

 

[Rasm]

AlphaFrog - thanks a million - I am getting there -