Focus back to active cell 1

[JoJ]

After clicking a cmd button, I cannot transfer focus back to the active cell in Excel. (I was hoping to eliminate one extra mouse click to get back to the cell I was already in.)

The button is on a modeless form. All controls on this form are not tab stops. The TakeFocusOnClick properties are also set to false.

Among many other suggestions, I tried
ActiveSheet.Range(ActiveCell.Address).Activate
in the cmd buttons click event. Even that didn't work.

..tsk

 

[Zathras]

It's tricky, but it can be done:

In the code page for "ThisWorkbook":
[blue]

Private Sub Workbook_Open()
  ExcelWindowHandle = GetForegroundWindow
End Sub

[/color]

In the code page for the form:
[blue]

Private Sub CommandButton1_Click()
  DoSomething
End Sub

[/color]

In a separate code module:
[blue]

Option Explicit
Declare Function GetForegroundWindow Lib "User32" () As Long
Declare Function SetForegroundWindow Lib "User32" (ByVal HWND As Long) As Long
Public ExcelWindowHandle As Long

Sub Test()
  UserForm1.Show vbModeless
End Sub

Sub DoSomething()

[green]

  ' Do whatever it is that needs doing when the buton is pushed
  ' Then pass control back to the workbook (active cell)
  '
  '

[/color]

  SetForegroundWindow ExcelWindowHandle
End Sub

[/color]

 

[JoJ]

Thanks.

Using the User32 API didn't even cross my mind.

I gave you a star.