Looping through a column and selecting the check boxes

[Modica82]

Hi all,

I am having a poor day today as i no expert with Excel VBA by a long shot, but i will be soon pulling out the little hair i have left if this continues.

I have a column with check boxes in them and all i wanna do is loop that column and set some properties of the contained checkedbox.

How do i do this using VBA???????

PLEAAAASSSEE HEEEELLLPP


---------------------------------------

http://www.thatssolastseason.co.uk

 

[SkipVought]

Hi,

Do you have any other objects in the sheet (other controls or shapes or charts)

And what kind of control -- FORMS or Control Toolbox?

Skip,

_[red][/red]

 

[ThomasLafferty]

In addition to Skip's input, if they are from the Forms toolbar, are the checkboxes entirely enclosed by the cells in which they are embedded? This may help in referencing them.

Tom

Live once die twice; live twice die once.

 

[Modica82]

Hi,

I can reference them if i use a general loop. But what i want to do is loop just that column between row 5 and 24 and update the cell links, becuase a sort messes them up. I just need to know the sytaxt of accessing the cell of a column and updating the checkbox control that it contains. I am not very good with excel so i dont know how to select the controls in a cell.

i.e in psuedo for me

For Each CheckBox in Rows(1).Columns(1)Checkboxes

Next

does that make any sense??

Regards,

Rob

---------------------------------------

http://www.thatssolastseason.co.uk

 

[SkipVought]

"I can reference them if i use a general loop. "

So in that loop test as so...

if CheckBox.topleftcell.column = thatcolumn then
  if CheckBox.topleftcell.row >= 5 and CheckBox.topleftcell.row <= 24 then
   'you're on to sumthin
     Checkbox.LinkedCell = Whatever
  end if
end if

Skip,

_[red][/red]

 

[Modica82]

THankyou!!!!

here is my code if anyone has the same problem

    Dim objCB As CheckBox
     
    For Each objCB In Worksheets("List").CheckBoxes
        
        'Collection Check Boxes
        If objCB.TopLeftCell.Column = 13 Then
            If objCB.TopLeftCell.Row >= 5 And objCB.TopLeftCell.Row <= 24 Then
                If InStr(objCB.Name, "chkCol") > 0 Then
                    'you're on to sumthin
                    objCB.LinkedCell = "$S" & "$" & objCB.TopLeftCell.Row
                End If
            End If
        End If
        
        'AM Delivery Check Boxes
        If objCB.TopLeftCell.Column = 15 Then
            If objCB.TopLeftCell.Row >= 5 And objCB.TopLeftCell.Row <= 24 Then
                If InStr(objCB.Name, "chkAM") > 0 Then
                    'you're on to sumthin
                    objCB.LinkedCell = "$U" & "$" & objCB.TopLeftCell.Row
                End If
            End If
        End If
    Next

---------------------------------------

http://www.thatssolastseason.co.uk