+ Reply to Thread
Results 1 to 5 of 5

Spin Button / Macro

  1. #1
    Registered User
    Join Date
    03-09-2010
    Location
    Leuchars, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Spin Button / Macro

    Hello all, I've been using the forum for awhile for answers, but just registered as I can't seem to find a solution to my problem.

    I have a spreadsheet which has user defined input, which works just fine as it is. However, counter to the 'if it ain't broke, don't fix it' idealogy, I want to fix it. I want to include either a spin button or a macro button to increase the cell value by 1. Easy enough to do with a spin button, but there's a total of 84 different cells which need it... and there will be roughly 50 separate spreadsheets (1 for each user). I can create a spin button and link it to a cell and it works perfectly, or I can write a macro, and assign it to a button, and it works perfectly.

    But I'm trying to avoid having to do this 84 times for every user. Is there a way to have the spin button automatically linked to the cell it's in? Or, alternatively, is there a way to write a macro so that it can select the cell in which the button is placed, then increase/decrease the value by 1. At least then it'd be one macro, and I could copy/paste a bunch of them.

    Thanks in advance!

  2. #2
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Spin Button / Macro

    Good morning nogoodnames

    Your first choice is achievable, but not easliy. Your alternative choice is very easy - which is why I'm going to go with that one.

    Draw a spin buttonusing your Control Toolbox toolbar. Make sure it is called SpinButton1.
    The put the code below in your sheet module (right click the worksheet tab, click View Code, then paste the code into the empty window that opens).

    Please Login or Register  to view this content.
    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Spin Button / Macro

    Just building a bit further on dominic's suggestion:
    If you want the spin button to be visible and applied only to a specific range of cells then you can use this code. The spinner will be visible when the activecell is in the range(A2:A10) and hidden if not.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Registered User
    Join Date
    03-09-2010
    Location
    Leuchars, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Spin Button / Macro

    Thank you both! Works a treat!

  5. #5
    Registered User
    Join Date
    11-27-2012
    Location
    Sierra Leone
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Spin Button / Macro

    This is great, thanks all

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1