Go to adjacent cell

[New2013]

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.

 

[New2013]

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.

Sorry for not correctly stating what exactly I needed. I'd post a screenshot of one of the sheets in the workbook but it contains all financial data.

What I envision is creating one checkbox, then naming it to something like cbFoo_Click() then having copies of that checkbox (Something just hit me...is an array the answer?) do the same thing in each of the cells down the column based on whether the control is checked or not (to go two cells to the left and change the color to green, if not checked then do nothing). Am I making sense? I know I'm missing something ridiculously simple.

If I'm still not making it clear, please let me know.

Thanks

 

[Rick Rothstein]

Sorry for not correctly stating what exactly I needed. I'd post a screenshot of one of the sheets in the workbook but it contains all financial data.

What I envision is creating one checkbox, then naming it to something like cbFoo_Click() then having copies of that checkbox (Something just hit me...is an array the answer?) do the same thing in each of the cells down the column based on whether the control is checked or not (to go two cells to the left and change the color to green, if not checked then do nothing). Am I making sense? I know I'm missing something ridiculously simple.

An array of controls (at least the way you are imagining them) will not help since each individual control would still need its own individual event procedures. However, using a Class Module to create common event procedures and then linking the constrols to the Class should allow us to use one set of event codes for all the linked controls. Okay, so you are going to name your controls cbFoo (you said something like... I need to know exactly)... you should add a 1 to the first one you create, then when you copy/paste your other controls into the workbook, the number should increment automatically (so that each control ends up with a unique name).

So, let me know exactly what name you are giving the CheckBoxes. Just double checking... are all the CheckBoxes going to be located on the same worksheet? I'm not sure if it will matter yet, but just in case... what is the worksheet's name?

 

[New2013]

An array of controls (at least the way you are imagining them) will not help since each individual control would still need its own individual event procedures. However, using a Class Module to create common event procedures and then linking the constrols to the Class should allow us to use one set of event codes for all the linked controls. Okay, so you are going to name your controls cbFoo (you said something like... I need to know exactly)... you should add a 1 to the first one you create, then when you copy/paste your other controls into the workbook, the number should increment automatically (so that each control ends up with a unique name).

So, let me know exactly what name you are giving the CheckBoxes. Just double checking... are all the CheckBoxes going to be located on the same worksheet? I'm not sure if it will matter yet, but just in case... what is the worksheet's name?

The name of the first control will be cbClearedOrNot_1(). The name of the workbook is Expenses 2013 - 2014.xlsm Each worksheet will not have the same number of checkboxes on it (as the line items of expenses will vary from month to month and worksheet to worksheet). Does that answer your questions?

Thanks again!

 

[Rick Rothstein]

The name of the first control will be cbClearedOrNot_1(). The name of the workbook is Expenses 2013 - 2014.xlsm Each worksheet will not have the same number of checkboxes on it (as the line items of expenses will vary from month to month and worksheet to worksheet). Does that answer your questions?

Okay, here is the setup. First off, each CheckBox you want to have this functionality must have its name start with the characters "cbClearedOrNot_" and they must be ActiveX controls. Also, the CheckBoxes must exist when the workbook is opened... you cannot add new ones afterwards (I have never tried before, but I may be able to program around that restriction if need be, but I am not sure). Now for the code...

