• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform Not Releasing Worksheet

Linda Campbell

New Member
Hi,

I would really appreciate some help with this problem that only seems to have developed since I upgraded from Excel 2010 to 2013.

I use a lot of UserForms to collect, verify and store data from the user. I then use the stored data to produce various reports for the user and Management.

I generally have a blank sheet selected as the background for the UserForm, to make it neat and also to prevent the user from being distracted. This sheet is selected as part of the initialization process for the form.

The reports are usually generated by code initiated by a click event on the UserForm. The report code runs, selects the relevant worksheet page (rather than the background page) and then unloads the UserForm so the user can then see and manipulate the report as required. I provide a button on the report page to enable the user to go back to data entry mode (i.e. it re-opens the UserForm). This is very simple code (i.e. frmFormName.show). In Excel 2010 this worked like a charm and I have implemented across many applications.

However, since I changed to Excel 2013 I have come across a problem with this process that I just can't solve:

When the UserForm unloads at the completion of running the code, it locks up the report sheet in some manner that prevents it from being edited (enter data, change background color, insert delete etc.). If another sheet is selected and then the report sheet is re-selected, the report sheet becomes editable again. This only happens if I run the UserForm from a button on the sheet. If I run the code to initialize the form (i.e. frmFormName.show) from within the vba editor the problem does not occur). If I include code in the worksheet.open event, the problem does not occur. It only happens when I open the form from a button on the sheet. This is a problem because I hide tabs from the users and the only way that they can get back to the UserForm is by accessing code from some sort of a form control or button.

I have attached a mock file of the issue, please excuse how ugly it is. Sheet1 is the background sheet, sheet2 is a mock up of the report. If you click "Call User Form", it will switch to sheet1 and produce the ugliest userform in the world. If you click the CommandButton it runs code to take you back to sheet2 (my pretend Report). It is then not possible to edit anything in the "report".

Anything that anyone can do to help me with this problem would be really, appreciated. I love this site and have learned an enormous amount from it, but this is the first time that I have not been able to find the answer already here. So I thought I might ask if someone could give me a hand.

Thanks in advance.
Linda
 

Attachments

  • test.xlsm
    18.4 KB · Views: 11
Linda

Firstly, Welcome to the Chandoo.org Forums

Change the CommandButton1_Click event as shown below
Code:
Private Sub CommandButton1_Click()
  UserForm1.Hide
  Sheet2.Activate
End Sub
 
Hui,
I can't believe it's so simple! (I actually doubted your brilliance and checked it about four times before I believed it :)). This may even resolve another problem that I have experienced since upgrading to 2013, but might take me a bit longer to get my head around it

Thank you so much,
Linda
 
Hui,
Thanks for your reply. That works great! I did reply yesterday, but can't find it here, I hope that you got it. (Actually I have just realised that I needed to log in when I replied, and I don't think I did that, so that is probably why I can't find it :( - my apologies, I really was thrilled to get your response)

Can I make the problem a little more complicated?

In a different part of the application, I need to make a new copy of a worksheet. I have always used "sheetname.copy" for this process. This would create a copy of the worksheet in a new workbook, I would use the unload command to close out the userform interface and then the user would be free to make whatever changes they liked to the copy of the worksheet and save it and close it when they were done (which would bring them back to the original file), they would click the button to return to data entry mode.

Once again in 2013, this does not work. The copy has the same problem as the report issue described above - it seems to be locked up some how. If the user types on the new workbook, the editing actually happens on the original workbook (which would be a disaster for my templates :)).

I thought that your solution (i.e. use "hide" instead of "unload") would work for this problem too. But I could not get it to work. I have manipulated my test file to include a copy command to demonstrate the issue.

If you could give me some advice about this part of the problem too, I would be very grateful.

Thanks again,
Linda
 

Attachments

  • test2.xlsm
    20.2 KB · Views: 8
Had same issue.
Looks to be solved when loading the userform as "Modeless"

Code:
UserForm.Show vbModeless

Works for me. (Excel 2013)
 
Back
Top