oh dear! I must be under the weather... so obvious that when I saw your reply I rolled my eyes as my own stupidity!
After sorting out a couple of minor errors of my own doing, I am stoked to report that the UserForm now does exactly what I need it to do and with the carryover into the spreadsheet so THANK YOU for your perseverance and tolerance for my lack of VBA skills.
But (there is always a but!) I have now run into another issue that I am struggling with.
Each of the individual Report template sheets, as you would have seen, have a command button to save the Report template as an .xlsm file. Part of the macro also copies information contained in two cells and uses that info as the majority of the file name. It also looks for "/", " / " and replaces it with "_" if they appear in either of the two cells so as not to muck up the file name and require the User to change them manually.
Query 1 - is there a way to have the macro read a "/" in either of the two referenced cells and convert it to a "_" in the file name but leave it as "/" in the cell?
The code for the command button, and different where necessary according to the worksheet names, is:
Code:
Private Sub CommandButton1_Click()
Sheet2.CommandButton1.BackColor = RGB(242, 242, 242)
Dim fName As Variant
Dim myCell As Range
Dim myCharacterToReplace As String
Dim myReplacementCharacter As String
Set myCell = ThisWorkbook.Worksheets("Report template - Advanced").Range("L5")
myCharacterToReplace = " / "
myReplacementCharacter = "_"
myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace _
, Replace:=myReplacementCharacter)
Set myCell = ThisWorkbook.Worksheets("Report template - Advanced").Range("L5")
myCharacterToReplace = "/"
myReplacementCharacter = "_"
myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace _
, Replace:=myReplacementCharacter)
Set myCell = ThisWorkbook.Worksheets("Report template - Advanced").Range("H17")
myCharacterToReplace = "/"
myReplacementCharacter = "_"
myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace _
, Replace:=myReplacementCharacter)
fName = Application.GetSaveAsFilename(InitialFileName:=Range("H17").Value & " Report" _
& Range("A1").Value & Range("L5").Value, FileFilter:="Excel Files (*.xlsm), *.xlsm" _
, Title:="Save As COMSEC Incident report as")
If fName = False Then Exit Sub
ActiveWorkbook.SaveAs filename:=fName, FileFormat:=52
End Sub
This code works as intended but I couldn't guarantee that the users will use the CommandButton and not the File>Save or File>Save As method or by the using the Save icon. In an effort to overcome that I found this piece of code and utilised it and it worked fine with the initial iteration I had that was only one Report template and not the three that I now currently have.
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xFileName As String
Dim myCell As Range
Dim myCharacterToReplace As String
Dim myReplacementCharacter As String
Set myCell = ThisWorkbook.Worksheets("Report template").Range("L5")
myCharacterToReplace = " / "
myReplacementCharacter = "_"
myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace, Replace:=myReplacementCharacter)
Set myCell = ThisWorkbook.Worksheets("Report template").Range("L5")
myCharacterToReplace = "/"
myReplacementCharacter = "_"
myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace, Replace:=myReplacementCharacter)
Set myCell = ThisWorkbook.Worksheets("Report template").Range("H17")
myCharacterToReplace = "/"
myReplacementCharacter = "_"
myCell.Value = Replace(Expression:=myCell.Value, Find:=myCharacterToReplace, Replace:=myReplacementCharacter)
If SaveAsUI <> False Then
Cancel = True
xFileName = Application.GetSaveAsFilename(InitialFileName:=Range("H17").Value & Range("A1").Value & Range("L5") _
, FileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="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
I thought it might have been a simple case of adding in the worksheet names and then repeating the red highlighted code to cover off the other worksheets and the required cells. It does kind of work if I use File>Save or the Save icon but if I do a File>Save As and then click the Save UI Cancel button it restarts the UI and offers to save it as an .xltm which is exactly what I want to avoid. I would have thought that clicking Cancel would have closed the Save UI like it does when I do it via the CommandButton method.
I also tried referencing the three worksheets as an Array but that didn't work either.
Query 2 - how, what, where have I gone wrong in the save routine.... and apologises for the longwinded ramble.