how to lock the height and the width of the cell using macros on Excel 2003


  • I need help to protect particular cells/rows/columns in a sheet automatically. We are currently having a huge workbook and want to limit the characters in specific cells anywhere in a sheet which is not possible using the available options with Excel. How can this be accomplished using Macros?


    We tried selecting the cells so we should be able to edit, right-click and choose Format Cells. On the Protection tab un-check 'Locked' and clicked OK. then Protect the sheet so we can enter data/formulas but not change any formatting but we wanted the same format on multiple cells.


    We need the form where the user cannot manipulate the data that is already on the form.


    I am hoping to get detailed instructions on how to make this possible. thanks!





    O/S: Windows XP


    3 марта 2011 г. 22:37


  • Hi

    Limiting the number of characters a user can enter in to a cell is possible through Data Validation, under the Data menu.  Use the text length option in the Allow drop down menu and then set the options to less than and the number of characters.

    Your use of worksheet protection is the only way to preserve the row heights and widths of columns.

    Applying these can be done via a macro, but by the time you've worked out which cells need which type of protection and what lengths of text are required for each  range or cell, then it would be as quick to apply by hand.

    You can add data validation to a whole row/column by selecting it before configuring the options.  The same validation will then be in place for all the cells in the selection.


    G North MMI
    • Помечено в качестве ответа Sally Tang 7 марта 2011 г. 7:22
    4 марта 2011 г. 12:07