Falling at the last hurdle with Checkboxes and Option buttons

NoSparks · #1 06-10-2019, 11:40 PM

Thank You is all I need.

I'm just an old retired guy that plays with this stuff for entertainment.
Glad I was able to assist you along the way.

kiwimtnbkr · #2 06-10-2019, 11:51 PM

Got the individual worksheets going bang on and I thought it would also be a case of just transferring the code over to the 'ThisWorkbook' and away I would go to RESOLVED but as we have already discovered, I am not as clever as I would like to think I am...

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xFileName As String
Dim L5part As String, H17part As String
        'L5part = ThisWorkbook.Worksheets("Report template - Advanced").Range("L5").Value
        'L5part = Replace(Replace(L5part, " / ", "/"), "/", "_")
        'H17part = ThisWorkbook.Worksheets("Report template - Advanced").Range("H17").Value
        'H17part = Replace(H17part, "/", "_")
        L5part = ThisWorkbook.Worksheets("Report template - NextGen").Range("L5").Value
        L5part = Replace(Replace(L5part, " / ", "/"), "/", "_")
        H17part = ThisWorkbook.Worksheets("Report template - NextGen").Range("H17").Value
        H17part = Replace(H17part, "/", "_")
        'L5part = ThisWorkbook.Worksheets("Report template - All Future").Range("L5").Value
        'L5part = Replace(Replace(L5part, " / ", "/"), "/", "_")
        'H17part = ThisWorkbook.Worksheets("Report template - All Future").Range("H17").Value
        'H17part = Replace(H17part, "/", "_")
If SaveAsUI <> False Then
    Cancel = True
    xFileName = Application.GetSaveAsFilename(H17part & " Report" & Range("A1").Value & L5part, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save report as")
    If xFileName <> "False" Then
      Application.EnableEvents = False
      ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
      Application.EnableEvents = True
    End If
End If
End Sub

The Save workbook is now causing the final bit of grief.....If I comment out two of the three worksheets, the Save Workbook routine works - but if they are uncommented then the routine fails to get the H17part and L5part information.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Option Buttons - linking and control	natelfo	Visio	8	01-25-2024 02:01 AM
Option buttons for multiple choice questions	ScottishPolarBear	Word	4	12-02-2014 02:23 PM
Display cells dependent on option buttons	inq80	Excel	0	09-01-2014 03:40 PM
How do you achieve Falling letters?	PPT	PowerPoint	0	04-21-2012 06:12 AM
Word 2007 Option buttons - transparency	JacobThomas	Word VBA	1	11-02-2011 05:40 AM