+ Reply to Thread
Results 1 to 4 of 4

Lock cell linked to checkbox when checkbox is ticked

  1. #1
    Registered User
    Join Date
    04-22-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Lock cell linked to checkbox when checkbox is ticked

    Hello,

    I have a spreadsheet where all cells within column E have a checkbox within them. I am looking to link the checkbox to a macro so that when the checkbox is ticked the macro will fill the cell with the user name, date and time and then lock that cell.

    So far I have a macro for the user details but am unable to also lock the cell.

    Please can you help.

    My VBA is:

    Sub CheckBox1_Click()
    Dim cBox As CheckBox
    Dim lRow As Integer
    Dim LRange As String

    LName = Application.Caller
    Set cBox = ActiveSheet.CheckBoxes(LName)

    lRow = cBox.TopLeftCell.Row
    LRange = "E" & CStr(lRow)


    If cBox.Value > 0 Then
    ActiveSheet.Range(LRange).Value = Format(Date, "dd-mmm") & " " & Format(Time, "hh:mm") & " by " & (Application.UserName)

    Else

    ActiveSheet.Range(LRange).Value = Null
    End If

    End Sub

    Thanks

    Simone

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,217

    Re: Lock cell linked to checkbox when checkbox is ticked

    Hi Simone,

    Please edit your post to re-insert your code and mark it using code tags, then PM me and I (or someone else) can help you out. See forum rule below:

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Registered User
    Join Date
    04-22-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Lock cell linked to checkbox when checkbox is ticked

    Hello,

    Sorry didn't realise I could add those codes in. I think i have added them now, although let me know if I need to change anything else.

    Sub CheckBox1_Click()
    Dim cBox As CheckBox
    Please Login or Register  to view this content.
    Dim lRow As Integer
    Please Login or Register  to view this content.
    Dim LRange As String

    Please Login or Register  to view this content.
    LName = Application.Caller
    Please Login or Register  to view this content.
    Set cBox = ActiveSheet.CheckBoxes(LName)

    Please Login or Register  to view this content.
    lRow = cBox.TopLeftCell.Row
    Please Login or Register  to view this content.
    LRange = "E" & CStr(lRow)


    Please Login or Register  to view this content.
    If cBox.Value > 0 Then
    Please Login or Register  to view this content.
    ActiveSheet.Range(LRange).Value = Format(Date, "dd-mmm") & " " & Format(Time, "hh:mm") & " by " & (Application.UserName)

    Please Login or Register  to view this content.
    Else

    Please Login or Register  to view this content.
    ActiveSheet.Range(LRange).Value = Null

    Please Login or Register  to view this content.
    End If

    End Sub

    Thanks

    Simone

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,217

    Re: Lock cell linked to checkbox when checkbox is ticked

    You should have edited your first post rather than reposting the material again. Whatever you did did not work. You can either type in the codes described in my post above, or select all of your code then click the # symbol in the controls above your post edit area, which will insert the codes for you.

+ 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. Changing cell information depending on whether checkbox is ticked
    By Miss_Ahmad in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-06-2012, 09:29 PM
  2. If Checkbox is Ticked and Cell has Value then Run Macro
    By Heavenkissing in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2011, 10:34 AM
  3. The sum of a column, EXCEPT when a checkbox is ticked
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2011, 02:26 PM
  4. Add value when checkbox is ticked
    By emel24 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-29-2006, 05:21 AM
  5. VBA for put cell C2 in overstrike/black when B2 checkbox is ticked?
    By StargateFan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2005, 11:06 AM

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