Form buttons resizing

[BrendanDixon]

Hi All,

I have A spreadsheet with 4 form buttons on it. This worked fine until our ICT department started upgrading all our computers from excel 2007 to 2010.

The problem I have now is that when saving the sheets as a PDF the buttons automatically resize themselves to be smaller so that I can hardly see them. When I click on the small button it will then resize back to its original size.

I have tried different sizes, different fonts, tried playing with the properties of the form buttons. But I cannot stop this behavior. Can anyone help me?

 

[BrendanDixon]

bump

 

[BrendanDixon]

morning bump

 

[Ruddles]

Morning.

Resize the buttons when the worksheet loads. You can use absolute values for the widths and heights or you can set them equal to the width of a given column or twice the height of a given cell, etc.

Any good?

 

[BrendanDixon]

Morning.

Resize the buttons when the worksheet loads. You can use absolute values for the widths and heights or you can set them equal to the width of a given column or twice the height of a given cell, etc.

Any good?

Hi Ruddles. That is what I was thinking the problem is I have no idea how to do this.

PS I have 4 buttons on each sheet with exactly the same size 0.77cm by 1.72cm.
Would I have to write a macro for each button or is there somthing that can resize all the buttons in one line.

Thanks

 

[Ruddles]

Ah, I can't do cms, only pixels!

You can deal with the whole lot in one go - something like this in the workbook's Workbook_Open event handler will do the job:-

Option Explicit
 
Private Sub Workbook_Open()
 
  Dim iButton As Integer
  
  With ThisWorkbook.Sheets("Sheet1")
  
[COLOR=blue]    For iButton = 1 To 4
      .Shapes("Button " & iButton).Width = 60
      .Shapes("Button " & iButton).Height = .Rows(1).RowHeight * 1.8
      .Shapes("Button " & iButton).Top = .Rows(1).Top
    Next iButton
[/COLOR]    
[COLOR=red]    .Shapes("Button 1").Left = 0
    .Shapes("Button 2").Left = .Columns("C").Left
    .Shapes("Button 3").Left = .Range("E1").Left
    .Shapes("Button 4").Left = .Cells(1, 7).Left
[/COLOR]      
  End With
  
End Sub

The bit in blue assumes your buttons are called Button 1, Button 2, Button 3 and Button 4. If they're not, I'll have to rewrite that bit. The top, width and height of the buttons are common so we can handle those properties inside the loop, but as their horizontal positions are different, those are handled outside the loop by the code in red. (But see footnote.)

As you can see, you can set the properties by providing actual values (like 60 and 0 above), or with reference to the properties of rows, columns, cells or ranges. I've used a mix of techniques to demonstrate what's available.

Footnote: instead of setting the horizontal position outside the loop, you can do it inside the loop if the buttons are evenly spaced and in numerical order. Either of the following bits of code will do that:-

    For iButton = 1 To 4
      .Shapes("Button " & iButton).Width = 60
      .Shapes("Button " & iButton).Height = .Rows(1).RowHeight * 1.8
[COLOR=#000000][COLOR=green][COLOR=#000000]      .Shapes("Button " & iButton).Top = .Rows(1).Top[/COLOR]
      ' Button 1 in column 2, Button 2 in column 4, etc[/COLOR]
      [COLOR=magenta].Shapes("Button " & iButton).Left = .Columns(iButton * 2).Left
[/COLOR]    Next iButton
[/COLOR]

or:-

[COLOR=#008000][COLOR=black]    For iButton = 1 To 4
      .Shapes("Button " & iButton).Width = 60
      .Shapes("Button " & iButton).Height = .Rows(1).RowHeight * 1.8
[/COLOR][/COLOR]
[COLOR=#000000][COLOR=green] [COLOR=#000000]     .Shapes("Button " & iButton).Top = .Rows(1).Top[/COLOR]
      ' Button 1 at pixel 0, Button 2 at pixel 200, etc[/COLOR]
[COLOR=magenta]      .Shapes("Button " & iButton).Left = 200 * (iButton - 1)
[/COLOR]    Next iButton
[/COLOR]

I hope I haven't thrown too much info at you!

 

[BrendanDixon]

Hi Ruddles,

Allot to take in but I sort of understand. I am not sure how you tell what the buttons are called. My buttons have captions "Input", "Prev", "Next", "Print"

PS when I run the save as PDF macro I have multiple sheets selected and it is on these multiple sheets that I have the problem. I am not sure if the multiple sheets being selected will work with your fix.

**** I am so annoyed with this problem.

 

[Ruddles]

Right-click the button and its name will appear just above cell A1:-

Never mind messing about with the loop - just change each property of each button on each sheet one at a time. Make a list of what each of your buttons is named and which worksheet it's found on, then you can set all their properties in Workbook_Open:-

Private Sub Workbook_Open()

  With ThisWorkbook.Sheets("Sheet1")
    .Shapes("Button 1").Top = 0
    .Shapes("Button 1").Left = .Range("B1").Left
    .Shapes("Button 1").Height = .Rows(1).RowHeight * 2
    .Shapes("Button 1").Width = .Columns("B").Width
  End With
  
  With ThisWorkbook.Sheets("Sheet1")
    .Shapes("Button 2").Top = 0
    .Shapes("Button 2").Left = .Range("D1").Left
    .Shapes("Button 2").Height = .Rows(1).RowHeight * 2
    .Shapes("Button 2").Width = .Columns("D").Width
  End With
  
  With ThisWorkbook.Sheets("Sheet2")
    .Shapes("Button 1).Top = 0
    .Shapes("Button 1).Left = .Range("B").Left
    .Shapes("Button 1).Height = .Rows(1).RowHeight * 2
    .Shapes("Button 1).Width = .Columns("B").Width
  End With
    
End Sub

Without seeing your worksheet I can't guess exactly where and what size you want to fix your buttons but you can tinker around with the code until you get them to your liking.

I had a similar problem a while ago when I rolled out a workbook to a department where every user seemed to have a different computer with a different graphics card and a different monitor, and everyone kept their view zoomed a different amount. I ended up coding Workbook_Open to resize every single button and combobox every time the workbook opened with slight variations for every user.

 

[BrendanDixon]

Hi Ruddles,

I think your code would take to much time to write, considering I work with up to 30 sheets with 4 buttons each

but I have manged to find a fix to my problem. I found that if I changed any of the page setup settings it resized the buttons back to their original size. This is the code I have written and it seems to work perfect.

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Zoom = 65
    End With
    Application.PrintCommunication = True
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Zoom = 60
    End With
    Application.PrintCommunication = True