Styjun

What is a "shilicashe?"

How were Martello towers supposed to work?

Print the last, middle and first character of your code

How can I get a player to accept that they should stop trying to pull stunts without thinking them through first?

Why would non-kinetic weapons be used for orbital bombardment?

How to deal with moral/legal subjects in writing?

How can I truly shut down ssh server?

Would a non-attacking Barbarian's rage end the same turn he started it?

Why didn't Thanos kill all the Dwarves on Nidavellir?

OR-backed serious games

Sharing shapefile collection

Shortest hex dumping program

Astronaut distance from Earth?

Terry Pratchett book with a lawyer dragon and sheep

What is this little owl-like bird?

Why do people keep referring to Leia as Princess Leia, even after the destruction of Alderaan?

How do native German speakers usually express skepticism (using even) about a premise?

Is anyone advocating the promotion of homosexuality in UK schools?

Does throwing a penny at a train stop the train?

Fast validation of time windows in a routing problem

Optimization terminology: "Exact" v. "Approximate"

Salt, pepper, herbs and spices

What does the phrase "head down the rat's hole" mean here?

How to trigger Authentification of Named Credential created via Apex

I try to create a Spin Button through VBA in Excel, but it gives me an “automation error, catastrophic failure”?

Excel VBA Check if directory exists errorVB - Excel checking previous value give an errorCreating multiple buttons in ExcelCell values do not update after button click in Excel using VBA where SQL query is writencreate a excel search engine using vba and filtersAutomation Error - Catastrophic Failure EXCEL VBADelete rows based on FALSE value in Excel VBA (Runtime error 91)Hide Selected Columns in Excel using (VBA) Click ButtonExcel VBA Looping through activeX option buttonsEXCEL - VBA recorded macro giving runtime error “1004”

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

4

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)
 spinButton.Object.Min = 1
 spinButton.Object.Max = 100
 spinButton.LinkedCell = "B2"
End Sub

My goal with the VBA code above is to create a simple Spin Button, which changes the value of cell "B2" by 1.

When I run the code as a macro, it gives me this error warning:

which they write about on the MS website here.

The thing is, my code works. As in, it creates a spin button at the right location, adds the correct properties and changes the value of the B2 cell when I click the spin button.

I'm using a completely new file with nothing in it. No other macros/modules and a completely empty spreadsheet. I use my VBA code in the "ThisWorkbook" if it makes any differences:

excel vba

share|improve this question

edited Mar 26 at 2:30

Mathieu Guindon

48.2k88 gold badges7171 silver badges164164 bronze badges

asked Mar 26 at 1:36

WoeIsWoeIs

53322 silver badges1111 bronze badges

add a comment | 

4

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)
 spinButton.Object.Min = 1
 spinButton.Object.Max = 100
 spinButton.LinkedCell = "B2"
End Sub

My goal with the VBA code above is to create a simple Spin Button, which changes the value of cell "B2" by 1.

When I run the code as a macro, it gives me this error warning:

which they write about on the MS website here.

The thing is, my code works. As in, it creates a spin button at the right location, adds the correct properties and changes the value of the B2 cell when I click the spin button.

I'm using a completely new file with nothing in it. No other macros/modules and a completely empty spreadsheet. I use my VBA code in the "ThisWorkbook" if it makes any differences:

excel vba

share|improve this question

edited Mar 26 at 2:30

Mathieu Guindon

48.2k88 gold badges7171 silver badges164164 bronze badges

asked Mar 26 at 1:36

WoeIsWoeIs

53322 silver badges1111 bronze badges

add a comment | 

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)
 spinButton.Object.Min = 1
 spinButton.Object.Max = 100
 spinButton.LinkedCell = "B2"
End Sub

My goal with the VBA code above is to create a simple Spin Button, which changes the value of cell "B2" by 1.

When I run the code as a macro, it gives me this error warning:

which they write about on the MS website here.

