Free Microsoft Excel 2013 Quick Reference

Spin buttons Results

How do I change the spin button interval? Right now when I click it it
always moves up or down by 1, while I want it to move by 0.05.


Adam Bush

I am copying a spin button several times (over 100 spin buttons in this input
spreadsheet) and rather than having to go into the "format control" screen
100+ times, I was hoping that there was a way to quickly and easily change
the cell link for each of the copies spin buttons. I removed the anchors in
the "cell link" filed of the format control however, when I copy and paste
the sin button, this cell link character doesn't change. Is there an quicker
way to change the "cell link" for each copied spin button?


thanks for your info I have managed to insert the Spinner button however I
have a list of information in the spread sheet I would like to link to the
button is this possible and how do i go about it.

Thanks for your help

"Gord Dibben" wrote:

> Right-click on a Toolbar and select Forms Toolbar.
> Click on the "Spinner" button.
> Draw a button on your worksheet.
> Right-click on the button and "Format Control"
> Set your parameters and define a cell that the spinner will affect.
> Gord Dibben Excel MVP
> On Fri, 19 Nov 2004 06:11:01 -0800, Andy K wrote:
> >How do I setup a spin button

I have a textbox called 'MultipleItemNumber'. IN this textbox I have specified a row source which essentially gives me a dropdown list. However, i want to lock this textbox and remove the drop-down button because I want to scroll through the entries in this textbox using a spin button. The list is always changing which is why I cannot specify a range (for example 1-20). Is there a way to scroll through the entries in the list using a spin button?

Secondly, I currently have a command button beside the MultipleItemNumber textbox that enters the value in this textbox into cell BB8 on the worksheet. This cell change, in turn, changes other textboxes in the userform. My question is given that my aforementioned problem is solved(i.e. scroll through the entries of the list in the MultipleItemNumber textbox), is it possible that everytime i change the MultipleItemNumber textbox using the spin button, it automatically changes cell BB8 in the worksheet which in turn would change my other textboxes in my userform?

ANy help would truly be greatly aprecaited. I've been stuck on this problem for a while. thanks

DSL PM'd about not wrapping VBA code

I programmed a spin button it works fine to about 2/3 of the data records then it stops.

Here is the code I used:

Dim Wks As Worksheet

Set Wks = ThisWorkbook.Worksheets(1)
With UserForms(0)
r = .SpinButton1.Value + 1
.TextBox1 = Wks.Cells(r, "A").Value
.TextBox2 = Wks.Cells(r, "B").Value
.TextBox3 = Wks.Cells(r, "C").Value
.TextBox4 = Wks.Cells(r, "D").Value
.TextBox5 = Wks.Cells(r, "E").Value
.TextBox6 = Wks.Cells(r, "F").Value
.TextBox7 = Wks.Cells(r, "G").Value
.TextBox8 = Wks.Cells(r, "H").Value
.TextBox9 = Wks.Cells(r, "I").Value
.TextBox10 = Wks.Cells(r, "J").Value
.TextBox11 = Wks.Cells(r, "K").Value
.TextBox12 = Wks.Cells(r, "L").Value
.TextBox13 = Range("J1001").Value
.TextBox14 = Range("I1001").Value
End With

Pretty straight forward but it will not move to the bottom of the data record list. Since the list is ever expanding did I miss something?

Hi, I'm currently doing a project on Excel and I am unable to edit the details for the buttons. For example, I want to have a spin button so a draw it in but when I right click on the button the options are not there. Again with the Command Button I cant assign a macro.

Its like the excel file I have been working on has infected all the excel programs on computer its opened on (school, home) but if i the computer hasnt been in contact with my file it works fine.

Can anyone help please


I am using a spin button (Forms toolbar) to control the value of a
single decision variable (cell) in my spreadsheet. Unfortunately, the
incremental change allowed by the UserForms spin button is integer and

Ideally, I would like the change to be 0.1 but I understand this
requires VBA programming knowledge which, unfortunately, I don't have!

Can anyone help me get round this?

Thanks in advance,


