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.
Shirley MoremanDirectorAsked:
Who is Participating?
 
Roy CoxGroup Finance ManagerCommented:
Are you sure an error isn't occurring before it is switched back on.

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.

There are better ways round this
0
 
Roy CoxGroup Finance ManagerCommented:
Difficult to comment without seeing the workbook and/or code
0
 
Shirley MoremanDirectorAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Roy CoxGroup Finance ManagerCommented:
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?
0
 
Shirley MoremanDirectorAuthor Commented:
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.
0
 
Shirley MoremanDirectorAuthor Commented:
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!
0
 
Roy CoxGroup Finance ManagerCommented:
The default for cells is Locked. Is the code unprotecting the worksheet? If so then new cells added will be Locked.
0
 
Shirley MoremanDirectorAuthor Commented:
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.
0
 
Roy CoxGroup Finance ManagerCommented:
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.
0
 
Shirley MoremanDirectorAuthor Commented:
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!
0
 
Shirley MoremanDirectorAuthor Commented:
Thanks for your assistance Roy.  It seems that the screen updating was causing the issue.  All working now.
0
 
Roy CoxGroup Finance ManagerCommented:
It was probably because an error was occurring and screenupdating was not being restored. That's the importance of error handlers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.