Help Needed - Code to change property of ALL form buttons within the whole document

[Upex]

Hi All,

I wonder if anyone has a suggestion as how I would go about this:

I have a workbook with a lot of buttons created from the forms option. These are on all sheets and at present there are around 200 sheets, each having different buttons in different places.

I (stupidly) didnt set consistent properties and not want to change all of them to be 'move and size with cells'. Does anyone know I can loop through button by button, or bulk select them all at once?

I know I need to use

        .Placement = xlMoveAndSize
        .PrintObject = False

to change them, but cant figure out how to get the loop or bulk select.

Also... not sure if it can be done, but would you know how to snap them to the cell they are in (so they are the same size as the cell)? - They mostly are (done by hand during creation) but some have fallen out due to column and row resizing. If this can be done - a point of note: some buttons are 1 cell in size, and others are across 2, 3 or more, both by column and lines. I think this may have to be a manual 'fix'. But any help with the loop or bulk select would be truely awesome.

Thanks in advance

Upex

 

[pushkardey]

Use For each loop.... if you still cant use it i will give you the code tommorow ... however that is only if i see a reply here....

 

[RoryA]

Simply:

Sub SetButtonProps()
   Dim wks As Worksheet, btn As Button
   For Each wks In ActiveWorkbook.Worksheets
      For Each btn In wks.Buttons
         With btn
            .Placement = xlMoveAndSize
            .PrintObject = False
         End With
      Next btn
   Next wks
End Sub

 

[Upex]

Rorya

Many thanks, that seems to have worked a treat. I was unsure of the term to use and tried shape, command button etc, but not button.

Also didnt know you could dimension more than 1 thing at once, so learned something new there as well.

Many thanks for your prompt and efficient help.

pushkardey

Thanks for the reply, dont worry about the code as Rorya aced it.

Thanks both, Upex