EXCEL -vb - Spin Button - Focus

[Janusz Dacxzko]

Excel 2000 (9.0.2720) vb-6.
We have a column of Cells and the SpinButton. Let say for this example:
column 1 row 1 cell have value "A"
column 1 row 2 cell have value "B"
column 1 row 3 cell have value "C"
Cell with value "A" is is highlited (has focus)
Now we press "SpinButton" down. We exchange values "A" and "B"
so we have B - row 1
A - row 2
C - no change
We want A in row 2 be in focus, so we activate Cells(1,2)in last statement of spin down subroutine.
Well - the cell is activated for a split of second and then SpinButton get a focus - taking focus off our cell !!!. Now
SpinButton is like two CommandButton but CommandButton have a property: TakeFocusOnClick - TRUE or FALSE but SpinButton don't.(In my opinion MS missed this - hmmmmm?)
So, SpinButton is geting focus after "spin down" event is already finished. Now Cell in row 2 (value A)is active but SpinButton has a focus. If we press "down" again it works good bcse SpinButton (having focus now) is not trying to get focus again. How to solve this ???.

[Mihail]

What you wish to solve?

[Janusz Dacxzko]

Well, Mihail
I'm loosing a focus from a cell that should have it - every second time when I press SpinButton. Would be solution - set Focus on SpinButton before pressing it, as I explained before when SpinButton has a focus it don't try get it back, so my cell properly maintain her focus for now, but next clic and SB takes focus again - Kapish ?

[Janusz Dacxzko]

Sorry, I'm wrong - It is not a solution to set focus on SB, you have to press it first then program knows that it was pressed, besides there is no instruction to set focus or activate SB ??!!

[Mihail]

A solution can be:

Option Explicit
 
Private Sub SpinButton1_GotFocus()
    ActiveCell.Activate
End Sub
 
Private Sub SpinButton1_SpinDown()
Dim R As Integer
    R = ActiveCell.Row + 1
    If R > 65535 Then R = R - 1 'last row in sheet
    Cells(R, 1).Activate
End Sub
 
Private Sub SpinButton1_SpinUp()
Dim R As Integer
    R = ActiveCell.Row - 1
    If R < 1 Then R = R + 1 'Firs row in sheet
    Cells(R, 1).Activate
End Sub

Si, kapish :))

[NeoPa]

I handle this irritation by selecting the worksheet that the cell is on. I've sometimes found that not to work though. Try selecting the current selection instead :

Call Selection.Select

[Janusz Dacxzko]

Micha,
I totaly overlooked "Object_GotFocus()". Of course your solution is perfect. Si,Si Kapish now.!
Many thanks
Janusz

[Janusz Dacxzko]

NeoPa - Thanks - I used Mihail solution.
Cheers