Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    populate cells, sometimes (2002)

    I have a spreadsheet that users fill-in various cells (sheet is protected) with info related to "Bill To" -- e.g., name, address, city, state, zip, etc.
    I have another parellel section ("Ship To") in the sheet into which I would like the option to populate the "Bill To" info if the user checks a cell or box, for example.
    AND, protect those cells! Otherwise, if the Ship To is NOT the same as the Bill To, they should be able to enter the info into those cells (obviously, they're not protected).

    Can this be done somehow?

  2. #2
    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: populate cells, sometimes (2002)

    Assume you have named the ranges "BillTo" and "ShipTo" (via insert -name -define)

    Add a checkbox from the controls toolbar and then add the code for it (you can dbl-click it to get to VB)
    <pre>Option Explicit
    Private Sub CheckBox1_Click()
    Dim rShipTo As Range
    Set rShipTo = Range("ShipTo")
    ActiveCell.Select
    ActiveSheet.Unprotect
    rShipTo.Locked = CheckBox1.Value
    If CheckBox1.Value Then
    Range("billto").Copy rShipTo
    Else
    rShipTo.ClearContents
    End If
    ActiveSheet.Protect
    End Sub</pre>


    When you "check the box", the sheet will be unprotected, if checked, it will copy the billto to the shipto, and lock the cells, if unchecked it will clear the contents and unlock the cells, it will then protect the sheet.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    Re: populate cells, sometimes (2002)

    Thanks, Steve...

    Would you tell me one more thing? What's the code in the VBA program if the spreadsheet is protected with a password and if I want to put that password back on the spreadsheet again?

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

    Re: populate cells, sometimes (2002)

    <pre> ActiveSheet("Sheet1").Unprotect ("Password")
    ActiveSheet("Sheet1").Protect ("Password")
    </pre>

    Legare Coleman

Posting Permissions

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