Pretty Simple I think, I'm just learning VBA. I want to be able to
control two different spin buttons with 4 different keys. (ex. i key is
up, k key is down on one spin button, j key is left and L key is right
on the other spin button. I already have some code written for these
buttons, I would just like to hit a key rather than clicking the
buttons. Also, I want to be able to hold down the key just like
clicking and holding.

Thanks in advance to all who respond.

I have created a workbook (xl 2K) with separate worksheets for each sales
Each sheet is identical to the other (except for name and individual data).
And each sheet contains a performance chart showing their sales.
There are 2 trend lines - the first is linear and the second is
The data is imported from multiple files in the folder.

The importing and charts work great.

On one sheet I have added a spin-button to control the order of the
( 2 - 6) and it works great. The code is in the worksheet module.

Is there a way that the spin-button click event can be made universal
and initiated from the
ThisWorkbook module? My intent is to remove worksheet code from each sales
person sheet and use the code in ThisWorkbook. (the reason is that new
sheets are being created all the time and I would rather not deal with
individual sheet code.)

I am aware that a user-form could do this, but would prefer to avoid this

Please let me know if I am insane or not...

Again - Thanks...


Remove "AYN" from email to respond

When setting up a spin button, is it possible to use something other
than whole numbers?

I want to use between 60% and 100% and move by 1% increments, but it
seems only to let me use the numbers 60, 100 and 1. When I key in
percentages, it rejects it.

Is it possible to do that? Thanks!

Hey everyone!

I've got a small problem i'm sure any of you will sort in a matter of minutes! :P (lets hope!)

Im trying to use a "Spin Button" on a protected sheet, but obviously I keep getting an error. This is different to the "Spinner" by the way - both can do the same thing, but I'm using Excel 2003, and the Spinner had a maximum value it could go to so instead I had to use a "Spin Button" which doesn't have any kind of limit.

Ive attached a test workbook!
Any help would be greatly appreciated!

EDIT: Ok I figured it out - I unlocked the "Linked Cell" but this allows a user to just clear the contents of the cell as its not locked.
Any other way of doing this?

Hello all,

Let me explain what I pretend:

I have plotted a chart in excel for a product anual sales and forecasts. I've created a drop list to select the product upon which the graph is updated. That works perfectly.

But now I want to put a spin button next to the graph that changes a parameter in forecast's formula.
Depending on the chosen product, the spin button should change the respective parameter value for that product - which is also the linked cell for the button.

I've come with a simple code and put it in the Spin Button object, but it doesn't work (note that I'm not a vba expert....)
Private Sub SpinButton1_Change()

If Range("G10").Value = "PRODUCT A" Then
        SpinButton1.LinkedCell = Range("P105")
End If

If Range("G10").Value = "PRODUCT B" Then
        SpinButton1.LinkedCell = Range("AG105")
End If

If Range("G10").Value = "PRODUCT C" Then
        SpinButton1.LinkedCell = Range("AX105")
End If

If Range("G10").Value = "PRODUCT D" Then
         SpinButton1.LinkedCell = Range("BO105")
End If

End Sub
What am I doing wrong? Thanks for your help!

I have a multicolumn listbox with perhaps 1,000 values. The default scroll bar freezes when you try to use it (common occurrence reported on the web) and I'm not a fan of scroll bars in general.

I do like being able to use a spin button to scroll up and down 1 entirely new screen of values at a time (not selecting any, just changing the view). Any ideas how to write this? I've found some entries on the web that were close, but not this solution.

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!

Hi guys

As I have never used spin button earlier so can anyone please help me how can I use spin button in excel userform to store time in hh:mm format in two textboxes.I would appreciate if anyone can provide me the code for this one.


I'm putting a spin button with a text box in a user form. I need for the text box to start with a default value that will then change when the user uses the spin button. Right now the default value is located in a worksheet, which is linked to the text box.

The problem I have is that when the user clicks on the spin button, the value reverts to 1 before then going up. This looks silly when the value started at, say 5, and the user clicked up on the spin button. Clearly I don't have the code set up correctly. Here's an example of what it looks like:
Private Sub SpinButtonDilPer_Change()
    DilPer.Text = SpinButtonDilPer.Value
End Sub

Private Sub DilPer_Change()

Dim NewVal As String

    SpinButtonDilPer.Min = 0
    SpinButtonDilPer.Max = 20
    NewVal = Val(DilPer.Text)
    If NewVal >= SpinButtonDilPer.Min And _
        NewVal <= SpinButtonDilPer.Max Then _
            SpinButtonDilPer.Value = NewVal
End Sub
DilPer is the name of the text box and obviously SpinButtonDilPer is the spin button. Any ideas on making this work better are appreciated.


Does anyone know how to linked the "small change" value in a spin button to take the value of another cell?


I can enter the spin button into a worksheet. However, I cannot get it to
change the target cell for changing values up or down. Do I need to write a
macro to do this for the spin button? If someone could walk me through one
of the ACTIVEX controls, I believe I could handle the remaining ones.


Mike Moore

Hi people,

I am new to this forum, and i urgently need help for one of my projects at hand.

I am creating a graph where i can edit my points on the graph by:
1) Choosing the point from a drop down list
2) Moving the point around the graph using spin buttons

The difficulty i am facing now is that the spin buttons somehow does not give me smooth scrolling of the points on the graphs. The buttons are functional and working, but there should be a glitch somewhere.

This is the code i am using.
Private Sub SpinButton3_Change()
Dim lngRow As Long

lngRow = Cells(18, 35).Value

    x = Cells(lngRow, 37).Offset(0, 1).Value
    y = Me.SpinButton3.Value
    If y > i Then
        Cells(lngRow, 37).Offset(0, 1).Value = x + 1
        Cells(lngRow, 37).Offset(0, 1).Value = x - 1
    End If
End Sub
Private Sub SpinButton3_GotFocus()
i = Me.SpinButton3.Value
End Sub
Someone please help me!

I made a spin button linked to a cell with a Max of 100 and Min of -100, and a small change of 1, however, when I go below 0 the number turns to 65,535 and does not go negative at all. I need it to go down from 0 to -1, to -2, and so on. Does anyone know how to fix this?