VBA - Macro displaying error when executed

[Lee Rabbit]

Hi All,

Been trying to fix this for a couple of days but not having much luck.

I execute the following VBA via a command button and it works perfectly, however, on the sheets that are selected in the array, there are buttons.

Herein lies the problem. These buttons are temperamental and resize when the VBA runs.

Sub save_sh()

    Dim i As Long, c As Long
    Dim SheetArray() As String
   
    With ActiveSheet.ListBoxSh
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
               
           
            End If
           
       
        Next i
       
       
   
    End With
   
   
    If c = 0 Then
        MsgBox "Nothing was selected.. try again!"
    Else
           
    'set the save folder (change accordingly)
    Dim saveFolder As String
    saveFolder = "C:\PDF\EMAIL\"
   
    'set the saveas filename (change accordingly)
    Dim saveFilename As String
    saveFilename = Range("D22").Value & "_" & "WEEK" & "_" & Range("D23").Value & "_" & "INVOICES" & ".pdf" _
   
    'select the specified sheets
    Sheets(SheetArray()).Select
   
    'export the selected sheets to PDF
   
    With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .PrintArea = "$A$1:$M$30"
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 1
   
     
    End With
   
       
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveFolder & saveFilename
       
    'de-select the selected sheets
    Sheets(SheetArray(0)).Select
   
    MsgBox "THE WEEKLY INVOICE REPORT HAS BEEN SAVED AS PDF", vbExclamation
    ThisWorkbook.Sheets("MASTER SHEET").Activate
   
    End If
   
End Sub

To overcome the button resizing, I thought I'd run a WorkSheet_activate VBA. The buttons are grouped on the worksheet.

Private Sub Worksheet_Activate()

    With Me.Shapes("Group 2")
        .Width = 514
       
    End With

End Sub

I thought this would solve the issue but now I get a Run-Time error when I execute the code to create the PDF.

That points to this in my WorkSheet_activate code

I am confused why this is happening and any help on this matter would be gratefully received.

Is there a way I can run the code to resize the buttons that is not using the Worksheet_Activate option? Or a way to get passed this error?

Thanks in advance,
Regards,
Lee

 

[BobUmlas]

Select each button and right-click, use Format Control, then check the option button to not move or size with cells:

 

[Lee Rabbit]

Select each button and right-click, use Format Control, then check the option button to not move or size with cells:

View attachment 13679

Unfortunately, this is how the buttons are already set. It makes no difference the random resize of the buttons I am experiencing.

Thanks for the input though.
Regards,
Lee

 

[Lee Rabbit]

This issue is now resolved. Took me long enough...