+ Reply to Thread
Results 1 to 6 of 6

Using Checkbox to Change Cell Value and Lock Cell

  1. #1
    Forum Contributor
    Join Date
    01-29-2009
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    123

    Using Checkbox to Change Cell Value and Lock Cell

    Hi

    I need a checkbox (actually a few but I will use the same code and change the name and cell reference) to do the following and I hope you can help!

    1. Checkbox in A1 is ON by default
      • B1 is 100
      • User can free change the value of B1 when checkbox is on
    2. When checkbox is OFF
      • "100" (or whatever number entered by the user) gets stored
      • B1 changes to a relatively large number "9999"
      • B1 changes cell color to grey and is locked for edit
    3. When checkbox is ON again
      • The value stored previous to checkbox turned OFF is restored back to B1
      • B1 changes back to normal cell color (no fill or white)
      • B1 is unlocked and user can edit the cell

    Please help, thank you!
    Last edited by glennchung; 08-18-2014 at 05:26 PM.

  2. #2
    Registered User
    Join Date
    07-17-2014
    Location
    Turkey
    MS-Off Ver
    2003
    Posts
    85

    Re: Using Checkbox to Change Cell Value and Lock Cell

    Do you want to use checkbox on userform?

  3. #3
    Registered User
    Join Date
    07-17-2014
    Location
    Turkey
    MS-Off Ver
    2003
    Posts
    85

    Red face Re: Using Checkbox to Change Cell Value and Lock Cell

    Try this attached file please...

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-29-2009
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    123

    Re: Using Checkbox to Change Cell Value and Lock Cell

    I don't need this to be on a userform. Does it mean I can still use the same code and just make sure my checkbox names are the same?

    But when I open the file and click on the checkbox, it gave me a runtime error 1004.

    And for the value in B1, is there a way to make it dynamic? It will not always be "100". It will start off at 100 and user can change it. But I want the last value on B1 to be stored before checkbox is OFF and restored back to B1 after checkbox is ON again.

  5. #5
    Forum Contributor
    Join Date
    01-29-2009
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    123

    Re: Using Checkbox to Change Cell Value and Lock Cell

    Anyone has any idea?

  6. #6
    Forum Contributor
    Join Date
    01-29-2009
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    123

    Re: Using Checkbox to Change Cell Value and Lock Cell

    I made some minor changes to cemregunay's code. But I still can't figure out how to store the original value BEFORE it turns to "99999" (Checkbox OFF) and be able to bring it back when checkbox is ON. This value in Cell D10 can be edited whenever checkbox is ON. Any idea?


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. change a value in a cell using a checkbox in excel 2010
    By skinman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-18-2013, 09:56 PM
  2. [SOLVED] checkbox to change cell color
    By mezza89 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-05-2013, 03:03 PM
  3. checkbox cell range change vba
    By novitaun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-14-2008, 12:53 PM
  4. How to make checkbox change cell color?
    By walkingthecow in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2008, 12:19 PM
  5. Change cell value with checkbox
    By bazzdaciple in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2007, 06:16 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