Weird behavior after runing UserForm

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
275
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
I have several UserForms in my Excel workbook. After running one of them, I have noticed some very strange behavior. I have seen others post about it on a different site, but the solution offered to them doesn't seem like the right solution for me.

Here's what is happening, in a nutshell:

- The user clicks a button on a page called "Purchase Order Log" that activates a UserForm. The Initialize code runs and fills in some text boxes with information if certain conditions are met. If they are not, the text boxes are left blank.

- When the user is finished entering information into the text boxes, they click a button called "Save & Close". Code is then executed to write the information in the text boxes to a sheet called "PO Info".

- A message box appears on the screen telling the user their information has been saved.

- The UserForm is unloaded.

- The sheet "Purchase Order Log" is then Activated.

AFTER all of this happens, the user types something into a cell on "Purchase Order Log." However, while it appears on the screen that they are doing this, the data they have typed ACTUALLY ends up going onto the "PO Info" sheet. (Side note: the scroll wheel on the mouse does not work at this time. It is only after switching to another sheet that the scroll wheel functions again.)

One solution that was offered on a thread about the same problem was to make the UserForm show "vbModeless". However, in my case, I do not want the user to be able to make changes to the sheets while the UserForm is visible, so this does not seem like the correct solution for me.

Here's the code that runs when the user presses the "Save & Close" button on the UserForm, in case it is helpful.

Code:
Private Sub SaveAndCloseButton_Click()

SaveInfoToPOInfoSheet
MsgBox "Information has been saved!"
Unload Me
Sheets("Purchase Order Log").Activate

End Sub

Can anyone suggest another way to fix this rather strange problem? I can't understand why the heck Excel would appear to allow the user to type in information to one sheet while it's actually being input into another!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Upvote 0
Did you try the file uploaded on that link?
If so did you have the same problem, as it worked fine for me.
 
Upvote 0
The other person who was having the same issue made a file specifically to show what the problem is. I've downloaded it and put it on my own DropBox. You can find it by clicking here. Click the big button, and then it will take you to a sheet where there is a big cell that you are supposed to try to change. For me, when I do that, it APPEARS as though I am changing that cell, but in actuality I am changing that cell on the OTHER SHEET.

Through the research I've done about this problem, it appears that it only happens in Excel 2013 & 2016. Fluff, you mentioned it worked fine for you. Did you try it in Excel 2013? (I notice your signature says you have both 2003 and 2013.) Jaafar, since you have v2010, I am guessing it will work fine for you.


 
Upvote 0
The other person who was having the same issue made a file specifically to show what the problem is. I've downloaded it and put it on my own DropBox. You can find it by clicking here. Click the big button, and then it will take you to a sheet where there is a big cell that you are supposed to try to change. For me, when I do that, it APPEARS as though I am changing that cell, but in actuality I am changing that cell on the OTHER SHEET.

Through the research I've done about this problem, it appears that it only happens in Excel 2013 & 2016. Fluff, you mentioned it worked fine for you. Did you try it in Excel 2013? (I notice your signature says you have both 2003 and 2013.) Jaafar, since you have v2010, I am guessing it will work fine for you.



The workbook worked fine for me . I didn't not experience the problem you described maybe because I use excel 2010 as you said.

Just an idea : Try delaying the call to the SelectSheet1 Macro as follows and see if it makes a diffference.

Code:
Private Sub CommandButton1_Click()
    Unload UserForm1
    Application.OnTime Now, "SelectSheet1"
End Sub
 
Upvote 0
Just an idea : Try delaying the call to the SelectSheet1 Macro as follows and see if it makes a diffference.

In the test example, that worked! However, in my own workbook, something seems to be wrong. I tried the same technique, and am getting an error message. It's saying it cannot run the macros 'ActivatePOLogSheet', which is triggered by the OnTime line of code. "The macro may not be available in this workbook or all macros may be disabled." I had tried using this command once before, by putting a 1 second delay on the launching of that sub. Same error that time, too. I have no idea what may be wrong here. Here's the code in the sub that calls that macro, and that macro itself.

Sub that calls the macro with the OnTime command.
Code:
Private Sub SaveAndCloseButton_Click()

SaveInfoToPOInfoSheet
MsgBox "Information has been saved!"
Unload Me
Application.OnTime Now, "ActivatePOLogSheet"

End Sub

And the sub it cannot run:
Code:
Sub ActivatePOLogSheet()

Sheets("Purchase Order Log").Activate

End Sub
 
Last edited:
Upvote 0
Just figured out why my sub wouldn't run. Turns out when you call a sub that way, it has to be in a MODULE. I had ActivatePOLogSheet' inside a Form. Once I moved it to a module, it worked.

Jaafar, thanks for that suggestion! So glad to have that annoying bug out of my macro project!
 
Upvote 0
Just figured out why my sub wouldn't run. Turns out when you call a sub that way, it has to be in a MODULE. I had ActivatePOLogSheet' inside a Form. Once I moved it to a module, it worked.

Jaafar, thanks for that suggestion! So glad to have that annoying bug out of my macro project!

Glad you got this worked out .. Using the Ontime trick was just a stab in the dark :)
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top