The thing is, my code works. As in, it creates a spin button at the right location, adds the correct properties and changes the value of the B2 cell when I click the spin button.

I'm using a completely new file with nothing in it. No other macros/modules and a completely empty spreadsheet. I use my VBA code in the "ThisWorkbook" if it makes any differences:

excel vba

share|improve this question

edited Mar 26 at 2:30

Mathieu Guindon

48.2k88 gold badges7171 silver badges164164 bronze badges

asked Mar 26 at 1:36

WoeIsWoeIs

53322 silver badges1111 bronze badges

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)
 spinButton.Object.Min = 1
 spinButton.Object.Max = 100
 spinButton.LinkedCell = "B2"
End Sub

share|improve this question

edited Mar 26 at 2:30

Mathieu Guindon

48.2k88 gold badges7171 silver badges164164 bronze badges

asked Mar 26 at 1:36

WoeIsWoeIs

53322 silver badges1111 bronze badges

share|improve this question

edited Mar 26 at 2:30

Mathieu Guindon

48.2k88 gold badges7171 silver badges164164 bronze badges

asked Mar 26 at 1:36

WoeIsWoeIs

53322 silver badges1111 bronze badges

edited Mar 26 at 2:30

Mathieu Guindon

48.2k88 gold badges7171 silver badges164164 bronze badges

edited Mar 26 at 2:30

Mathieu Guindon

48.2k88 gold badges7171 silver badges164164 bronze badges

asked Mar 26 at 1:36

WoeIsWoeIs

53322 silver badges1111 bronze badges

asked Mar 26 at 1:36

WoeIsWoeIs

53322 silver badges1111 bronze badges

2 Answers
2

active

oldest

votes

3

spinButton.Object.Min = 1 
spinButton.Object.Max = 100
spinButton.LinkedCell = "B2"

You are getting that error because you are setting the minimum value as 1 beforehand and then linking to a cell with no value. If you do the reverse, then it will work

spinButton.LinkedCell = "B2"

spinButton.Object.Min = 1
spinButton.Object.Max = 100

share|improve this answer

answered Mar 26 at 3:28

Siddharth RoutSiddharth Rout

120k1414 gold badges162162 silver badges218218 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much.
  
  – WoeIs
  Mar 26 at 17:19
  

  
  
  
  

add a comment | 

2

This worked for me with two slight changes:

1. Insert this code in a regular module, not the ThisWorkbook module.




2. Add a line that sets B2's value to 1 before linking the spin button to it (assuming that's what you want the starting value to be, but you could just as easily set it to 100 or anything in between).

---

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", _
 Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)

 spinButton.Object.Min = 1
 spinButton.Object.Max = 100

 ActiveSheet.Range("B2").Value = 1
 spinButton.LinkedCell = "B2"
End Sub

share|improve this answer

edited Mar 26 at 4:16

answered Mar 26 at 1:53

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, that is useful to know.
  
  – WoeIs
  Mar 26 at 17:19
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55348665%2fi-try-to-create-a-spin-button-through-vba-in-excel-but-it-gives-me-an-automati%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

spinButton.Object.Min = 1 
spinButton.Object.Max = 100
spinButton.LinkedCell = "B2"

You are getting that error because you are setting the minimum value as 1 beforehand and then linking to a cell with no value. If you do the reverse, then it will work

spinButton.LinkedCell = "B2"

spinButton.Object.Min = 1
spinButton.Object.Max = 100

share|improve this answer

answered Mar 26 at 3:28

Siddharth RoutSiddharth Rout

120k1414 gold badges162162 silver badges218218 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much.
  
  – WoeIs
  Mar 26 at 17:19
  

  
  
  
  

add a comment | 

3

spinButton.Object.Min = 1 
spinButton.Object.Max = 100
spinButton.LinkedCell = "B2"

You are getting that error because you are setting the minimum value as 1 beforehand and then linking to a cell with no value. If you do the reverse, then it will work

spinButton.LinkedCell = "B2"

spinButton.Object.Min = 1
spinButton.Object.Max = 100

