I wanted to create a simple macro that simply checked if a check box is checked or not and based on that, hide or show the row.
But there are some catches, I cannot link the chackbox to the cell, otherwise another bigger macro generates an error.
I did researcha bit and found that you can do:
sheets(1).shapes("Checkbox 88").topleftcell.row
to get the row of the shape.
So I tried to implement this to my code:
Dim sh As Shape
For Each sh In Sheets(1).Shapes
If TypeOf sh.OLEFormat.Object Is CheckBox Then
If sh.OLEFormat.Object.Value = -4146 Then
'sh.OLEFormat.Object.TopLeftCell.Row.EntireRow.Hidden = True
MsgBox "Hi"
End If
End If
Next sh
I know that the:
sh.OLEFormat.Object.TopLeftCell.Row.EntireRow.Hidden = True
is wrong, because if I run the macro as I posted it, the macro returns the msgbox "Hi", because the wrong part is commented.
The strange part for me is that if I do:
Dim aux As Byte
Dim sh As Shape
aux = Sheets(1).Shapes("Checkbox 88").OLEFormat.Object.TopLeftCell.Row
'checkbox 88 is one of the checkboxes/shapes in the excel document
MsgBox aux
it works to get the row...
I am thinking that the error has to do with the OLEFormat.object
or something, but my google researches came empty handed.
Aucun commentaire:
Enregistrer un commentaire