Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Spin button macro Results

HI .. Trying to use Code prepared By "Palmetto" for a very dymamic range based spin buttom , It works perfectly in his downloadable sample worksheet, and is exacly what I need, but I just can't reproduce it. Here's the forum link "http://www.excelforum.com/excel-programming/720986-spin-button-macro.html", I've tried adding a "spinbutton 1" on the spreadsheet and placing the code in the "Tab"> "View code", as described, but it never works, Any help would be appreciated. P.S. I'm new to this so basics are appreciated.

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!

Hello!

I have learned how to create an auto sort macro based on cell changes within a range. The code works fine when manually changing these cells. However, the values in this range are typically updated by clicking a spin button (Active X).

Unfortunately, the existing code does not auto sort when the cells are updated in this manner.

Here is the code I use in the worksheet...


	VB:
	
 Range) 
    If Not Intersect(Target, Range("A4:B11")) Is Nothing Then 
        Call Module5.DoSort 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

... and the code for the macro


	VB:
	
 DoSort() 
    Worksheets("ScrollBar Average Sale").Range("A4:B11").Sort Key1:=Worksheets("ScrollBar Average Sale").Range("B4"),
Order1:=xlDescending, Header:=xlNo 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Any suggestions on getting the auto sort macro to run when the Spin Button updates these values?

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 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.

Thanks

Mike Moore

Hi all,

I suspect the answer to this will be no, but I'll ask anyway. I am using a Spin Button to move data up/down a list, and I have written some code (below) that identifies wheter or not the selected item is top/botom of the list. If it is, I would like to disable the option to spin up/down, but I can only find an option to disble the the button as a whole. Is there another option to do this or will I have to write some code to stop it working in these situations.

Thanks.

The code that I use to identify options on the top/bottom of the list. This Macro is activated by an _change event on the UserForm.

 
Sub SpinButtonActivation()
 
Dim ItemToCheck As String            ' The item to be checked within the list
    ItemToCheck = Options.txtListOptions.Text
    Set RngFound = Range("AY:AY").Find(ItemToCheck, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
    If Not RngFound Is Nothing Then
        iRow = Sheets(1).Cells(Rows.Count, 51).End(xlUp).Offset(1, 0).Row - 1
        If RngFound.Row = 2 Then ' Spin Up is disabled if top row is selected
            Options.spinListOptions.Enabled = False
        ElseIf RngFound.Row = iRow Then ' Spin Up is disabled if bottom row is selected
            Options.spinListOptions.Enabled = False
        Else: Options.spinListOptions.Enabled = True
        End If
    End If
End Sub


Hello,

First, I apologize if this has been asked and solved before. I did a quick search, but couldn't find anything similar to what I'm asking, and I've spent many hours looking through various pages Google has told me were relevant (and most of the time, weren't relevant at all).

The issue I'm having is with the ActiveX Spin Button (in 2007, formerly in the Control toolbox in 2003). I need the Spin Button to call a certain macro whenever it is clicked up or down. I know how to do this with one spin button, the problem is that the workbook I'm creating could potentially have dozens (or even hundreds) of these spin buttons that all need to call the same macro. It doesn't seem feasible to have to create separate Change event functions for each of these.

My company used to use the simpler form controls for this process, but a situation has come up where we need the ability to make the buttons invisible, or at least appear disabled, and that doesn't seem doable with the form controls.

So my question is, is there a way to specify in my workbook that whenever a spin button is clicked, this particular macro is to be called? Or even, whenever an ActiveX object is clicked, call the macro, because the spin buttons will be the only ActiveX objects in the workbook. I've read a bit about how to create global event handlers for worksheets and workbooks, but I can't find anything related to spin buttons specifically.

Any help is appreciated, even if it's just to tell me that it can't be done. Thanks very much!

Dear Forum Users,

I am attempting to create a worksheet that is designed to keep track of the "compliance scores" of 100+ individuals in 5 different areas of performance. I decided that I could use the "spin button" to tally up their scores as I collect the data.

The problem is; linking each spin button to it's designated cell is going to be extremely time consuming because there will end up being hundreds of them.

Is there a different tool I should be using to "tally" the scores? If not, Is there a quicker way to link these "spin buttons" to cells than entering their properties and linking them one at a time?

