Unable to edit sheet after hiding User Form

I'm creating a spreadsheet based heavily in user forms that will help create inventory items with small variances for colors for our company that I can then import into our software. I want to keep as much as possible hidden from the end user but it seems like the easiest way to allow them to edit a bill of materials after they enter it will be to grant access to the spreadsheet that it's saved on and then go back to the user form once edited. I'm basically figuring it all out as I go but this has me stumped.

I created a command button on the form that will hide the form and display the relevant worksheet. (code below)

Please Login or Register  to view this content.

Once the sheet is modified, there's a button on the sheet that will then reload the form. (code below)

Please Login or Register  to view this content.

This seems to work perfectly the first time. However, any subsequent times it does not allow me to change anything on the spreadsheet. I've messed around a little bit with the order of hiding and showing things but I'm guessing I'm just missing something important.

Any help or suggestions would be appreciated. Let me know if I need to show more of the code.

Thanks!

Here's the file with the application parent stuff optioned out.

Just add a component name/quantity and add to build then edit BOM. The first time it seems to work, trying multiple times after that you can't change anything on the worksheet.

This is a really intriguing problem. At first I thought there must be a Worksheet_Change event in TempBOM that was causing mischief, but there is not. Then I suspected a TempBOMBox_Change event being triggered, but not there either. I could not find any other _Change event that could be interfering.

I did confirm that if I typed anything on TempBOM it would not stick. The first strange thing was that when I opened a cell for edit, it turned dark gray. Then I noticed that the changes I typed were not sticking on TempBOM but they did show up on Front Page. And cells on Front Page are dark gray. It's as if I am trying to edit TempBOM but Excel is presenting cells from Front Page to edit. If that's really what's happening, it's an enormous Excel bug. Or a corruption in your file.

This is interesting so I will try to spend a little more time on it but all I have been able to do so far is to rule out the most likely causes.

Doh! I was hoping it was something obvious I was overlooking.

I noticed it was changing things on the Front Page as well but haven't been able to figure out why.

It's not clearing any content though. It seems to be holding a selection on another sheet which is preventing changes to the current "supposedly selected" cells... it makes no sense.

What version of Excel are you using? I can reproduce the problem on 2013 at the office but when I try on 2010 at home everything works perfectly.

2013 at the office and 2010 at home as well. I haven't worked on it from home for a while.

Originally Posted by Norie

That code is clearing content, and I can see it happening when I clicked Reload Form after I'd open/closed the form and entered/edited data multiple times with no problem/data loss.

By design, the only way to get to the reload form button currently is to click the x on the user form which in turn triggers the terminate event. It doesn't appear that it's triggering when it's not supposed to as the data that it's supposed to clear out on terminate is not being cleared at any other time that I have been able to reproduce.

If it is just a corruption caused by working on it in multiple versions of office, is there an easy way to copy the forms to a new file or will that potentially copy the corruption with it? I know the code should be safe to copy but I don't want to spend a significant amount of time working on rebuilding it to just end up copying over the corrupted item(s) and have the same issue...

I can no longer reproduce the problem on any platform.

If you continue to have the problem, I would try to clone your file. You can save code by going to the code development window and right-clicking on each module name, then selecting Export. This allows you to save the code for each module in a .bas file. You can also export then import the userforms. Then create a new blank workbook. In the code development window, Import each file that you just exported.

Then you need to make all your worksheets visible, and for each select the entire sheet, copy, and paste into a new sheet in the new workbook. Update the sheet names to match the original names. Double check that all your named ranges from LookupLists were also carried over.

I'm sorry that I don't have any other advice for you. If there is indeed a corruption this process is likely to clear it. However, be warned that this is a troubleshooting step and there is no guarantee this will solve it.