Falling at the last hurdle with Checkboxes and Option buttons

NoSparks · #1 06-11-2019, 07:13 AM

You're not telling it which one of the three sheets to use.

Only one of those sheets should be visible and that's the one you want.

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim xFileName As String
    Dim L5part As String, H17part As String
    Dim ws As Worksheet

If ThisWorkbook.Worksheets("Report template - Advanced").Visible = True Then
    Set ws = ThisWorkbook.Worksheets("Report template - Advanced")
ElseIf ThisWorkbook.Worksheets("Report template - NextGen").Visible = True Then
    Set ws = ThisWorkbook.Worksheets("Report template - NextGen")
Else
    Set ws = ThisWorkbook.Worksheets("Report template - All Future")
End If

    L5part = ws.Range("L5").Value
    L5part = Replace(Replace(L5part, " / ", "/"), "/", "_")
    H17part = ws.Range("H17").Value
    H17part = Replace(H17part, "/", "_")

If SaveAsUI <> False Then
    Cancel = True
    xFileName = Application.GetSaveAsFilename(H17part & " Report" & ws.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

Hope that works.

kiwimtnbkr · #2 06-11-2019, 10:03 AM

THAT absolutely gets two

from me and also a BIG.GREEN.RESOLVED placed on it!

One thing I have enjoyed about this, is the reading I have been doing for how the code you have provided works - the use of ElseIf and Else - the use of and difference between cellrefpart = Replace(Replace and cellrefpart = Replace was very clever and very subtle.

I feel that if I had to expand this workbook to include other sections that I would be able to now without too many issues and any errors generated would likely be typos on my behalf - like typing in the last lot of code and typing in 'Dim ws as Workbook' instead of 'Dim ws as Worksheet' and then going HUH! when it errored until I discovered my typo.

MANY MANY thanks for ALL the assistance that you have provided me with in getting this spreadsheet project up and running how I had envisioned it.

Mike

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