Closed Thread
Results 1 to 11 of 11

Unlocked cells becoming locked after using activex combo box

  1. #1
    Registered User
    Join Date
    08-12-2014
    Location
    Carson City, NV
    MS-Off Ver
    2013
    Posts
    8

    Unlocked cells becoming locked after using activex combo box

    I am having a problem with an Excel 2013 application I have been using for months. It started on my desktop after what I believe was the last Excel update. That update also caused my activex controls to stop functioning. (I found that deleting the .exd files solved that problem, but shortly thereafter I began having this latest issue. Now when I activate an activex combo box (with list fill range from another open workbook - not sure that matters at all) the combo box works fine. It fills with the list, I make my choices and everything is fine, EXCEPT after making that selection all the cells that were unprotected now act as if they are protected. I cannot enter anything UNTIL I run a macro then the unprotected cells work again. This happened on my desktop first. Up until today, I could copy that same file to my laptop with the same version of Excel and everything worked fine on the laptop. That is until today. This morning when I shut down my laptop, Windows (8.1) wanted to install an update. After it installed, I thought I would check to see if my excel app would still work. It does not - same issue as the laptop. Anyone else have a similar issue or know the fix? I'm desperate to get this app working again.

  2. #2
    Registered User
    Join Date
    08-12-2014
    Location
    Carson City, NV
    MS-Off Ver
    2013
    Posts
    8

    Re: Unlocked cells becoming locked after using activex combo box

    Okay, I have been testing for the last hour on another computer and I not sure if it has anything to do with an update or not. I went to another computer and it seemed to be working fine for awhile and then I encountered the same issue. I thought I had it narrowed down to the following code as I posted in an earlier post and now I am back to it and pretty sure it has something to do with it. It happens every time I run the following macro. (I have a number of identical macros to hide a pair of rows on a sheet - the user clicks a button and it hides the 2 rows selected. Another button shows the next 2 rows.) When I run it, I can enter a value in a an unprotected cell fine UNTIL I use a activex combo box on that row. After making a selection from the combo box I cannot make entries into those cells again until I run the macro code again (either this code or the opposite code that shows a row). This code is attached to the worksheet. I'm wondering if I have the correct parameters on the ActiveSheet.Protect line??

    PHP Code: 
    Private Sub HideButtonAB_Click()
    Application.ScreenUpdating False
    If Range("C22") = 0 Then
    Sheets
    ("FLEX BID").Select
        ActiveSheet
    .Unprotect

    Rows
    ("22:23").Select
        Selection
    .EntireRow.Hidden True
        Sheets
    ("FLEX BID").Select
        ActiveSheet
    .Protect DrawingObjects:=TrueContents:=TrueScenarios:=True
        Application
    .ScreenUpdating True
    ElseIf Range("C22") > 0 Then
    MsgBox 
    "Must be blank row in order to hide row"

    End If
    End Sub 

  3. #3
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    348

    Re: Unlocked cells becoming locked after using activex combo box

    Please Login or Register  to view this content.
    In the end it will protect your worksheet unless cell 22 value is higher than 0.
    Last edited by Blokeman; 01-25-2015 at 07:31 PM.
    Please click the * Add Reputation if this helps
    If solved remember to mark Thread as solved

    "I'm glad to help and this is not meant to sound smart, but either you have super-human vision to see all those controls cleared one by one with the code I posted, or your computer is really slow."

  4. #4
    Registered User
    Join Date
    08-12-2014
    Location
    Carson City, NV
    MS-Off Ver
    2013
    Posts
    8

    Re: Unlocked cells becoming locked after using activex combo box

    Thanks for the reply! Forgive me, but I am not seeing any difference in your code and mine other than the comment and "Private" and "End Sub" are missing?? This code does unprotect the worksheet , hide the rows and then protect the worksheet unless the cell 22 value is higher than 0. The problem is unprotected cells are behaving as if they are protected until I run this code again.
    Last edited by kfalls; 01-25-2015 at 08:01 PM. Reason: clarify response

  5. #5
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    348

    Re: Unlocked cells becoming locked after using activex combo box

    I've only reply to your second post about the code you post. If you can upload the file then maybe an excel guro can help you.

  6. #6
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Unlocked cells becoming locked after using activex combo box

    kfalls,

    My speculation is that your problem may be related to the 'hidden rows'. As a test, comment out all the code that hides rows that contain Active X controls. My guess is that your code will work OK.

    Unfortunately, if that is the case, the workaround that I know of, can be tedious and time consuming:
    a. On Workbook Close, save the STATE of hidden rows and other items that may become corrupt.
    b. On Workbook Open, restore the STATE that you saved.

    Two simple ideas, but lots of hard, painful, frustrating work.

    Other workarounds which may be inferior include:
    a. Replacing Active X Controls with Forms Controls
    b. Not hiding rows
    c. Moving Active X controls away from rows that are hidden.

    Lewis

  7. #7
    Registered User
    Join Date
    08-12-2014
    Location
    Carson City, NV
    MS-Off Ver
    2013
    Posts
    8

    Re: Unlocked cells becoming locked after using activex combo box

    Lewis,

    Thanks so much for the reply! As I stated in my post, I had tried deleting the .exd files and it solved the original problem of the active x controls not working at all after a Microsoft security update. I tried it again when this issue began to no avail. I was made aware of a thread Microsoft has that contains a FixIt app the clears out all the .exd files. I JUST finished running it and it is working again. (At least for now). I am praying that truly solved the issue. At least it works for now! The thread is at: https://support.microsoft.com/kb/3025036

    Thanks again!

    Alan
    Last edited by kfalls; 01-27-2015 at 11:03 PM. Reason: Corrected link

  8. #8
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Unlocked cells becoming locked after using activex combo box

    Alan,

    Thanks for the feedback. I didn't realize the Dec 14th update problem, could cause partial problems.
    I'm glad that you got it working.

    Lewis

    P.S. Please update the link in the previous post. It's not to the fixit, but to this thread.

  9. #9
    Registered User
    Join Date
    08-12-2014
    Location
    Carson City, NV
    MS-Off Ver
    2013
    Posts
    8

    Re: Unlocked cells becoming locked after using activex combo box

    Oops! Sorry about that. I corrected above post and it is also shown here.

    https://support.microsoft.com/kb/3025036
    Last edited by kfalls; 01-27-2015 at 11:05 PM.

  10. #10
    Registered User
    Join Date
    11-29-2012
    Location
    Warsaw, poland
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Unlocked cells becoming locked after using activex combo box

    All,

    I have actually quite similar problem. Perhaps you might know the solution.

    I have prepared a test worksheet which can be downloaded from here:

    https://www.dropbox.com/sh/ph5lrj1fy...gXY0X10-a?dl=0

    How the thing work:
    - it hides the formula bar in Workbook_Open
    - you can extend the number of unhidden rows either by clicking the spin button or by entering the new value (1 to 100) in the cell A1.
    - quite often cells are locked for editing after clicking on spinbutton

    I noticed 1 solution: not to hide formula bar. But it is not the one I would like to have!

    Do you have any idea how to to work it around?

    Thanks a lot in advance

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,689

    Re: Unlocked cells becoming locked after using activex combo box

    @darry

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Protecting every sheet and not allowing to select locked or unlocked cells
    By doubl3d80 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-25-2012, 12:42 PM
  2. Highlighting Locked/Unlocked Cells
    By jderren in forum Excel General
    Replies: 2
    Last Post: 10-04-2010, 03:26 PM
  3. When protecting workbook with pw, unlocked cells are still locked
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-18-2009, 12:52 AM
  4. Locked spreadsheet, move cursor only to unlocked cells?
    By dhilligoss in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2005, 10:29 AM
  5. [SOLVED] Searching for locked/unlocked cells
    By Karthik Bhat - Bangalore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1