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
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