VBA to set properties of checkbox to 'unlocked'

  • Hi all,


    I have some code that members here helped with a while ago, this is attached to a small userform and it inputs text into a cell with a checkbox in the cell to the right:



    I'm working on protecting the sheet and I think I'm having problems because I've discovered that checkboxes are locked by default.


    Is there any way that when checkboxes are added with the above code, that the properties can be set so that they are unlocked?


    Thanks,
    -Adam

  • Re: VBA to set properties of checkbox to 'unlocked'


    I've found that by unlocking all cells on the sheet then protecting it and running the code to add a new text entry and checkbox, it's stopping and the following line of code is highlighted in yellow...


    Code
    Set cb = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)





    Does anyone know where I'm going wrong here?



    Thanks,
    -Adam

  • Re: VBA to set properties of checkbox to 'unlocked'


    Hi Stephen,


    I'm getting the following error:


    Run-time error '1004':
    Unable to get the Add property of the CheckBoxes class


    I've added your code into the function but the same error is coming up

  • Re: VBA to set properties of checkbox to 'unlocked'


    I think you will need to unprotect the sheet before trying to add the checkbox and then protect it afterwards, or perhaps try this line after your first With:

    Code
    .Protect Contents:=True, userInterfaceOnly:=True
  • Re: VBA to set properties of checkbox to 'unlocked'


    Hi,


    I'm trying to get it to function so that users aren't able to unprotect the sheet, and leave only the areas where the text and checkboxes will be added unlocked. Unprotecting the sheet makes everything work again, so maybe this is a limitation of Excel?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!