Results 1 to 11 of 11

Thread: spinner (2003)

  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    spinner (2003)

    All

    I am using the following code to include a spinner on a worksheet. Can someone please advise how I can:
    Make the spinner increment/decrease cell D1 by 500 on each click
    Place the spinner on the right hand side of cell D1 - making it appear relative to the position of cell D1 and not hard code the spinners position
    Make the spinner appear each time a user clicks on cell D1 and then disappear when they click out of the cell

    Ideally I wish to use the same spinner in 12 cells in the range D1 to O1 with the spinner appearing as the user clicks into the cell and disappearing when they click away.



    Sub addSpinner()
    Dim sb As Object
    Set sb = Worksheets(1).Shapes.AddFormControl(xlSpinner, Left:=100, Top:=10, Width:=20, Height:=40)
    With sb.ControlFormat
    .LinkedCell = "D1"
    .Max = 10000
    .Min = 0
    End With

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: spinner (2003)

    Add the following line just above End With:
    <code>
    .SmallChange = 500</code>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: spinner (2003)

    I think this should work.

    Sub AddSpinner()
    Dim sb As Object
    Set sb = Worksheets(1).Shapes.AddFormControl(xlSpinner, Left:=100, Top:=10, Width:=20, Height:=40)
    With sb.ControlFormat


    .Value = 0
    .Min = 0
    .Max = 10000
    .SmallChange = 500
    .LinkedCell = "$D$1"

    End With
    End Sub
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: spinner (2003)

    Here's a somewhat more elaborate version:

    Sub addSpinner()
    Dim sb As Object
    Set sb = Worksheets(1).Shapes.AddFormControl(Type:=xlSpinne r, _
    Left:=10, Top:=10, Width:=20, Height:=10)
    sb.Name = "MySpinner"
    sb.Visible = False
    With sb.ControlFormat
    .Max = 10000
    .Min = 0
    .SmallChange = 500
    End With
    End Sub

    To make it work for D1:O1, create the following event procedure in the worksheet module (not in a standard module or in ThisWorkbook):

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim varVal
    Application.EnableEvents = False
    With Me.Shapes("MySpinner")
    If Not Intersect(ActiveCell, Range("D1:O1")) Is Nothing Then
    .Left = ActiveCell.Offset(0, 1).Left
    .Top = ActiveCell.Top
    .Height = ActiveCell.Height
    .Visible = True
    varVal = ActiveCell.Value
    .ControlFormat.LinkedCell = ActiveCell.Address
    .ControlFormat.Value = varVal
    Else
    .Visible = False
    End If
    End With
    Application.EnableEvents = True
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: spinner (2003)

    All

    I have now ammended the code as follows:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim sb As Object
    Set sb = Worksheets(1).Shapes.AddFormControl(xlSpinner, Left:=100, Top:=10, Width:=20, Height:=40)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("C1:C2"), Target) Is Nothing Then
    With sb.ControlFormat
    .LinkedCell = "D1"
    .Max = 10000
    .Min = 0
    .SmallChange = 500
    End With
    Else: sb.Visible = False
    End If
    End Sub

    However the following problems persist:
    1. The spinner does not disappear/unload when I click outside C1:C2 as I had expected
    2. I cannot yet make the spinner appear in a position relative to the cell which has been clicked into

    Assistance on these points will be much appreciated

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: spinner (2003)

    Here is my test workbook. The code in Module1 has already been run (it should be run only once, to create the spinner). The code behind the worksheet shows/hides the spinner as needed.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: spinner (2003)

    Hans

    Thank you for the example work book, which works well. I have spent some time examining the code contained therein and almost understand what is going on. However I will be grateful if you can answer the following 4 queries.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim varVal
    Application.EnableEvents = False '****What does this mean/do and why is it necessary?*****
    With Me.Shapes("MySpinner") '****How does the prgram know to look for mySpinner in module1. Can I get it to look for myspinner in other modules?****
    If Not Intersect(ActiveCell, Range("D1:O1")) Is Nothing Then '****Please explain this statement. It looks like a double negative which I cannot get my head around
    .Left = ActiveCell.Offset(0, 1).Left
    .Top = ActiveCell.Top
    .Height = ActiveCell.Height
    .Visible = True
    '*****What do the following 3 lines of code do? I thought that the target cell value update happened automatically?****
    varVal = ActiveCell.Value
    .ControlFormat.LinkedCell = ActiveCell.Address
    .ControlFormat.Value = varVal
    Else
    .Visible = False
    End If
    End With
    Application.EnableEvents = True
    End Sub

    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: spinner (2003)

    1) If you use worksheet event code, it is generally a good idea to turn off event handling while your code manipulates cells, for modifying the value of a cell directly or indirectly (because a formula gets recalculated) could trigger another worksheet event procedure, and this could lead to a cascading series of events, or even to an unending loop. You must always reset event handling to True at the end of the procedure.

    2) The AddSpinner procedure creates a spinner on the worksheet and assigns it the name MySpinner:

    Set sb = Worksheets(1).Shapes.AddFormControl(Type:=xlSpinne r, _
    Left:=10, Top:=10, Width:=20, Height:=10)
    sb.Name = "MySpinner"

    So MySpinner is an object on the first worksheet, not in Module1. You must have run AddSpinner once manually, after that MySpinner is known. The worksheet event procedure does not have to look at Module1.

    3) You wanted to use one spinner to serve for all cells in the range D1:O1.
    Intersect(ActiveCell, Range("D1:O1") will result in the active cell if it is within D1:O1, and in Nothing if the active cell is not within that range.
    In other words, Intersect(ActiveCell, Range("D1:O1") Is Nothing will result in True if the active cell is not within D1:O1.
    Hence Not Intersect(ActiveCell, Range("D1:O1") Is Nothing is True if the active cell is within D1:O1.

    4) Since we use only one spinner, we must change its linked cell each time you select another cell in D1:O1. If you only changed the linked cell, the new linked cell (the active cell) would get the value of the previous linked cell. To avoid this, we set the value of the spinner to the value of the active cell.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: spinner (2003)

    Hans

    Thanks for the information provided. However, and I apologise if I am being a bit slow here:

    Given that addSpinner is at face value a normal sub in module 1, which line of your code triggers addSpinner to be run, so as to create mySpinner, if it is not called from the worksheet selection change event?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: spinner (2003)

    There is NO line in the event procedure that calls AddSpinner. AddSpinner should NOT be called repeatedly, it needs to be run only once. As I wrote two replies ago:
    <hr>The code in Module1 has already been run (it should be run only once, to create the spinner).<hr>
    and in my previous reply
    <hr>You must have run AddSpinner once manually<hr>
    So there is no need to have the worksheet event procedure run AddSpinner. In fact, it would cause problems if you did, because the code would try to create a new Spinner control with an already existing name.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: spinner (2003)

    Hans

    Thanks I have just had one of those moments when light dawns. Previously, I did not understand that once the object was created it is held in the back end of excel. However now that I have appreciated that the object is not destroyed each time the work sheet is closed, it all makes sense.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •