Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Go to adjacent cell

  1. #1
    New Member
    Join Date
    Oct 2013
    Location
    Montana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Go to adjacent cell

    Hi all
    I'm really new at this VBA programming and have searched the forums and can't find the answer to my question. I appreciate any help you all can give.

    What I'm trying to do is when a checkbox (any checkbox in the workbook) is clicked then I want the cell two to the left of it to change colors to green.

    Can anyone tell me how to do this? I don't really care if it's an ActiveX control or a regular checkbox.

    Thank you.

  2. #2
    Board Regular BrianMH's Avatar
    Join Date
    Mar 2008
    Location
    Leicester UK
    Posts
    1,490
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Go to adjacent cell

    Have you linked the checkbox to a cell? If so use data validation on the linked cell.
    The more you learn about something the more you know you have much to learn.

    I try to follow the KISS standard - Keep It Simple, Stupid.
    __________________________________________________________________________

    Why use HTML Tables instead of file links?

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    NY
    Posts
    1,056
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Go to adjacent cell

    Sub Greener()
    Set cb = ActiveSheet.CheckBoxes(Application.Caller)
    If cb.Value = 1 Then
    Range(cb.TopLeftCell.Address).Offset(0, -2).Interior.Color = vbGreen
    Else
    Range(cb.TopLeftCell.Address).Offset(0, -2).Interior.Color = xlNone
    End If
    End Sub

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,885
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Go to adjacent cell

    Assuming an ActiveX CheckBox named CheckBox1, use this for its Click event code...

    Code:
    Private Sub CheckBox1_Click()
      If CheckBox1.Value Then
        CheckBox1.TopLeftCell.Offset(, -2).Interior.ColorIndex = 4
      Else
        CheckBox1.TopLeftCell.Offset(, -2).Interior.ColorIndex = xlColorIndexNone
      End If
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Oct 2013
    Location
    Montana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Go to adjacent cell

    Quote Originally Posted by Rick Rothstein View Post
    Assuming an ActiveX CheckBox named CheckBox1, use this for its Click event code...

    Code:
    Private Sub CheckBox1_Click()
      If CheckBox1.Value Then
        CheckBox1.TopLeftCell.Offset(, -2).Interior.ColorIndex = 4
      Else
        CheckBox1.TopLeftCell.Offset(, -2).Interior.ColorIndex = xlColorIndexNone
      End If
    End Sub
    Thank you for your response!

  6. #6
    New Member
    Join Date
    Oct 2013
    Location
    Montana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Go to adjacent cell

    Thanks for your response!

  7. #7
    New Member
    Join Date
    Oct 2013
    Location
    Montana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Go to adjacent cell

    Quote Originally Posted by Rick Rothstein View Post
    Assuming an ActiveX CheckBox named CheckBox1, use this for its Click event code...

    Code:
    Private Sub CheckBox1_Click()
      If CheckBox1.Value Then
        CheckBox1.TopLeftCell.Offset(, -2).Interior.ColorIndex = 4
      Else
        CheckBox1.TopLeftCell.Offset(, -2).Interior.ColorIndex = xlColorIndexNone
      End If
    End Sub
    Hmmm. I tried this code and it didn't work. Any ideas why not?

    Thanks.

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,885
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Go to adjacent cell

    Quote Originally Posted by New2013 View Post
    Hmmm. I tried this code and it didn't work. Any ideas why not?
    Since we cannot see your computer screen, you will have to describe "didn't work" for us. What, if anything, happened?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    New Member
    Join Date
    Oct 2013
    Location
    Montana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Go to adjacent cell

    OK...What it does is change the color of the cell 2 to the left and 1 cell up. Also, I want to be able to put this checkbox in several cells in the column and have it do the same thing (change the color of the cell two to the left of it) in each checkbox. Is that possible? Or is there some other code that I'm going to have to use for it. I'd like to not have to write code for every checkbox in the workbook when it's doing the same thing for each instance of the checkbox.

    Thanks!

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,885
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Go to adjacent cell

    Quote Originally Posted by New2013 View Post
    OK...What it does is change the color of the cell 2 to the left and 1 cell up. Also, I want to be able to put this checkbox in several cells in the column and have it do the same thing (change the color of the cell two to the left of it) in each checkbox. Is that possible? Or is there some other code that I'm going to have to use for it. I'd like to not have to write code for every checkbox in the workbook when it's doing the same thing for each instance of the checkbox.
    Just so you know, of all the things I tried to imagine you meant by "didn't work", what you just posted was not on the list. For future reference, it is important to give full details when you post a question or follow-up in a forum. As for your latest question, yes, I think I can doctor up a way to have only one event for all the CheckBoxes using a Class Module (don't worry, I will be able to talk you through it), but I need to know the naming convention you used for the CheckBoxes. For example, do they all start with the letters "CheckBox"? If not, is there a common text beginning for their names? If not, you will have to list all the names for us. Also, if the CheckBoxes do have a common beginning text (such as CheckBox), do any other controls that you do not want to have this functionality also have those same beginning letters? If so, I would presume those letters are followed by a number...what numbers apply to the CheckBoxes you want to have this functionality? The idea behind my questions is I need some way for the code to identify only those CheckBoxes that you want to have this functionality.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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