Weird behavior after runing UserForm

[mcomp72]

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.

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!

 

[mcomp72]

Here is a posting about the same issue:
https://stackoverflow.com/questions...es-data-entered-to-go-on-wrong-sheet#28818334

In the initial post, the person posted a link to a video showing exactly what is happening. It's worth a look if you are unfamiliar with this problem.

By the way, I'm using Excel 2016 on Windows 10 64-bit.

 

[Jaafar Tribak]

Here is a posting about the same issue:
https://stackoverflow.com/questions...es-data-entered-to-go-on-wrong-sheet#28818334

In the initial post, the person posted a link to a video showing exactly what is happening. It's worth a look if you are unfamiliar with this problem.

By the way, I'm using Excel 2016 on Windows 10 64-bit.

Can you upload a workbook example to a file sharing site (like Box.net or other site) so we can take a look.

 

[Fluff]

Did you try the file uploaded on that link?
If so did you have the same problem, as it worked fine for me.

 

[mcomp72]

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.

 

[Jaafar Tribak]

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.

Private Sub CommandButton1_Click()
    Unload UserForm1
    Application.OnTime Now, "SelectSheet1"
End Sub

 

[mcomp72]

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.

Private Sub SaveAndCloseButton_Click()

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

End Sub

And the sub it cannot run:

Sub ActivatePOLogSheet()

Sheets("Purchase Order Log").Activate

End Sub

 

[mcomp72]

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!

 

[Jaafar Tribak]

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