Wise Owl Answers - How Do I Control Different Cells with One Spin Button in Excel VBA?

This video explains how to use a single ActiveX spin button control in Excel to control different cells in a worksheet. You'll learn how to set properties of a spin button control using the Properties window and using VBA code. You'll also learn how to use the Worksheet Selection Change event to trigger code when a user clicks on a cell. Finally, you'll see how to use some basic If statements to check if a cell value is number and if the number falls within a range.

Chapters
00:00 The Question
00:29 Creating a Spin Button
02:37 Using the Worksheet Selection Change Event
05:14 Using the Intersect Method
07:03 Detaching the Spin Button from a Cell
07:47 Highlighting the Last Valid Selection
09:47 Checking if a Cell Contains a Number
11:27 Checking a Number is Within a Range

[yadatube]

Wise Owl - you couldn't have done a BETTER JOB with this than you did. Just Fabulous. Incrementally educating us from a simple base to more powerful solutions. Great job!

Not that I'm asking for an update (I already have some code to do this...) but if A1:A5 had numbers like this:

12
.003
15.6554
-.3822
44.4011011

Make a single spin button adapt to the each number, so that it's not just an integer being added, but (and for example....) in the case of .003, you're making it .004, or .002.

Adaptive spinning :)

Regards...

[AamirSaeedTajalli]

Thanks Andrew.
So i asked a worth attention getting question, proud to get your attention.

[renz-mo]

Very fantastic tutorial. Easy to follow but very detailed.

[wayneedmondson]

Hi Andrew. Awesome tutorial! I started my solution in the code window of the ActiveX control using the SpinButton1_Change event and me.SpinButton1.LinkedCell. The problem there is that the code runs after the change, so the first click is always one behind what you want. Your solution of putting it into the Worksheet_SelectionChange event is perfect. Thanks for the insights. Love your videos, channel and web site. Learned so much from you already. Looking forward to more :)) Thumbs up!!

[RohithKK-uhpp]

Sir very nice video. You deserve 50 M + subscribers and I wish you will get it soon.

[Pratimoksha]

Thank you !!! i offert you, my second blue thumb in this comment ! 👍 ← even if this one is yellow...

[NeilCrapper]

Thank you for the very comprehensive answer to my question. If ever you come to Brixham let me know and I will buy you a beer, Covid permitting.

[KhalilYasser]

Thank you very much. Awesome as usual.

[NeilCrapper]

Fascinating as ever, thank you. I was wondering why you need the double negative of "If Not Application.Intersect(ActiveCell, Sheet1.Range("A1:A5")) Is Nothing Then" rather than "If Application.Intersect(ActiveCell, Sheet1.Range("A1:A5")) Then" I have found it doesn't work but I don't understand why. Are you able to throw any light on this for 68 year old beginner please?

[neyqwrc]

Hi Wise Owl, this is great tutorial. I was wondering, what if I want to scroll or move my sheet without moving the spinbutton? Is that possible? Thanks!

[PJ-higz]

Is it possible to edit multiple cells with one spin button click? Say A1:A5 all change simultaneously with one press of the spin button. I thought this was what the video was about.

[yashsomaiya]

Hi Andrew. Can u please make excel vba videos on conditional formatting, table, formatting, how to effectively handle or debug different types of errors and dashboards creation? The reason I m requesting you to make vides because there isn't much of help available on net.

[bacrc]

Hi Andrew. Can I connect to exist edge browser.

[TeezyThaKidd]

ActiveX is evidently not a thing on macOS, so I while I had no success with the Form Control spin button on my Mac, I was able to do this within Excel (the ActiveX method) on Windows.

Can't find ANY documentation or video tutorial on how to do the Form Control method on macOS.

[mrcat]

You seem to be the right man to help my situation. Im trying to make a comprehensive stock level management but i have a few problems. I make products that have a list of parts that i make. I want to know how many parts i have left after i make one product. I also want to include sub assembly. I also have multiple products that may share parts from other products along with new parts. Please please help me it would make my life so much better :). Thank you for the content regardless!!

[romanemul]

Can you do 'implements' VBA statement tutorial

[mclaren]

This is exactly what I am looking for. However, it doesn't work for me. The only problem I am seeing is when I type "Application" it does not give me the option to auto complete tab. It does give that option when i started to type "Intersect". Any ideas?

[philpavlidis]

Hi,
Instead of counting numbers, can it change cells? for example from A1 TO B1, C1 etc ...

[RyanScanlon-xjdj]

Thank you for the tutorial. Im trying to accomplish exactly what this video is showing me. However, i would like to use one spin button and have a dropdown list select the cell i want to add data to. Can this be done, or im a limited to manually selecting the cell?

[alexsandoval]

78 / 5000
translate.
Can you read an excel file or an access database that is in OneDrive/Sharepoint with VBA of course.