Our training courses

Book any course as classroom, onsite or online training, or as one-to-one consultancy
Excel Training Basic Excel Advanced Excel VBA Macros Office Scripts Excel Introduction Excel Intermediate Excel Advanced Excel Business Modelling Excel VBA macros Advanced VBA Fast track Excel VBA Basic Office Scripts Advanced Office Scripts Power Platform Training Pure Power BI DAX and fast-track Power Automate Power Apps Introduction to Power BI Adv. Power BI Reports Adv. Power BI Data DAX for Power BI Fast track Power BI Fast track Power BI/DAX Power Automate Desktop Basic Power Automate Advanced Power Automate Fast track Power Automate Power Apps Programming and AI SQL training Visual C# training Python training AI training Introduction to SQL Advanced SQL Fast track SQL Introduction to MySQL Introduction to Visual C# Intermediate C# Fast track C# Introduction to Python Advanced Python Fast track Python Using AI tools Using the OpenAI API SQL Server training Reporting Services Report Builder Integration Services Azure / SSAS Reporting Services Advanced SSRS Fast track SSRS Report Builder Introduction to SSIS Advanced SSIS Fast track SSIS SQL in Azure Data Studio SSAS - Tabular Model
Other training resources

Free resources

Read our blogs, tips and tutorials

Try our exercises or test your skills

Watch our tutorial videos or shorts

Take a self-paced course

Read our recent newsletters

Paid services

License our courseware

Book expert consultancy

Buy our publications

Getting help

Get help in using our site
Our training venues

Where we are

London

Manchester

Where you are

Across the UK

At your site

Online

At your computer
Why we are different

Transparent reviews

551 attributed reviews in the last 3 years

Delivery of courses

Refreshingly small course sizes

Outstandingly good courseware

Whizzy online classrooms

Wise Owl trainers only (no freelancers)

Nicer to work with

Almost no cancellations

We have genuine integrity

We invoice after training

Reliable and established

Review 30+ years of Wise Owl

View our top 100 clients

Search our website

GO
+44 (161) 883 3606 sales@wiseowl.co.uk Web enquiry form
We also send out useful tips in a monthly email newsletter ...

Sign up to newsletter

Our training courses

AI - Articial Intelligence

Power BI and DAX

Other training resources

Tips and tricks

Guides and tutorials

YouTube tutorials

Test your skills

Self-paced courses

Our training venues

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

This page has 3 threads | Add post

Video tutorials

Video tutorials

Finding our videos useful? Please consider helping us to share more!

VBA User Forms videos | How do I control different cells with one spin button in Excel?

Posted by Andrew Gould on 03 April 2021

How do I control different cells with one spin button in Excel?

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.

See our full range of VBA training resources, or test your knowledge of VBA with one of our VBA skills assessment tests.

This video has the following accompanying files:

File name	Type	Description
Different Cells with One Spin Button.xlsm	Excel workbook with macros
VBA Avoid Copy and Paste.zip	Zipped (compressed) file

Click to download a zipped copy of the above files.

There are no exercises for this video.

Making a video bigger

You can increase the size of your video to make it fill the screen like this:

Full screen view

Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown above.

When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.

Improving the quality of a video

To improve the quality of a video, first click on the Settings icon:

Settings icon

Make sure yoiu're playing your video so that the icons shown above appear, then click on this gear icon.

Choose to change the video quality:

Changing video quality

Click as above to change your video quality.

The higher the number you choose, the better will be your video quality (but the slower the connection speed):

Different speeds

Don't choose the HD option shown unless your connection speed is fast enough to support it!

Is your Wise Owl speaking too slowly (or too quickly)? You can also use the Settings menu above to change your playback speed.

This page has 3 threads

From:	Ryan Scanlon
When:	06 Mar 24 at 02:06

spinbutton.1 was highlighted. it oddly disappears when i use the snipit tool. does this mean the spinbutton is not defined?

Overwrite this with your post. You can paste in images (or click on the tool above to add them).

