Excel lost focus after closing userform

[Rene M]

Hello,

I am dutch so forgive me if my Englisch is not 100%

This is an anoying problem I have for a time now, but can't figure out what causes it because it happens with every userform I use!

In the calling Module:

Application.ScreenUpdating = True
frmInstellingen.Show
ThisWorkbook.Activate 'added this to get back the focus, but for a second the previously used app is shown (i.e. Windows Explorer) en then Excel gets back the focus


Next the module for closing the userform by clicking a button

Private Sub KnopCancel_Click()

strHuidigeModule = "frmInstellingen" 'some variable for later use
strhuidigeSub = "KnopCancel_Click" 'some variable for later use
ThisWorkbook.Sheets("variabelen").Range("InstellingenKeuze") = "annuleer" 'some variable for later use

Unload me

End Sub

 

[Logit]

I'm curious about using ThisWorkbook.Activate. I've never had to use that when opening or closing a UserForm.

 

[Rene M]

I'm curious about using ThisWorkbook.Activate. I've never had to use that when opening or closing a UserForm.

Because Excel lost focus I have to put that code there to regain focus. I never used this before either.

 

[Rene M]

After testing it looks like I found the problem!

At the beginning of a submodule I run a second submodule to disable several events and also put the modulename and submodule in a variable for error handling.
If there is an error it puts the error in a 'log.txt' and resets the events.

After some testing it looks like 'Application.Interactive' makes Excel loos focus. After Disable the command the problem seems gone!
Is there an other way to disable keyboard and mouse input tot replace the application.interactive command?


THE (MAIN) SUBMODULE LOOKS LIKE THIS

Sub StartInstellingen()

strHuidigeModule = "Module_InstellingenNormaal"
strhuidigeSub = "StartInstellingen"

On Error GoTo ERR
Dim PrevEvents As Boolean, PrevCancel As Integer, PrevInteractive As Boolean, PrevScreenUpdate As Boolean, PrevCalc As Integer, PrevProtected As Boolean

'OPSLAAN EVENTS
PrevEvents = Application.EnableEvents
PrevCancel = Application.EnableCancelKey
PrevInteractive = Application.Interactive 'toetsenbord/muis
PrevScreenUpdate = Application.ScreenUpdating
PrevCalc = Application.Calculation

'SET EVENTS COMPLEET
Call SetEvents(False, xlDisabled, False, xlCalculationManual, False)


'START CODE SUBMODULE

.
.
EINDE:
'HERSTEL EVENTS
Call SetEvents(PrevEvents, PrevCancel, PrevInteractive, PrevCalc, PrevScreenUpdate)

Exit Sub

ERR:
Call ErrorHandling(strHuidigeModule, strhuidigeSub, ERR.Number, ERR.Description, False)
ERR.Clear
Call VulVariabelen

'ENABLE EVENTS
PrevEvents = True
PrevCancel = xlInterrupt
PrevInteractive = True
PrevCalc = xlCalculationAutomatic
PrevScreenUpdate = True

GoTo EINDE

End Sub

THE SUBMODULE TO DISABLE/ENABLE EVENTS LOOKS LIKE THIS

Sub SetEvents(strEnableEvents As Boolean, strCancel As Integer, strInteractive As Boolean, strCalculate As Integer, strScreenUpdate As Boolean)

Application.ScreenUpdating = False

If Application.EnableEvents <> strEnableEvents Then
Application.EnableEvents = strEnableEvents
End If

If Application.EnableCancelKey <> strCancel Then
Application.EnableCancelKey = strCancel
End If

If Application.Interactive <> strInteractive Then
Application.Interactive = strInteractive
End If

If Application.Calculation <> strCalculate Then
Application.Calculation = strCalculate
End If

If Application.ScreenUpdating <> strScreenUpdate Then
Application.ScreenUpdating = strScreenUpdate
End If

End Sub

 

[Logit]

You can turn off the keyboard with the following :

Sub KeyboardOff()
' Sets CTRL+D to activate KeyboardOn.
Application.OnKey "^d", "KeyboardOn"
Application.DataEntryMode = True
End Sub

' Macro to restore keyboard entry.
Sub KeyboardOn()
Application.DataEntryMode = False
End Sub

The few macros I've found to disable the mouse places the 'Wait' cursor on screen and completely shuts down any activity. I haven't yet located
a macro that just turns off the mouse but allows you to use the keyboard. You have to have some kind of control when the mouse is taken out
of action ...

 

[Rene M]

You can turn off the keyboard with the following :

Sub KeyboardOff()
' Sets CTRL+D to activate KeyboardOn.
Application.OnKey "^d", "KeyboardOn"
Application.DataEntryMode = True
End Sub

' Macro to restore keyboard entry.
Sub KeyboardOn()
Application.DataEntryMode = False
End Sub

The few macros I've found to disable the mouse places the 'Wait' cursor on screen and completely shuts down any activity. I haven't yet located
a macro that just turns off the mouse but allows you to use the keyboard. You have to have some kind of control when the mouse is taken out
of action ...

Thanks!

I wil replace 'Application.Interactive' with your code. And disabling mouse isn't neccesary so this will do.

 

[Rene M]

This doesn't do the thing form me. I get more errormessages.

This is what DataEntreyMode is for: _Application.DataEntryMode Property (Microsoft.Office.Interop.Excel)

 

[Logit]

I tried the two macros both in a regular module and also in the UserForm. The macros functioned in either location.

???