lundi 10 avril 2023

Shape.OLEFormat and its position in the excel sheet

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