Spinner, worksheet focus problem

[Ed]

I'm new to working with Controls. I put a Spinner control from the Control
Toolbox on my worksheet. The Spinner action and the code work fine (the
code copies a range from Sheet3 and pastes it into Sheet1 which has the
Spinner). But it seems like after clicking the Spinner and running the
code, the focus will be alternately on the sheet (application title bar with
color, active cell has "selected" border), then on the spinner (title bar
grayed out, no active cell, dotted line around Spinner control) - back and
forth, every other time. The last lines of code are
wks.Range("A1").Select
wks.Activate
hoping to force the focus back to the worksheet, but it's not working. Is
there a property I have set incorrectly?

Ed

 

[Dave Peterson]

Untested...

wks.select
activecell.activate
'or
wks.Range("A1").Select

It looked like you had them in the wrong order if wks wasn't the activesheet.

 

[Ed]

Thanks for the reply, Dave. wks is the ActiveSheet, the sheet with the
Spinner on it. The code finds a range on another sheet, copies it, and
pastes it into a range on wks. The other sheet is never activated or
brought to the front.

I did set a long delay, because I seemed to be skipping numbers rather than
just incrementing one at a time. I think default was 50 and I made it 500.
Solved the skipping numbers problem, but I wondered if that caused this
problem, or if they would be unrelated.

Ed

 

[Tom Ogilvy]

Private Sub SpinButton1_Change()
ActiveCell.Activate


End Sub


I wouldn't see focus as affecting the numbers. If you change the numbers in
an event, there is a possibility that you are triggering the event more
often than you suspect. You might put a

msgbox "In Spinbutton1_Change" (as an example)

in the event to help you discover if this is the case.

--
Regards,
Tom Ogilvy

Thanks for the reply, Dave. wks is the ActiveSheet, the sheet with the
Spinner on it. The code finds a range on another sheet, copies it, and
pastes it into a range on wks. The other sheet is never activated or
brought to the front.

I did set a long delay, because I seemed to be skipping numbers rather than
just incrementing one at a time. I think default was 50 and I made it 500.
Solved the skipping numbers problem, but I wondered if that caused this
problem, or if they would be unrelated.

Ed

Untested...

wks.select
activecell.activate
'or
wks.Range("A1").Select

It looked like you had them in the wrong order if wks wasn't the activesheet.

bar

 

[Ed]

I wouldn't see focus as affecting the numbers. If you change the numbers
in

an event, there is a possibility that you are triggering the event more
often than you suspect. You might put a

Sorry for not being clear, Tom. That's what I meant by "skipping" the
numbers - my delay was set too fast and I was holding the click a bit longer
than I thought I was, so I'd change three and four numbers at a time, rather
than just one by one. That's why I increased the delay time - to compensate
for my heavy finger.

As you and Dave have suggested, I'll try the ActiveCell.Activate (when I get
a chance to get back to this!). Thanks for the boost.
Ed

Private Sub SpinButton1_Change()
ActiveCell.Activate


End Sub


I wouldn't see focus as affecting the numbers. If you change the numbers in
an event, there is a possibility that you are triggering the event more
often than you suspect. You might put a

msgbox "In Spinbutton1_Change" (as an example)

in the event to help you discover if this is the case.