First, open up the VB editor (press CTRL+F11 from any worksheet). Next, add a Class Module to your project ("Insert/Class Module" on the <ACRONYM title=vBulletin>VB</ACRONYM> editor's menu bar) and copy/paste the following code into the code window that opened up...

Public WithEvents Class1 As MSForms.CheckBox

Private Sub Class1_Click()
  With Class1
    If .Value Then
      .TopLeftCell.Offset(, -2).Interior.ColorIndex = 4
    Else
      .TopLeftCell.Offset(, -2).Interior.ColorIndex = xlColorIndexNone
    End If
  End With
End Sub

Next, open up a standard Module ("Insert/Module" on the VB editor's menu bar) and copy/paste this code line into the code window that opened up...

Public CheckBoxes() As New Class1

Finally, open up the workbook code window (double click the item labeled "ThisWorkbook" in the Project window within the VB editor) and copy/paste the following code into it...

Private Sub Workbook_Open()
  Dim Counter As Integer, Obj As Object, WS As Worksheet
  For Each WS In Worksheets
    For Each Obj In WS.OLEObjects
      If Obj.Name Like "cbClearedOrNot_*" Then
        Counter = Counter + 1
        ReDim Preserve CheckBoxes(1 To Counter)
        Set CheckBoxes(Counter).Class1 = Obj.Object
      End If
    Next
  Next
  Set Obj = Nothing
End Sub

Okay, that is it for code placement. The next thing you have to do is save the workbook. If you are using XL2007 or later, then make sure to save it as an "Excel Macro-Enabled Workbook (*.xlsm). Now reopen the workbook and the CheckBoxes should all work as you wanted.

 

[BrianMH]

Hi Rick,

I've been wanting to learn more about classes and I've done a bit of reading but haven't found I've needed to use them yet. I see you are creating new instances of the class for each checkbox and each class seems to be a checkbox object (I think). Does this in effect create a separate object reference to each checkbox with it's own event code? If you have time can you explain this a bit as I think it would be very usesful in the future?

Thanks

 

[New2013]

Rick
Thank you SO much. The code works perfectly. I still have a couple of questions though. When I copy/paste the control named cbClearedOrNot_1, shouldn't it increment the number of the control to cbClearedOrNot_2, cbClearedOrNot_3, etc. because what's happening now is when I paste the control it reverts to CheckBox_1, CheckBox_2, etc.? Am I not copy/pasting it right?

Also, do I have to go through each worksheet and place the controls and name them cbClearedOrNot_15, ..... cbClearedOrNot_40, etc? Or can I name each control in each worksheet cbClearedOrNot_1, cbClearedOrNot_2, cbClearedOrNot_3, etc, in each worksheet?

Thanks!

Paul

 

[Rick Rothstein]

I've been wanting to learn more about classes and I've done a bit of reading but haven't found I've needed to use them yet. I see you are creating new instances of the class for each checkbox and each class seems to be a checkbox object (I think). Does this in effect create a separate object reference to each checkbox with it's own event code? If you have time can you explain this a bit as I think it would be very usesful in the future?

Unfortunately, no I cannot explain it to you, mainly because I do not know anything about the underpinnings of Object Oriented programming. Back in my "compiled Visual Basic" days, I shied away from Object Oriented stuff (except what was automatically exposed by the language, of course) because, one, I was able to do anything I ever wanted without using them and, two, it always seemed like Object Oriented programming was more beneficial in group programming projects and I always worked alone for the programming I did. So I hear you asking... how did the code I posted come about then?

Back in my "compiled VB" days, we had something automatically built into the language called "control arrays". Basically, they allowed you to create groups of like controls which all shared the same event procedures. When I came to Excel's VBA, I found that its VB language did not offer "control arrays", so I looked around and found an example code structure that imitated them for a group of Option Buttons. And while that code structure needs (usually heavy) modifying in order to work with other controls, I find that my apparent innate abilities to figure out thing that are technical in nature allows me to use trial and error to do so. Up until this thread's question, I have only created these imitation "control arrays" for UserForms... this was the first time I ever modified the code structure for controls on worksheets. It took some doing, but I finally solved it with the code I ended up posting here.

So, as I said at the beginning, but no, I cannot explain how it all works to you. Sorry.

 

[BrianMH]

That's all right. You have given me some things to search anyway .

 

[Rick Rothstein]

Rick

Thank you SO much. The code works perfectly. I still have a couple of questions though. When I copy/paste the control named cbClearedOrNot_1, shouldn't it increment the number of the control to cbClearedOrNot_2, cbClearedOrNot_3, etc. because what's happening now is when I paste the control it reverts to CheckBox_1, CheckBox_2, etc.? Am I not copy/pasting it right?

Also, do I have to go through each worksheet and place the controls and name them cbClearedOrNot_15, ..... cbClearedOrNot_40, etc? Or can I name each control in each worksheet cbClearedOrNot_1, cbClearedOrNot_2, cbClearedOrNot_3, etc, in each worksheet?

I had thought it would work too, but was surprised to find out that the only numerical incrementing that occurs is with the default name of CheckBox... copy/pasting a control whose name has been changed appears not to even preserve the new name that you give to the control you copied, let alone increment its number. Also, that number incrementing only applies to the controls on a single sheet... put the same kind of control on a different sheet and its numbering starts at one again. So, it looks like you may be stuck renaming each control manually... I am not aware of another way to do it.

 

[New2013]

Well that helps though. I can copy the range of controls from on spreadsheet to the next and add or subtract a control or two with each sheet. In that manner all I have to do is manually add the numbers to one sheet and then copy/paste them to the next sheet in the workbook. That will save a significant amount of time.