Unable to type into an unlocked cell after VBA completes
I am writing a tool for a client using Excel 2016. One part of the tool unhides a number of rows depending on the number of lines the user needs for data entry (based on a number they enter into a cell; they then click a button to unhide the rows).
The first data entry cell in the first of these rows is then selected from within the VBA. This works perfectly on my laptop and on some PC's. However, on some user's PC's, although the selected entry cell is not locked (my code never locks/unlocks these cells) the user cannot type into it... or rather they can type into it but nothing appears in the cell! Sometimes they can type into the formula bar instead of directly into the cell and other times pressing F4 twice seems to clear it.
I have tried putting an Application.Wait into my code to see if slowing it down by a second makes any difference, but this doesn't seem to help.
Does anyone have any thoughts on what might be causing this; it seems totally bizarre to me and I'm pretty sure that there is nothing in my code which is causing the problem.
The first data entry cell in the first of these rows is then selected from within the VBA. This works perfectly on my laptop and on some PC's. However, on some user's PC's, although the selected entry cell is not locked (my code never locks/unlocks these cells) the user cannot type into it... or rather they can type into it but nothing appears in the cell! Sometimes they can type into the formula bar instead of directly into the cell and other times pressing F4 twice seems to clear it.
I have tried putting an Application.Wait into my code to see if slowing it down by a second makes any difference, but this doesn't seem to help.
Does anyone have any thoughts on what might be causing this; it seems totally bizarre to me and I'm pretty sure that there is nothing in my code which is causing the problem.
Roy Cox
Difficult to comment without seeing the workbook and/or code
ASKER
Thanks for the quick reply Roy. I can't load the full workbook (client NDA) but I'll extract the code just in case you can see something I'm missing! It makes no sense to me that it works on my laptop all the time and on the users PC's some of the time. I'll load something up shortly.
I would never use that method of hiding/unhiding rows for data entry though.. You aren't switching screenupdating off in the code are you?
ASKER
Not ideal I know! But the client didn't want to see lots of blank rows so wanted to vary the number of rows in two sections depending on the numbers they enter at the top of the page.
Yes, I switch off screen updating at the beginning of the sub, but switch it back on again at the end.
Yes, I switch off screen updating at the beginning of the sub, but switch it back on again at the end.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Definitely no errors. I might try it without switching off the screen updating and see what happens.
I'd be interested to hear your thoughts on other ways of handling the variable number of rows. Originally I had a single row in each section and then inserted the appropriate number of rows. I thought that this might be part of the locking problem and, indeed, when I first switched to unhiding rows instead this seemed to to solve it, then suddenly it started happening again (on some PC's) even though I hadn't changed anything!
I'd be interested to hear your thoughts on other ways of handling the variable number of rows. Originally I had a single row in each section and then inserted the appropriate number of rows. I thought that this might be part of the locking problem and, indeed, when I first switched to unhiding rows instead this seemed to to solve it, then suddenly it started happening again (on some PC's) even though I hadn't changed anything!
The default for cells is Locked. Is the code unprotecting the worksheet? If so then new cells added will be Locked.
ASKER
The code does indeed unprotect then protect the worksheet. But I'm not adding any new cells and the newly visible cells (in what were the hidden rows) are not locked. This is why it is so mysterious; the cells are not locked but some users cannot type anything directly into the cell. They don't get the usual message to say they are trying to type into a protected cell.
I did try a version without switching off screen updating. This worked for one user but not another. I'm at the client site tomorrow so at least will be able to try things out a little more easily.
I did try a version without switching off screen updating. This worked for one user but not another. I'm at the client site tomorrow so at least will be able to try things out a little more easily.
If that fails then consider cleaning up a version for upload. I don't know whether this site has a confidential upload, it would be a good idea.
ASKER
Pleased to report that this is now working for all the users. I took out the code which switched off screen updating and moved the select of the first blank cell (ready for data entry) to further up my code (immediately after unhiding the rows) and this seems to have fixed it.
I use the switching off screen updating all the time and this is the first time I've known it to cause a problem, but hey ho, it works!
I use the switching off screen updating all the time and this is the first time I've known it to cause a problem, but hey ho, it works!
ASKER
Thanks for your assistance Roy. It seems that the screen updating was causing the issue. All working now.
It was probably because an error was occurring and screenupdating was not being restored. That's the importance of error handlers