Note: An idea of how to fix this occured to me after I typed this up, so this issue is solved for me. I decided to post it anyway since it might be useful to someone else. The solution is at the bottom of this post.
Greetings,
This is what I think should be a simple problem but I can't seem to figure it out.
Basically I have an Excel VBA project with a spreadsheet dashboard. The dashboard has a few embedded buttons the user can activate. One button brings up a modeless user form which allows data to be entered. I want the form modeless so they can still access other spreadsheet which might contain data they need or if they just happen to be working on multiple projects, I don't want to force them to finish the form before switching to another task.
In order to prevent the user from accessing the dashboard while the form is active, I do this:
Then, when the form finishes:
This works quite well. The userform remains visible but the spreadsheet disappears until the form is dimissed.
The problem is, hiding the workbook in this manner seems to automatically send focus to another open workbook. Normally, that makes sense of course, because you don't want focus to remain on a non-visible workbook. I would, however, like focus to remain on the UserForm. But I can't figure out how to get focus back to the form.
Solution:
So the problem was that I was hiding the workbook in the UserForm_Activate() event. This event occurs after the form has appeared. So the form appears, then the workbook is hidden, which causes focus to shift to the next available workbook. However, the UserForm_Initialize() event happens before the UserForm actually appears. Hidding the workbook in the initialize event will still shift focus to the next available workbook, however, the UserForm appears after that event so focus will come back to the form when it appears.
New Code:
Greetings,
This is what I think should be a simple problem but I can't seem to figure it out.
Basically I have an Excel VBA project with a spreadsheet dashboard. The dashboard has a few embedded buttons the user can activate. One button brings up a modeless user form which allows data to be entered. I want the form modeless so they can still access other spreadsheet which might contain data they need or if they just happen to be working on multiple projects, I don't want to force them to finish the form before switching to another task.
In order to prevent the user from accessing the dashboard while the form is active, I do this:
Code:
Private Sub UserForm_Activate()
Set tWB = ThisWorkbook
Windows(tWB.Name).Visible = FalseThen, when the form finishes:
Code:
Private Sub UserForm_Terminate()
Windows(tWB.Name).Visible = TrueThis works quite well. The userform remains visible but the spreadsheet disappears until the form is dimissed.
The problem is, hiding the workbook in this manner seems to automatically send focus to another open workbook. Normally, that makes sense of course, because you don't want focus to remain on a non-visible workbook. I would, however, like focus to remain on the UserForm. But I can't figure out how to get focus back to the form.
Solution:
So the problem was that I was hiding the workbook in the UserForm_Activate() event. This event occurs after the form has appeared. So the form appears, then the workbook is hidden, which causes focus to shift to the next available workbook. However, the UserForm_Initialize() event happens before the UserForm actually appears. Hidding the workbook in the initialize event will still shift focus to the next available workbook, however, the UserForm appears after that event so focus will come back to the form when it appears.
New Code:
Code:
Private Sub UserForm_Initialize()
Set tWB = ThisWorkbook
Windows(tWB.Name).Visible = False
