Button positions listed out

[adeeshc]

Hello All,

Been desperately looking for a code snippet which i could use to list the position coordinates of all buttons used in a workbook.

What i want is to list down the Sheet Name - Button Name - Button Height - Button Width - Button Top - Button Left in a new worksheet and append one after the other

Something like a loop to run through each sheet and within that another loop to list the names and other relevant properties of each button in that sheet and close loop, move to the next.

Would be really grateful if someone could help here.

Thank You.

 

[wideboydixon]

Something like this?

Public Sub ListAllButtons()

Dim thisSheet As Worksheet
Dim buttonSheet As Worksheet
Dim thisRow As Long
Dim i As Long

Set buttonSheet = ActiveWorkbook.Sheets.Add
buttonSheet.Name = "Button Positions"
buttonSheet.Cells(1, 1).Value = "Sheet Name"
buttonSheet.Cells(1, 2).Value = "Button Name"
buttonSheet.Cells(1, 3).Value = "Button Height"
buttonSheet.Cells(1, 4).Value = "Button Width"
buttonSheet.Cells(1, 5).Value = "Button Top"
buttonSheet.Cells(1, 6).Value = "Button Left"

thisRow = 2
For Each thisSheet In ActiveWorkbook.Sheets
    For i = 1 To thisSheet.Buttons.Count
        With buttonSheet
            .Cells(thisRow, 1) = thisSheet.Name
            .Cells(thisRow, 2) = thisSheet.Buttons(i).Name
            .Cells(thisRow, 3) = thisSheet.Buttons(i).Height
            .Cells(thisRow, 4) = thisSheet.Buttons(i).Width
            .Cells(thisRow, 5) = thisSheet.Buttons(i).Top
            .Cells(thisRow, 6) = thisSheet.Buttons(i).Left
        End With
    Next i
Next
buttonSheet.Columns("A:F").AutoFit

End Sub

 

[adeeshc]

Thanks for the quick reply.

I am getting an type mismatch error , which when debugged highlights the 'next' to the initial For loop. Tried it with 'Next thissheet' but still not working. Please help.


-- removed inline image ---

 

[adeeshc]

-- removed inline image ---

 

[wideboydixon]

Do you have one or more charts in your workbook perhaps?

Try this instead:

For Each thisSheet In ActiveWorkbook.Worksheets

WBD

 

[adeeshc]

Yes I do have more than one charts in the workbook.

Yes tried it with - For Each thisSheet In ActiveWorkbook.Worksheets but this time no error but simply creates the sheet with the headings and stops.

Also tried - For Each thisSheet In ThisWorkbook.Worksheets but again with same result.

Between could see a line where it gives thisRow = 2 but thisRow is not added afterwards, will this not result in pasting the positions to the same row always.

 

[adeeshc]

I think the problem is because the buttons i have in the workbook are all active x control buttons.

I added a form controls button and checked and this is working fine.

Kindly help in tweaking the code to suit active x control buttons.

 

[wideboydixon]

I'm having a bad day it seems. I tested this but only with a single button on a single sheet do I didn't pick up my mistake. Also, I didn't think about OLEObjects. Give this a whirl:

Public Sub ListAllButtons()

Dim buttonSheet As Worksheet
Dim thisSheet As Worksheet
Dim thisRow As Long
Dim i As Long

Set buttonSheet = ActiveWorkbook.Sheets.Add
buttonSheet.Name = "Button Positions"
buttonSheet.Cells(1, 1).Value = "Sheet Name"
buttonSheet.Cells(1, 2).Value = "Button Name"
buttonSheet.Cells(1, 3).Value = "Button Height"
buttonSheet.Cells(1, 4).Value = "Button Width"
buttonSheet.Cells(1, 5).Value = "Button Top"
buttonSheet.Cells(1, 6).Value = "Button Left"

thisRow = 2
For Each thisSheet In ActiveWorkbook.Worksheets
    For i = 1 To thisSheet.Buttons.Count
        With buttonSheet
            .Cells(thisRow, 1) = thisSheet.Name
            .Cells(thisRow, 2) = thisSheet.Buttons(i).Name
            .Cells(thisRow, 3) = thisSheet.Buttons(i).Height
            .Cells(thisRow, 4) = thisSheet.Buttons(i).Width
            .Cells(thisRow, 5) = thisSheet.Buttons(i).Top
            .Cells(thisRow, 6) = thisSheet.Buttons(i).Left
        End With
        thisRow = thisRow + 1
    Next i
    
    For i = 1 To thisSheet.OLEObjects.Count
        If thisSheet.OLEObjects(i).progID Like "*.CommandButton.*" Then
            With buttonSheet
                .Cells(thisRow, 1) = thisSheet.Name
                .Cells(thisRow, 2) = thisSheet.OLEObjects(i).Name
                .Cells(thisRow, 3) = thisSheet.OLEObjects(i).Height
                .Cells(thisRow, 4) = thisSheet.OLEObjects(i).Width
                .Cells(thisRow, 5) = thisSheet.OLEObjects(i).Top
                .Cells(thisRow, 6) = thisSheet.OLEObjects(i).Left
            End With
            thisRow = thisRow + 1
        End If
    Next i
Next
buttonSheet.Columns("A:F").AutoFit

End Sub

WBD

 

[adeeshc]

Yohooo....works like a charm.

Thanks a lot sir....much appreciated!