SpinButton Change Selection

[Marlon]

Hi,

I would like to create a SpinButton with which I change
from one cell to the next one (up and down).

I tried this:

Private Sub SpinButton1_Change()
With SpinButton1
.Min = 1
.Max = 10
Cells(.Value, 1).Select
Cells(.Value, 1) = .Value
End With
End Sub

The problem is that the "Cells(.Value, 1).Select" doesn't
work properly. But the values in the cells are like
expected "Cells(.Value, 1) = .Value". If you use the code
above you can see what I mean.

Many thanks for help.
Best regards,
Marlon

 

[Toppers]

Marllon,
Unless I misunderstand what you are trying to do, it looks OK
to me in that it puts 1 in A1, 2 in A2 , .. 10 in A10 with the Spinbutton on
a Userform.. And it does this in either direction. Equally, if I change the
column, the value is put in the required cell.


[MS office 2003]

 

[Marlon]

Hi Toppers,

Sorry, I forgot to mention that I'm using a Spinbutton
from the Toolbox. The Spinbutton is directly located on a
Worksheet and not on a UserForm.

The Values are correct - I know. But in fact I don't need
them. It was just a test. I would like the cursor to
switch to the next cell. If the Spinbutton is arranged on
a Worksheet the behavior is very strange.

Best regards,
Marlon

-----Original Message-----
Marllon,
Unless I misunderstand what you are trying to do, it looks OK
to me in that it puts 1 in A1, 2 in A2 , .. 10 in A10 with the Spinbutton on
a Userform.. And it does this in either direction. Equally, if I change the
column, the value is put in the required cell.


[MS office 2003]

Hi,

I would like to create a SpinButton with which I change
from one cell to the next one (up and down).

I tried this:

Private Sub SpinButton1_Change()
With SpinButton1
.Min = 1
.Max = 10
Cells(.Value, 1).Select
Cells(.Value, 1) = .Value
End With
End Sub

The problem is that the "Cells(.Value, 1).Select" doesn't
work properly. But the values in the cells are like
expected "Cells(.Value, 1) = .Value". If you use the code
above you can see what I mean.

Many thanks for help.
Best regards,
Marlon

.

 

[Toppers]

Marlon,
The following works .. with B1 as Cell Link.

Sub Spinner1_change()
Cells(Range("b1"), 1).Select
Cells(Range("b1"), 1) = Range("b1")
End Sub

 

[Marlon]

Hi Toppers,

that doesn't help me either.

Meanwhile I tried something this:

Private Sub SpinButton1_SpinDown()
ActiveCell.Offset(1, 0).Activate
End Sub

Private Sub SpinButton1_SpinUp()
ActiveCell.Offset(-1, 0).Activate
End Sub

But it has the same effect as the first code. When I
press one of the buttons of the Spinbutton the Cursor
changes to the next cell (that's OK). But if I press the
button again the Cursor/Selection disappears. If I press
the button again the Cursor reappears in the next cell
and so on.

Best regards,
Marlon

 

[Marlon]

Hi Toppers,

the event "GotFocus" makes nearly what I want:

Private Sub SpinButton1_GotFocus()
With SpinButton1
.Min = 1
.Max = 10
Cells(.Value, 1).Select
End With
End Sub

But if I press the Down-Button the Cursors goes upwards
and if I press the Up-Button the Cursor goes down.
Strange. - Any idea why?

Best regards,
Marlon

 

[TroyW]

Marlon,

The difficulty you are observing is due to the SpinButton control taking
focus from the worksheet (you'll notice black rectangles appear around the
SpinButton arrows). The key step is forcing focus back onto the worksheet to
make the cursor reappear.

FYI, the CommandButton control has a property named "TakeFocusOnClick" which
can be set to False to deal with this type of problem. Unfortunately, the
SpinButton control doesn't have this property, so you have to kludge it with
the ActiveCell.Activate command in the GotFocus event of the SpinButton.

Troy


Private Sub SpinButton1_GotFocus()
'Remove the focus from the SpinButton.
ActiveCell.Activate
End Sub

Private Sub SpinButton1_SpinDown()
If ActiveCell.Row < ActiveSheet.Rows.Count Then
ActiveCell.Offset(1, 0).Activate
End If
End Sub

Private Sub SpinButton1_SpinUp()
If ActiveCell.Row > 1 Then
ActiveCell.Offset(-1, 0).Activate
End If
End Sub

 

[Marlon]

Hi Troy,

many thanks. Your explanation helps a lot to understand
the whole story. It works properly for me now.

Best regards,
Marlon

 

[TroyW]

Thanks for the reply. Cheers.

Troy

Hi Troy,

many thanks. Your explanation helps a lot to understand
the whole story. It works properly for me now.

Best regards,
Marlon