Please forgive my ignorance, but I have limited knowledge in programming Macros.

Thank you,
Daniel Brady

Hello forum members,
Using Excel 2007 I created a worksheet for my neighbor who runs a restaurant and would like to track the # of orders for each menu item buy just clicking a button corresponding to the items on the menu. I have a Column A for each menu item (142 of them) and then Column B contains Active X spin buttons. Column C is the numeric value of each spin button. I then selected each spin button and using the properties box,manually programed the link for each button to the corresponding cell in Column C. I wanted to then replicate column B & C (which represents one month of sales) and paste to column D & E to create another month (and repeat this) so I literally wouldn't have to create 142 spin buttons and their cell links 11 more times. Copying and pasting worked for all formulas and buttons with the exception of the "linked cell" info. This does not automatically update (the column letters) like my formulas do. Is it possible to do this? As I'm sure you have already deduced I am a "Visual Basic" and "Macro" challenged individual but I thought I'd ask and hopefully learn how to streamline this process. I thank you in advance and I'm sorry for the lengthy post.

Hello!

I have learned how to create an auto sort macro based on cell changes within a range. The code works fine when manually changing these cells. However, the values in this range are typically updated by clicking a spin button (Active X).

Unfortunately, the existing code does not auto sort when the cells are updated in this manner.

Here is the code I use in the worksheet... I got a tip on another forum that I may need to change the even to Calculate, instead of Change. But when I make that change I receive a compile error.

Private Sub Worksheet_Change(ByVal Target
As Range) 
    If Not Intersect(Target, Range("A4:B11")) Is Nothing Then 
        Call Module5.DoSort 
    End If 
End Sub

... and the code for the macro

