Check box problem

[jrussellCW]

Hi there, new to the forum, I have a quick easy question. I am trying to create a macro, using vb, that will add a check box to the cell I have selected, label the check box "Produced" and link the checkbox to the cell 2 cells to the right.
It should also then create a check box in the cell to the right of "produced", and label this new check box "Received" This new check box will be linked to the cell two cells to its right.
The result will be two check-boxes in adjacent cells, returning "true" and "false" to the next two cells over.
Then I will have Two cells that sum up the number of "true's" for each column, So that I can easily see how many commercials each month production has produced, and how many they have received. I thought it would be simple, but it's giving me trouble.
oh, and the check boxes must be limited in size to the size of the cell they are in, there are going to be lots of them down the column.

 

[jrussellCW]

The counter is working fine, by the way, but it just takes forever to manually set up each check box. The macro would make this much easier

 

[HalfAce]

Here's something you might try.
Works well for me here but I guess it'll depend on your sheet layout as to how much tweaking it needs. (Uses checkboxes from the Forms toolbar.)

Sub InsertChkBx()
    
ActiveSheet.CheckBoxes.Add(0, 0, 0, 0).Select
With Selection
  .Characters.Text = "Produced"
  .ShapeRange.LockAspectRatio = msoFalse
  .ShapeRange.Height = ActiveCell.Height
  .ShapeRange.Width = ActiveCell.Width
  .ShapeRange.IncrementLeft ActiveCell.Left
  .ShapeRange.IncrementTop ActiveCell.Top
  .Value = xlOn
  .LinkedCell = ActiveCell(, 3).Address
End With
    
ActiveSheet.CheckBoxes.Add(0, 0, 0, 0).Select
With Selection
  .Characters.Text = "Received"
  .ShapeRange.LockAspectRatio = msoFalse
  .ShapeRange.Height = ActiveCell.Height
  .ShapeRange.Width = ActiveCell(, 2).Width
  .ShapeRange.IncrementLeft ActiveCell(, 2).Left
  .ShapeRange.IncrementTop ActiveCell(, 2).Top
  .Value = xlOn
  .LinkedCell = ActiveCell(, 4).Address
  .Value = xlOff
End With
    
Application.SendKeys "{ESC}"
End Sub

Does that get you any closer?

 

[Warship]

With a twist:

Sub CreateCheckBoxes()
    Dim curCell As Range, r As Long, x As Long
    r = Application.InputBox("How many rows?", "", 1, , , , , 1) - 1
    For x = ActiveCell.Row To ActiveCell.Row + r
      Set curCell = ActiveCell
      ActiveSheet.CheckBoxes.Add(curCell.Left, curCell.Top, curCell.Width, curCell.Height).Select
      With Selection
        .Characters.Text = "Produced"
        .LinkedCell = curCell.Offset(, 2).Address
        .Value = xlOn
        .Value = xlOff
      End With
      Set curCell = curCell.Offset(, 1)
      ActiveSheet.CheckBoxes.Add(curCell.Left, curCell.Top, curCell.Width, curCell.Height).Select
      With Selection
        .Characters.Text = "Received"
        .LinkedCell = curCell.Offset(, 2).Address
        .Value = xlOn
        .Value = xlOff
      End With
      ActiveCell.Offset(1).Select
    Next x
End Sub

 

[jrussellCW]

That did it, awesome thanks! Only thing I would change is that the first check box seems to be checked by default. Not a big deal. Thanks very much!

 

[jrussellCW]

With a twist:

Sub CreateCheckBoxes()
    Dim curCell As Range, r As Long, x As Long
    r = Application.InputBox("How many rows?", "", 1, , , , , 1) - 1
    For x = ActiveCell.Row To ActiveCell.Row + r
      Set curCell = ActiveCell
      ActiveSheet.CheckBoxes.Add(curCell.Left, curCell.Top, curCell.Width, curCell.Height).Select
      With Selection
        .Characters.Text = "Produced"
        .LinkedCell = curCell.Offset(, 2).Address
        .Value = xlOn
        .Value = xlOff
      End With
      Set curCell = curCell.Offset(, 1)
      ActiveSheet.CheckBoxes.Add(curCell.Left, curCell.Top, curCell.Width, curCell.Height).Select
      With Selection
        .Characters.Text = "Received"
        .LinkedCell = curCell.Offset(, 2).Address
        .Value = xlOn
        .Value = xlOff
      End With
      ActiveCell.Offset(1).Select
    Next x
End Sub

Ooh I like this one better. That will allow me to create as many as I need. Thanks! I should have kept up my visual basic. The last thing I ever did with it was years ago, i programmed a mancala game. It was awesome.

 

[HalfAce]

Yeah, that's pretty slick. I like the multiple rows at once option.

 

[Warship]

i programmed a mancala game. It was awesome.

So do it in Excel/VBA now for practice!

 

[jrussellCW]

I should make an excel mancala, that would be interesting.

Is there a way to make excel highlight rows if neither check box is checked?

 

[Warship]

I'm sure.

Does it need to be interactive?

By that I mean:
Start off with both False and row is highlighted,
Then one is True and highlight off
If set back to both False highlight again?