Results 1 to 9 of 9
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Protection (2000)

    I need to make a template whereby the users can add information but not change the cell formatting. The template being used to upload data from our existing ERP system to the new system.
    Initial checks on sheet protection worked OK with straight data entry, however, I found that if say some data that was in one format is copied and pasted into the template, then the format was 'overwritten'. The only box I have ticked in the Protect Sheet selection is "Select unlocked cells". I had expected that the Paste menu would then have the format portion greyed out and inoperable. BUt this is not the case.
    So, is there a way to achieve my aim, have the template formats fixed regardless of how the user inputs data.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Protection (2000)

    Have you tried defining and applying a style to the cells in question?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protection (2000)

    You could instruct users to put the Paste Values button on the toolbar and use that instead of Paste. You could even create a custom toolbar in the template with the Paste Values button (and perhaps other buttons)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protection (2000)

    Other options:

    1) allow data input in one place and not worry about the formatting. (you can make it look like an "input form" on the sheet)

    You can then have a separate location which "reads" the info the user entered and have this formatted as desired for output

    Or
    2) you can create a userform for data entry and then place the data into the cells from the form. The user would not have the ability to change the format.


    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection (2000)

    I had this problem in a project I did several years ago. The way I ended up solving it was to create a duplicate sheet which was hidden (Very Hidden so that it did not show up on the Format|Sheet|Unhide menu). Then I created a Worksheet change event procedure that copied the cells that were changed from the hidden worksheet and PasteSpecial the formats back over the cells that were changed. It looked something like this:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Application.EnableEvents = False
    For Each oCell In Target
    Worksheets("HiddenSheet").Range(oCell.Address).Cop y
    oCell.PasteSpecial (xlPasteFormats)
    Next oCell
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protection (2000)

    A very good approach.

    The only "failure" I see is that changing the format does not trigger the change event. It is therefore possible to change all the formatting after the data has been added and your code will not run to "fix it" until someone changes the cell contents again.

    You might want to have a "generic" copy/paste format code that overwrites the formatting for the entire sheet that is done on file open or beforeclose or even beforeprint to ensure that the formatting has been corrected.

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection (2000)

    If the sheet is protected, you should not be able to change the format of cells, even unprotected cells. You should only get the problem if you paste into the cell.
    Legare Coleman

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protection (2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> of course. I forgot that the sheet was protected.

    Steve

  9. #9
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Protection (2000)

    Thanks Legare, that would make the process invisible to the users and therefore more repeatable.
    Also thanks to Catherine, Hans and Steve for their suggestions. I am going to trial all of these and then see which ones remain "unbroken"
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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