Sub DoSort() 
    Worksheets("ScrollBar Average Sale").Range("A4:B11").Sort Key1:=Worksheets("ScrollBar Average
Sale").Range("B4"), Order1:=xlDescending, Header:=xlNo 
End Sub


Any suggestions on getting the auto sort macro to run when the Spin Button updates these values? I should probably mention that the spin button is on a different worksheet.

Hi

I need some help using the spinner tool (the 2 way arrows) as a macro. I want it to point to different cells after each press. So, basically:

Click down once, go to cell A5
Click down again, now go to cell A6
Click down again, now go to cell A7
Clicking down again won't do anything, A7 is the end of the road

And the same thing backwards when clicking up.

I use the two commands SpinButton1_SpinDown() and SpinButton1_SpinUp(). I put the Range("A5").Select, etc. as the code. I don't know how to do the incremental part. I need a counter in there...

Thanks guys.

Darren

forecast model that counted the number of occurrences that exceeded certain thresholds. Now he wants to extract those lines of exception data (like top 10, Top 20, Top 30, etc.) and return them in the form of an exception report, maybe in a different tab or on the same sheet. I can't use a macro to copy paste an autofilter Top 10 since the tool has sliders and spin buttons that can manipulate (3) varibles to produce and ever changing set of results.

Ideas?

Hi
this is a spin of from another question.

Can the display macro list alt T/M/M be replicated by code or a macro? (ready for macro selection)

when I try and record this as a macro the "stop recording" button is greyed out and I can't conclude the record.

I would like this as I am trying to enable the macro list to display from the right click of the mouse.

thanks kd

Using Excel 2000

I have created a report sheet that must be filled in on a monthly
basis. There is one column per month. One of the cell is a comment box.
As columns will be hidden/displayed at each month change and for
presentation purposes, I have added a textbox control on the sheet (
not moved nor resized ).

The linked cell reference of the textbox comes from a dynamic name that
returns the reference of comment cell of the reporting month.

I have created the following local named range
comment = OFFSET('Sheet1'!$A$1,0,'Sheet1'!mth_offset)

In the linked cell property of the text box
linked cell = 'Sheet1'!comment

mth_offset is another local named range linked to a spinbutton. As I
spin up and down, the text box correctly displays the content of the
cells in row 1, as you would expect from the 'comment' formula.

The strange thing is that if as soon as I edit the content of the text
box, the dynamic link stops working, i.e. the textbox no longer updates
its content as I spin up/.down.

I have found a workaround: in the spin button change event procedure, I
add the code: "TextBox1.LinkedCell = TextBox1.LinkedCell". With that
the text box updates correctly.

Has anyone faced this problem ?
Is there a workaround that does not rely on code ? - getting users to
activate macros can be a pain.

Thanks for your help.
Regards

I have a VBA application written in Excel 2000.

Running the appl on Excel 2003 generated errors,
and the appl would not run on the Excel 2003 PC.

(I can't remember exactly what the errors read,
but Excel 2003 didn't recognize some components
or code. And the code would not compile.)

The appl contains about 10 forms, custom menus,
macros, & custom functions. The forms have
standard, native controls that include simple text
boxes, command buttons, spin buttons, combo
boxes, & labels. All controls are on forms -- no
controls are imbedded in worksheets.

I can run an earlier version of the same Excel 2000
VBA appl on the same Excel 2003 PC with no
problems. But this earlier version has no forms and
no custom menus, just macros & custom functions.

I suspect the forms are causing the errors, and
the problem is the Excel 2003 object libraries are
different from the Excel 2000 object libraries. And,
I'll need to set references in Excel 2003 to the
Excel 2000 object libraries.

These are the libraries I suspect that I have to set
references for:
- Microsoft Office 9.0 Object Library (MSO9.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)

(If they are even listed in Excel 2003. I didn't look,
and the PC is at a different location. I'll be trying
to get the appl working on Wed, Aug 9. But, I'll be
at that other location tonight, Tue, Aug 8th, & will
have some brief time on that Excel 2003 PC.)

Am I barking up the right tree,
or just howling at the moon?

Any confirmation, hints, traps, or other possibilities
would be appreciated.

Also, these are the object libraries that are referenced
in Excel 2000 where the appl was developed:
- Visual Basic For Applications (VBE6.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)
- OLE Automation (stdole2.tlb)
- Microsoft Forms 2.0 Object Library (FM20.DLL)
- Microsoft Office 9.0 Object Library (MSO9.DLL)

i have four cells each with a spin button (form control) next to it that can add/subtract its total when clicked. the first cell (A1) is the maximum number for the the next 3 cells (which can be increased/decreased). i want to set it so you can only add to each of the three other cells (say B1,C1,D1) until the sum of the three cells is equal to A1.

for example, if A = 10, B = 4, C = 4, D could only be increased up to 2 (the same for B & C if you swapped them with D).

i already set the format control's minimum to 0 but since A1 can be increased/decreased it will be variable so i can't really set a maximum on it. is there any way i could easily do this, possibly without using a macro? much appreciated.

I have a VBA application written in Excel 2000.

Running the appl on Excel 2003 generated errors,
and the appl would not run on the Excel 2003 PC.

(I can't remember exactly what the errors read,
but Excel 2003 didn't recognize some components
or code. And the code would not compile.)

The appl contains about 10 forms, custom menus,
macros, & custom functions. The forms have
standard, native controls that include simple text
boxes, command buttons, spin buttons, combo
boxes, & labels. All controls are on forms -- no
controls are imbedded in worksheets.

I can run an earlier version of the same Excel 2000
VBA appl on the same Excel 2003 PC with no
problems. But this earlier version has no forms and
no custom menus, just macros & custom functions.

I suspect the forms are causing the errors, and
the problem is the Excel 2003 object libraries are
different from the Excel 2000 object libraries. And,
I'll need to set references in Excel 2003 to the
Excel 2000 object libraries.

These are the libraries I suspect that I have to set
references for:
- Microsoft Office 9.0 Object Library (MSO9.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)

(If they are even listed in Excel 2003. I didn't look,
and the PC is at a different location. I'll be trying
to get the appl working on Wed, Aug 9. But, I'll be
at that other location tonight, Tue, Aug 8th, & will
have some brief time on that Excel 2003 PC.)

Am I barking up the right tree,
or just howling at the moon?

Any confirmation, hints, traps, or other possibilities
would be appreciated.

Also, these are the object libraries that are referenced
in Excel 2000 where the appl was developed:
- Visual Basic For Applications (VBE6.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)
- OLE Automation (stdole2.tlb)
- Microsoft Forms 2.0 Object Library (FM20.DLL)
- Microsoft Office 9.0 Object Library (MSO9.DLL)

Hi All,
I have a Conditional Formatting query where in essence I am needing to ‘make invisible’ a range of data by changing the font and background fill to white (and at the same time nullifying the effects of a number of other Conditional Formats). On the surface, this seems an easy thing to achieve, but the problem is, the range I need to apply this CF is a moving range and is dependant on a user changeable date.

Description of worksheet:
The sheet is managing shifts worked over 1 year by 21 people. The top 33 rows are a frozen (and collapsing) display area below which is a matrix of 365 days down and 21 people across. The TODAY button uses a macro to scan the dates and display todays entry directly beneath the freeze line of row 33. The date in M28 can be entered manually and after hitting enter, that dates data is now displayed under the freeze line. Also, using the spin button will increment or decrement the date in M28 moving the entire matrix up or down.

My query..........
What I’m hoping to achieve, is to display ONLY the data selected by the date in M28 (ie that shown under the freeze line, and for all remaining rows to be ‘whited out’, but as the user increments/decrements M28, then the “whiteout” range also needs to increment/decrement. I have whited out a second worksheet “example” as if I had selected 18 Jan (this sheet will not move as there are no buttons, but the data is still there under the whiteout).

I hope this is not TOO unclear, but I would be grateful if anyone could suggest a solution or even tell me if this is not possible – or indeed any other way of achieving the same result ( I had thought of hiding the rows below the freeze line, but the freeze/unfreeze operation would probably be too jerky.

Many Thanks in anticipation

Shytott

I have a VBA application written in Excel 2000.

Running the appl on Excel 2003 generated errors,
and the appl would not run on the Excel 2003 PC.

(I can't remember exactly what the errors read,
but Excel 2003 didn't recognize some components
or code. And the code would not compile.)

The appl contains about 10 forms, custom menus,
macros, & custom functions. The forms have
standard, native controls that include simple text
boxes, command buttons, spin buttons, combo
boxes, & labels. All controls are on forms -- no
controls are imbedded in worksheets.

I can run an earlier version of the same Excel 2000
VBA appl on the same Excel 2003 PC with no
problems. But this earlier version has no forms and
no custom menus, just macros & custom functions.

I suspect the forms are causing the errors, and
the problem is the Excel 2003 object libraries are
different from the Excel 2000 object libraries. And,
I'll need to set references in Excel 2003 to the
Excel 2000 object libraries.

These are the libraries I suspect that I have to set
references for:
- Microsoft Office 9.0 Object Library (MSO9.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)

(If they are even listed in Excel 2003. I didn't look,
and the PC is at a different location. I'll be trying
to get the appl working on Wed, Aug 9. But, I'll be
at that other location tonight, Tue, Aug 8th, & will
have some brief time on that Excel 2003 PC.)

Am I barking up the right tree,
or just howling at the moon?

Any confirmation, hints, traps, or other possibilities
would be appreciated.

Also, these are the object libraries that are referenced
in Excel 2000 where the appl was developed:
- Visual Basic For Applications (VBE6.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)
- OLE Automation (stdole2.tlb)
- Microsoft Forms 2.0 Object Library (FM20.DLL)
- Microsoft Office 9.0 Object Library (MSO9.DLL)

I'm using a spin button on a worksheet and I want to call a subroutine that
performs a GoalSeek from the spinbutton_spindown procedure. I have the
following spinbutton procedure and goalseek procedure.

Private Sub Cout_Spin_SpinDown()
Worksheets("Capacitors").Calculate
Range("Cout").Value =
Worksheets("Capacitors").Range("Cap_next_value_down")
Worksheets("Data").Calculate
Calc_Crossover
Worksheets("Main").Calculate
End Sub

Sub Calc_Crossover()
Range("C45").GoalSeek Goal:=0, ChangingCell:=Range("B45")
End Sub

The Calc_Crossover works fine when I run it as a macro. I have tried to run
it programatically with the Calc_crossover routine in a separate module and
on the same sheet as the spin_button routine. The range used for the
GoalSeek is on a separate worksheet "Data", however, I have used the
worksheets object to try to specify this as well and it still doesn't work.

I get the "GoalSeek method of Range object failed" message.

I have the application in manual calculation mode and used range
calculations to control the recalculation.

Can anybody offer any suggestions on what I might try? Thanks!


No luck finding an answer? You could always try Google.