PDA

View Full Version : [SOLVED] Initialize modeless FYI Userform but focus never leaves sheet



MrRhodes2004
12-13-2016, 02:30 PM
Is there a way for a userform to be completely modeless?

I have modeless forms that are set modeless in their properties, modal=false and then when called frmUserForm.show vbmodeless.

However, they first initialize based on change event. When a calculation is within a certain range the form appears to provide additional information. Though, when it appears it steals focus from the activesheet. The user has to actively reselect the cell.

For example if they enter a number in cell A5 which triggers the change event. The cursor drops to A6 as expected but then the userform is initialized. Instead of being able to enter data, the user has to reselect the next cell.

I have tried doing this with code. The cell that I say to select is selected but the Userform still has the focus and requires the user to re-click the cell.

Once the user re-selects the sheet, the focus remains on the sheet even if the information is updated on the userform.

I would like the userform to show and hide without the activesheet losing focus at all.

Thoughts?

SamT
12-13-2016, 03:35 PM
I would like the userform to show and hide without the activesheet losing focus at all.
Can't do that. But you might be able to fake it.

Declare a Project level Variable in ThisWorkbook

Public LastAddy As String

In the Worksheet_Change sub, set that variable

LastAddy = Target.Address

In the UserForm Initialize sub, after the UserForm.Show line, Activate the desired Range

.Show

DoEvents
Sheets("X").Range(LastAddy).Offset(1, 0).Activate
LastAddy = Null
End Sub

MrRhodes2004
12-13-2016, 03:36 PM
Some advice that I found was to try one of these methods:
ThisWorkbook.Application.Caption - gave a Compile Error: Invalid use of property
AppActivate ThisWorkbook.Application - Run-time error 5, Invalid procedure call or argument
AppActivate "Microsoft Excel" - Run-time error 5, Invalid procedure call or argument

SamT
12-13-2016, 03:50 PM
Where did you find those at?

What is multiposting? (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3)

MrRhodes2004
12-13-2016, 03:57 PM
Sam, thanks for the suggestions. I had done something similar, capture the last cell that was used and then return the one below afterward. It would select the proper cell that I wanted it to after the form was initialized but the focus would stay on the userform.

As for the found advice, I was googling as I do before posting hoping that someone else had the same problem. Similar situation came up and the advice was posted on another open forum. Though I read many forums, this is the only place that I have posted questions.

MrRhodes2004
12-13-2016, 04:08 PM
Where did you find those at?

What is multiposting? (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3)
The post that I found the additional advice was:
https://stackoverflow.com/questions/28042521/set-focus-back-to-the-application-window-after-showing-userform
It appears that it was originally posted in January 2015.

SamT
12-13-2016, 06:05 PM
I haven't duplicated the problem on my machine. the only strange thing I see is

I have modeless forms that are set modeless in their properties, modal=false and then when called frmUserForm.show vbmodeless.

That should not matter, but first, I would drop the vbModeless

If that didn't work, I would use frmUserForm.Load and use .Show at the bottom of the Initialize sub as I demonstrated above. That will usually cure any memory based problems.

SamT
12-13-2016, 06:47 PM
I did some research on what StackExchange said.

After Showing the UserForm, immediately
AppActivate MyCaption
Then activate your Cell.

Then in ThisWorkbook Add these statements as indicated

Const MyCaption As String = "SomeFancifulName"


Private Sub Workbook_Open()
Application.Caption = MyCaption
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Caption = Application.Name
End Sub

Note that I would also have, in MyPersonal.xlsm

Sub Aw_Nuts()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Caption = Application.Name

'And any other Property that your code might
'change before it broke (or "Breaked" if you wish).
End Sub

MrRhodes2004
12-14-2016, 02:41 AM
SamT,
Again Sir, you have been very helpful. The following has worked in my situation.
Const MyCaption As String = "Excel for the LCD" 'though I had to add this to the modifying module calling the userform
AppActivate MyCaption

I tested the vbmodeless and selecting of activecell after and they did not seem to help nor hinder the situation.

Because of you, I have added a little bit more knowledge to my very limited knowledge base. Again, thank you!

SamT
12-14-2016, 08:10 AM
:thumb