Go to adjacent cell

New2013

New Member
Joined
Oct 29, 2013
Messages
11
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Have you linked the checkbox to a cell? If so use data validation on the linked cell.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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. :)
 
Upvote 0
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! :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top