Help with Spin Button, Scroll Bar and activiating a macro

[Dedes]

Hi,

I never used Spin Buttons or Scroll Bars so I really not sure if they are capable of doing what I need.

I have a range of values from cell A1 to Cell A20 like you can see below

<TABLE dir=ltr cellSpacing=0 cellPadding=2 width=96 border=0><TBODY><TR><TD width="29%" height=19>
</TD><TD width="71%" bgColor=#ccccff height=19>

A​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

1​

​

​

​

​

</TD><TD width="71%" height=19>

90​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

2​

​

​

​

​

</TD><TD width="71%" height=19>

85​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

3​

​

​

​

​

</TD><TD width="71%" height=19>

83​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

4​

​

​

​

​

</TD><TD width="71%" height=19>

78​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

5​

​

​

​

​

</TD><TD width="71%" height=19>

64​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

6​

​

​

​

​

</TD><TD width="71%" height=19>

60​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

7​

​

​

​

​

</TD><TD width="71%" height=19>

55​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

8​

​

​

​

​

</TD><TD width="71%" height=19>

51​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

9​

​

​

​

​

</TD><TD width="71%" height=19>

48​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

10​

​

​

​

​

</TD><TD width="71%" height=19>

37​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

11​

​

​

​

​

</TD><TD width="71%" height=19>

35​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

12​

​

​

​

​

</TD><TD width="71%" height=19>

30​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

13​

​

​

​

​

</TD><TD width="71%" height=19>

22​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

14​

​

​

​

​

</TD><TD width="71%" height=19>

17​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

15​

​

​

​

​

</TD><TD width="71%" height=19>

13​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

16​

​

​

​

​

</TD><TD width="71%" height=19>

11​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

17​

​

​

​

​

</TD><TD width="71%" height=19>

9​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

18​

​

​

​

​

</TD><TD width="71%" height=19>

7​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

19​

​

​

​

​

</TD><TD width="71%" height=19>

2​

​

​

​

​

</TD></TR><TR><TD width="29%" bgColor=#ccccff height=19>

20​

​

​

​

​

</TD><TD width="71%" height=19>

1​

​

​

​

​

</TD></TR></TBODY></TABLE>


Then on Cell B2 I have one of the values from that list. What I need is that when I click on the up/down arrow the value on cell B2 changes to the next up/down value. Is this possible?

Then, after each change is made I need a Macro called "calculate" to run.

Can you help me please?

Thanks

Dedes

 

[MickG]

Try this:-
Not a good idea to call a sub "Calculate" !!

Option Explicit
Dim Rw As Long
Private Sub SpinButton1_SpinDown()
Rw = IIf(Rw = 0, 1, Rw - 1)
If Rw > 0 Then
Range("B1") = Range("A" & Rw)
Call Cal_culate
End If
End Sub
Private Sub SpinButton1_SpinUp()
Rw = Rw + 1
If Rw > 0 Then
Range("B1") = Range("A" & Rw)
Call Cal_culate
End If
End Sub
Sub Cal_culate()
MsgBox "Cal_culate has run"
'Add code here !!!!
End Sub

 

[Dedes]

Try this:-
Not a good idea to call a sub "Calculate" !!

Option Explicit
Dim Rw As Long
Private Sub SpinButton1_SpinDown()
Rw = IIf(Rw = 0, 1, Rw - 1)
If Rw > 0 Then
Range("B1") = Range("A" & Rw)
Call Cal_culate
End If
End Sub
Private Sub SpinButton1_SpinUp()
Rw = Rw + 1
If Rw > 0 Then
Range("B1") = Range("A" & Rw)
Call Cal_culate
End If
End Sub
Sub Cal_culate()
MsgBox "Cal_culate has run"
'Add code here !!!!
End Sub

HI MickG


Can you explain this part of the code please

Rw = IIf(Rw = 0, 1, Rw - 1)

And why calculate is not a good name for a macro?

thanks
Dedes

 

[MickG]

It just means if Rw = 0 then rw = 1 else rw = rw -1.
It was just a method to ensure "rw" did not go below 0, when an error occurred.
I think it could generally be improved on , but it seemed to work OK.
Using "Calculate " seems like getting close to a Reserved name which could cause problems with your code running , so its best to keep clear of any name that is generally used by excel for other things.
If I try to run a sub called "Calculate" Either my code won't run or I get an error.

 

[Dedes]

It just means if Rw = 0 then rw = 1 else rw = rw -1.
It was just a method to ensure "rw" did not go below 0, when an error occurred.
I think it could generally be improved on , but it seemed to work OK.
Using "Calculate " seems like getting close to a Reserved name which could cause problems with your code running , so its best to keep clear of any name that is generally used by excel for other things.
If I try to run a sub called "Calculate" Either my code won't run or I get an error.

Thanks MickG

Works perfectly!