Update Userform textboxes on the fly

**jaslake** · 05-10-2009, 09:42 PM

I've got a workbook (attached) with a UserForm that summarizes transactions entered in columns A and B based on the entry (color) in column A. If you click on the Button (Summary (Show/Hide)) it brings up the UserForm.

A couple of issues I've struggled with:
1. I'd like the UserForm to open when the workbook opens.
2. I'd like the UserForm to update as entries are made in Columns A and B when the user tabs out of column B
3. I'd like the UserForm to always stay open; the user cannot close it.

I've got the same data in cells G26:M34 but these cells don't "float" as the page moves down. This is the reason I've gone to a UserForm to accumulate the data.

I've spent several days trying various methods but cannot figure it out. Any ideas how I can accomplish this?

Thanks, John

**jaslake** · 05-11-2009, 12:42 PM

I've solved Issue 1; the UserForm now opens when the workbook opens.

Still have not resolved Issues 2 and 3.

Any Ideas?

John

**royUK** · 05-11-2009, 01:09 PM

You would probably need to unload the userform then re-load it usig the worksheet change event

Your table seems to show the values, why use the userform. You could set the Control Source of the relevant textbox to the cell in the table

**jaslake** · 05-11-2009, 05:49 PM

Thanks royUK.

About an hour ago, I implemented just what you suggessted and it does perform the task I was looking to do. However, it creates another problem. I'll explain below.

As for the three issues I originally discussed:

Issue 1 - the UserForm now opens when the workbook is opened.

Issue 2 - the UserForm now updates "on the fly" (unload/reload)

Issue 3 - I believe I can prevent the user from closing the UserForm by setting the "Enabled" property to "False". I'm not certain this is the solution yet but it shows promise.

Now, the new problem I've created for my self in solving Issue 2. When I reload the UserForm, the cursor "appears" to stay with the UserForm and I must left click the mouse to get focus back on column A. I've been researching the WEB for VBA code to send a mouse left click to the applicaton via VBA but have come up with nothing.

Does my explaination make any sense to you? Do you know of a way around this?

John

**jaslake** · 05-11-2009, 06:01 PM

I forgot to respond to your question. You asked why I'm using a UserForm when I already have a table with the data.

I originally had the table you referenced at the top of the sheet and I then locked the panes so the table was always in view. But, it simply took up too much room on the screen. And, if I moved it off to the side, it would scroll off the screen as data filled the sheet.

So, I looked at "floating Textboxes" but could not find anything that looked workable. I decided to try the UserForm approach. I could load it where I wanted it and it would stay there.

The approach seems to work, except when it doesn't.(smile)

John

**royUK** · 05-12-2009, 02:14 AM

Here's an example of a FloatingForm

This will prevent a user closing the form with the red X

Please Login or Register  to view this content.

**jaslake** · 05-12-2009, 12:06 PM

Thanks for that snippet. It works for me. I simply added a "Close" button to the UserForm. Now the User has the option of viewing or not viewing the Summary. Much more friendly.

All the issues I asked you about have been resolved.

Issue 1 - Open the UserForm when workbook opens has been resolved with the following code:

Please Login or Register  to view this content.

This "seems" to work; I'll need to do more extensive testing.

Issue 2 - UserForm now updates on the fly. I added this code to the worksheet change event:

Please Login or Register  to view this content.

Unload UserForm and UserForm.Show closes the UserForm then reinitializes the UserForm. The last three lines of the code sends a Mouse click to the application and takes the "cursor" focus off the UserForm and puts it back to the worksheet at the selected cell.

Issue 3 - Not allow the user to close the UserForm was resolved by the snippet you provided.

Please Login or Register  to view this content.

Issue 4 - The cursor "appeared" to stay focused on the UserForm after reload. That issue "appears" to have been resolved with this code

Please Login or Register  to view this content.

I gotta be honest with you, I have no idea why this fixes the problem or what it's doing.

I found this code on Mr. Excels Forum (from Gary E)

Please Login or Register  to view this content.

and adapted it to my needs.

Question: In my working career (I'm retired), I did a lot of work in Visual FoxPro. With the software came a manual that was about 1200 pages thick. It contained all the commands, functions and system variables of the language and examples of how and when to use them. Are you aware of something similar thats available for VBA?

I'll look at the "Floating TextBox" reference you provided. For now, I'm going to mark this thread solved.

Thanks for your help.

John

**royUK** · 05-13-2009, 03:09 AM

I don't know of such a manual, the VBA help files or the internet usually suffice.

AppActivate sets the focus to Excel

**jaslake** · 05-13-2009, 05:20 PM

Well, I found that this code to set focus (simulate a mouse click)

Please Login or Register  to view this content.

only works when it sees fit.

I discovered this code at http://www.ozgrid.com/forum/showthread.php?t=34692 and I believe it's just the ticket.

Please Login or Register  to view this content.

In a standard code module

Please Login or Register  to view this content.

This code shifts focus from the UserForm to the worksheet. I've been beating up on it all day and I've not been able to break it.

Thanks again for all your help.

John