Excel Macro Question

S

Swmacneil

I have various cells which have a check box in them. I am referenceing them
as "checkbox 216 etc..." in my macro. Is there an alternate way to reference
these checkboxes so i can reference them sequentially without using the
checkbox name?
 
N

Norman Jones

Hi SW,

If your checkboxes are from the Forms toolbar, as seems likely from the
indicated name, try something like:

'=============>>
Public Sub Tester()
Dim cbx As CheckBox

For Each cbx In ActiveSheet.CheckBoxes
'do something, e.g.:
MsgBox cbx.Name
Next cbx
End Sub

End Sub
'<<=============
 
N

Norman Jones

Hi SW,

Please forgive the second End Sub.

The code should have read:

'=============>>
Public Sub Tester()
Dim cbx As CheckBox

For Each cbx In ActiveSheet.CheckBoxes
'do something, e.g.:
MsgBox cbx.Name
Next cbx
End Sub
'<<=============
 
S

Swmacneil

Thanks for the reply.
This isnt quite what i was looking for though
Is it possible to loop through a range of cells and query which cells
actually have a check in them?
 
N

Norman Jones

Hi SW,

Try:

'=============>>
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range
Dim rngOut As Range
Dim cbx As CheckBox

Set Rng = Range("A1:H20") '<<==== CHANGE

For Each rCell In Rng.Cells
For Each cbx In ActiveSheet.CheckBoxes
If Not Intersect(rCell, cbx.TopLeftCell) Is Nothing Then
If rngOut Is Nothing Then
Set rngOut = rCell
Else
Set rngOut = Union(rCell, rngOut)
End If
End If
Next cbx
Next rCell

If Not rngOut Is Nothing Then
MsgBox "Checkboxes were found at the " _
& "following addresses " & rngOut.Address(0, 0)
Else
MsgBox "No checkboxes found in cells " & Rng.Address(0, 0)
End If

End Sub
'<<=============
 
N

N0junk

I have been reading the posts and decided to try your code to see if I could
manipulate the checkboxes on an imported worksheet. It reports that it can't
find them and I think it is because I use a
"worksheet.open File:=(url with checked boxes)"

If you guys know of a way to import web pages into excel that contain
checkboxes, please let me know...
 
Top