From:	Andrew G
When:	06 Mar 24 at 18:21

That is what it seems to suggest, yes. But it's odd that you say it works normally after clearing the error! What does the Properties window say its name is? You can right-click the spinbutton and choose Properties to see this:

From:	Ryan Scanlon
When:	07 Mar 24 at 01:19

Would it be easier to see the file instead of screen shots? i don't see an option to upload a file.

Reply

From:	Ryan Scanlon
When:	07 Mar 24 at 00:45

weird how it deselects.

From:	Andrew G
When:	08 Apr 24 at 07:31

Hi Ryan, no problem!

To check if you have code in the Workbook Open event you can double-click ThisWorkbook in the project explorer window:

Then check if you have this subroutine:

It seems unlikely that you'll have this but I was trying to eliminate every possibility!

From:	Ryan Scanlon
When:	07 Apr 24 at 19:08

Hi Andrew, i have been busy lately, sorry for the late reply.

i first see the run-time error when is open the file.

Do you have any code in the Workbook Open event? - i don't know, were would i see if i did?

an finally i have tried to copy into a new workbook but encountered the same result.

and it will be very troublesome to roll back to an earlier version cause i just purchased excel to get the developer options in Jan 2024. ill have to find it online some where

From:	Andrew G
When:	20 Mar 24 at 07:56

Hi Ryan!

When do you first see the run-time error? Is it as soon as you open the file, or when you click a button for the first time? Do you have any code in the Workbook Open event?

Other than rolling back the installation I might first try copying the worksheet into a new workbook and check if you still encounter the same problem.

P.S. If it's any consolation I don't know how to reply in order either - I think the problem is that there's a limit to the number of nested comments. I'll have a word with the person responsible!

From:	Ryan Scanlon
When:	20 Mar 24 at 01:21

firstly, sorry for not replying in order. i tried and i cant figure it out. Your right, it is a weird little bug. i have removed the "Option Explicit" and i get this run time error. when i press debug , you get the a highlighted spinbutton. then pressing the design mode the worksheet works fine. so it looks like i might need to roll back the program. unless there is another option? once again thanks for you time, you're a savior.

From:	Andrew G
When:	18 Mar 24 at 07:33

:D brilliant! What a weird little bug! The only related thing I could find was this https://answers.microsoft.com/en-us/msoffice/forum/all/variable-not-defined-errors-with-excel-update-2212/08b89bf8-7961-4a47-a814-141f577507cf

It seems that a January 2023 update to Excel introduced the behaviour you're describing. Rather than messing around trying to roll back updates you could just try removing any Option Explicit statements in your code and see if this stops the message appearing when you open the file.

From:	Ryan Scanlon
When:	16 Mar 24 at 17:37

hello, well i removed spinbutton1 and the reference to it in the code and the error moved to spinbutton2. lol thoughts?

From:	Andrew G
When:	08 Mar 24 at 08:52

Hi Ryan!

It's not obvious what the problem is. Agreed it would be easier to troubleshoot if we had the file but the reason we don't have an upload option is that if everyone sent us their files we'd never have time to do our jobs!

At this point I'd be tempted to delete the offending spin button as well as any code which references it. Then see if you receive the same error. If not, add a new spin button to replace the original.

Let us know how it goes!

Reply

Reply

Reply

From:	Ryan Scanlon
When:	23 Feb 24 at 02:07

I think i made the combobox more user friendly by expanding the list view and adding first letter command. i wish i could hover over the cell and mouse wheel to change. i dont think that is possible.

however my original question with the combobox selecting the appropriate row for the spin button still stands.

From:	Andrew G
When:	23 Feb 24 at 08:34

Yeah, the VBA controls are a bit clunky compared to their modern equivalents in other applications. Please see below for the combobox answer.

Reply

Reply

From:	Ryan Scanlon
When:	23 Feb 24 at 00:33

