Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    How to reset checkbox?

    Good Morning All.

    My experience of VB is not great, so any help concerning the below will be much appreciated.

    I've created the below line of script to change the colour of a row and enter a username when a check box is ticked.

    Private Sub CheckBox1_Click()
    Range("A3:C3").Interior.ColorIndex = 14
    Range("E3").Value = Environ("UserName")
    End Sub


    What i require is the ability to reset the fields when the box is unchecked.

    For example:
    If the default colour of that row is white, when the box is unchecked, the colour of that row is then reset to it's default value and the username is removed from the corresponding field.

    Thank you for your help in advance!

    Kind Regards.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you want to simply clear the fill colour, you can use:
    Code:
    Private Sub CheckBox1_Click()
        If CheckBox1.Value Then
            Range("A3:C3").Interior.ColorIndex = 14
            Range("E3").Value = Environ("UserName")
        Else
            Range("A3:C3").Interior.ColorIndex = xlColorIndexNone
            Range("E3").ClearContents
        End If
    End Sub
    If you want to reset it to whatever it was, you'd need to either store the original colour somewhere, or apply conditional formatting to colour it and then remove the CF when the checkbox is unchecked.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    BGL (2015-11-09)

  4. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Rory,

    This is great!

    Exactly what I'm after, thank you very much!

    Enjoy the rest of your day


    Kind Regards.
    Last edited by BGL; 2015-11-09 at 06:54. Reason: Solved

Tags for this Thread

Posting Permissions

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