Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Copying cell links in spinners 'relatively'? (2002 )

    In my spreadsheet I am using spinners to help the user input data quickly, and as a means of validation. The thing is, there are a lot of them. I am modelling 18 entities, the inputs for each of them are in an area of 3 columns x 10 rows = 30 spinners x 18 entities = 540 spinners.

    I've done one of the entity's 30 spinners already and would like to be able to copy them into each of the remaining 17 areas.

    The good news is I've found that by holding down the control key I can select them all and paste them into a new area, and they'll be arranged in the same way.

    The bad news is I seem to have to update the control link of each of the spinners manually. By control link I mean the cell reference you type when you right-click a spinner and go Format Control / Control / Cell Link. I was hoping that if I'd linked one spinner to, say, cell d4, and then copied the spinner into the next column to the right, the link would automatically change to cell e4 -- in other words, like a formula with relative references. Unfortunately, when I do this, the link in the copied spinner is d4.

    Is there any way to make the links change like relative formula references? Or any other way to avoid setting each of them manually?

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

    Re: Copying cell links in spinners 'relatively'? (2002 )

    You could use a single spinner control and use code to move it to the active cell.

    In the attached workbook, there is a single spinner control Spinner1 from the Forms toolbar; it could also have been done with a spinner from the Control Toolbox, but the code would have been slight;y different.

    There is code for the Worksheet_SelectionChange event in the worksheet module. If the active cell is in A14 or in F1:I4, the spinner is displayed to the right of the cell and the linked cell of the spinner is set to that cell. If the active cell is not within one of those ranges, the spinner control is hidden.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Copying cell links in spinners 'relatively'? (2002 )

    You are a rock god!

    How do you know learn all this stuff?

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

    Re: Copying cell links in spinners 'relatively'? (2002 )

    By trial and error (lots of error <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>)

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Copying cell links in spinners 'relatively'? (2002 )

    Yeah, I know about the error part.. I've finally bitten the bullet and got a copy of John Walkenbach's VBA for Dummies. He does a pretty good job, though sometimes I think I'll have to wait until the Programing for Neanderthals series to come out....

    Now, not to sound too greedy, but regarding the elegant solution you sent through -- in my initial joy I overlooked something (actually I should have mentioned it to begin with). Not all the cells which I want to control have the same range or increments. Is it possible to modify the code so that, for example, cell a1 has a range of 0-100 with increments of 5; cell a2 has a range of 15-30 in increments of 1, etc?

    If not, you're still a rock god.

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

    Re: Copying cell links in spinners 'relatively'? (2002 )

    Yes, that is possible. See the attached version for some examples (chosen arbitrarily).

    The essential points are
    a) A control from the Forms toolbar is a Shape object.
    [img]/forums/images/smilies/cool.gif[/img] The specific properties of the control can be set through the ControlFormat property of the Shape object.
    The ControlFormat properties used in the code are LinkedCell, Min, Max and SmallChange.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Copying cell links in spinners 'relatively'? (2002 )

    Thanks Hans. Hope you get some sleep sometime.... <img src=/S/doze.gif border=0 alt=doze width=15 height=15>

Posting Permissions

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