Hi Andrew, thank you again for your help. i was having an issue linking the combo box to the cell i wanted the spin button to control. Quick background on what im trying to accomplish. the form i am modifying is for my daughters volleyball team's coach. he logs stats and he said it takes him hours so i said i would help out.

originally i was going to use a combo box to control the row in which the spin button will link to, but i am going to change the process due to the combobox or list box doesn't scroll like i would like it to. it would take 3 clicks of the mouse to get to cell for the spinbutton(dropdown,slidebar,select). so i want to click the name to indicate the row that will be active. there will be about 25 spin buttons across to choose from on the form

.

From:	Andrew G
When:	23 Feb 24 at 08:02

Hi Ryan!

That makes much more sense now that I can see it!

So, if you want the combobox value to set the cell linked to the spinbutton (as I showed in the YouTube reply), the combobox needs to store the address of the cell, not just the name of the player whose name appears in the cell.

You could achieve this using a multi-column combobox which displays the player names and stores the cell references in a second, hidden column. The main problem with this keeping the combobox list up to date if the structure of your workbook changes.

Probably a better approach is to keep the combobox as it is (showing the list of player names) and use one of Excel's lookup and reference functions to locate the cell containing the player name. The MATCH function would be a good choice for this.

Here's a rough example of your sheet setup:

The code to set the linked cell for each spinbutton when you select a value from the combobox could look like this:

It works pretty well, here's the text if you just want to copy paste:

Private Sub ComboBox1_Change()

Dim NumberOfRowAboveFirstPlayer As Long

Dim NumberOfSelectedPlayer As Long

Dim RowNumberOfSelectedPlayer As Long

Dim NameOfSelectedPlayer As String

Dim RangeOfPlayerNames As Range

NumberOfRowAboveFirstPlayer = 13

NameOfSelectedPlayer = Sheet1.ComboBox1.Value

Set RangeOfPlayerNames = Sheet1.Range("C14:C17")

'This returns the relative number of the selected player:

'John = 1, Paul = 2, etc.

NumberOfSelectedPlayer = _

WorksheetFunction.Match(NameOfSelectedPlayer, RangeOfPlayerNames, 0)

RowNumberOfSelectedPlayer = NumberOfRowAboveFirstPlayer + NumberOfSelectedPlayer

SpinButton1.LinkedCell = "E" & RowNumberOfSelectedPlayer

SpinButton2.LinkedCell = "F" & RowNumberOfSelectedPlayer

SpinButton3.LinkedCell = "G" & RowNumberOfSelectedPlayer

End Sub

Hope it helps!

From:	Andrew G
When:	28 Feb 24 at 07:10

Great stuff, thanks for the update!

Reply

From:	Ryan Scanlon
When:	27 Feb 24 at 13:45

This is awesome. it works really well. i also created it two mores time to have combo boxes control the second and third set. the form is coming along and thank you. next i am building a macro to copy and paste the sheet, then rename the sheet to include an input value from the sheet. if i get stuck i will find the appropriate thread. thank again.

From:	Andrew G
When:	04 Mar 24 at 07:38

Hi Ryan!

It's not obvious from the screenshots why you're received a compile error. The combobox_change event shouldn't fire when you open the workbook (unless you have other code which changes the combobox value when the workbook opens?).

I would try compiling the project by choosing Debug | Compile VBAProject from the VBE menu. If you do have any non-declared variables this should highlight the offending item and hopefully provide a clue as to what's going on!

From:	Ryan Scanlon
When:	03 Mar 24 at 02:33

Hi Andrew, i have encountered a small bug. when i reopen the file the VBA editor is open and a pop up box says " Compile error: Variable not defined". i press ok then click the design mode button and everything work as normal. i have looked over the code but i may be missing something.

Reply

Reply

Reply

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Visual C# training

Excel training courses

Power Automate training

Power BI / DAX training

Python training courses

SQL training courses

VBA (macros) training

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

Terms & Conditions

End of small page here

Please be aware that our website uses cookies!

I'm OK with this Tell me more ...