share|improve this answer

answered Mar 26 at 3:28

Siddharth RoutSiddharth Rout

120k1414 gold badges162162 silver badges218218 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much.
  
  – WoeIs
  Mar 26 at 17:19
  

  
  
  
  

add a comment | 

spinButton.Object.Min = 1 
spinButton.Object.Max = 100
spinButton.LinkedCell = "B2"

You are getting that error because you are setting the minimum value as 1 beforehand and then linking to a cell with no value. If you do the reverse, then it will work

spinButton.LinkedCell = "B2"

spinButton.Object.Min = 1
spinButton.Object.Max = 100

share|improve this answer

answered Mar 26 at 3:28

Siddharth RoutSiddharth Rout

120k1414 gold badges162162 silver badges218218 bronze badges

spinButton.LinkedCell = "B2"

spinButton.Object.Min = 1
spinButton.Object.Max = 100

share|improve this answer

answered Mar 26 at 3:28

Siddharth RoutSiddharth Rout

120k1414 gold badges162162 silver badges218218 bronze badges

answered Mar 26 at 3:28

Siddharth RoutSiddharth Rout

120k1414 gold badges162162 silver badges218218 bronze badges

answered Mar 26 at 3:28

Siddharth RoutSiddharth Rout

120k1414 gold badges162162 silver badges218218 bronze badges

2

This worked for me with two slight changes:

1. Insert this code in a regular module, not the ThisWorkbook module.




2. Add a line that sets B2's value to 1 before linking the spin button to it (assuming that's what you want the starting value to be, but you could just as easily set it to 100 or anything in between).

---

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", _
 Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)

 spinButton.Object.Min = 1
 spinButton.Object.Max = 100

 ActiveSheet.Range("B2").Value = 1
 spinButton.LinkedCell = "B2"
End Sub

share|improve this answer

edited Mar 26 at 4:16

answered Mar 26 at 1:53

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, that is useful to know.
  
  – WoeIs
  Mar 26 at 17:19
  

  
  
  
  

add a comment | 

2

This worked for me with two slight changes:

1. Insert this code in a regular module, not the ThisWorkbook module.




2. Add a line that sets B2's value to 1 before linking the spin button to it (assuming that's what you want the starting value to be, but you could just as easily set it to 100 or anything in between).

---

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", _
 Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)

 spinButton.Object.Min = 1
 spinButton.Object.Max = 100

 ActiveSheet.Range("B2").Value = 1
 spinButton.LinkedCell = "B2"
End Sub

share|improve this answer

edited Mar 26 at 4:16

answered Mar 26 at 1:53

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, that is useful to know.
  
  – WoeIs
  Mar 26 at 17:19
  

  
  
  
  

add a comment | 

This worked for me with two slight changes:

1. Insert this code in a regular module, not the ThisWorkbook module.




2. Add a line that sets B2's value to 1 before linking the spin button to it (assuming that's what you want the starting value to be, but you could just as easily set it to 100 or anything in between).

---

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", _
 Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)

 spinButton.Object.Min = 1
 spinButton.Object.Max = 100

 ActiveSheet.Range("B2").Value = 1
 spinButton.LinkedCell = "B2"
End Sub

share|improve this answer

edited Mar 26 at 4:16

answered Mar 26 at 1:53

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

Sub Test()
 Dim spinButton As Object
 Set spinButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", _
 Link:=False, DisplayAsIcon:=False, Left:=276, Top:=58.5, Width:=12.75, Height:=25.5)

 spinButton.Object.Min = 1
 spinButton.Object.Max = 100

 ActiveSheet.Range("B2").Value = 1
 spinButton.LinkedCell = "B2"
End Sub

share|improve this answer

edited Mar 26 at 4:16

answered Mar 26 at 1:53

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

answered Mar 26 at 1:53

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges

answered Mar 26 at 1:53

BigBenBigBen

8,56933 gold badges88 silver badges2121 bronze badges