Convert VBA Private Sub triggered by SaveAsUI to a Macro Button

[tangnfi]

The Private Sub is triggered by "Save As". "BEFORE SAVE" it converts specific formulas to values, on several tabs. This is needed for archiving data however is a problem when there are updates to the template requiring a new version number in the filename. I am not sure how to convert the code so it can only be run on purpose, by clicking a button. The file is too large to attach. Hopefully the code will suffice. Thank you in advance to anyone who responds!

>>> moved from Ask an Excel Question to VBA Macros <<<
>>> use code - tags <<<

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
      
Dim I As Integer
Dim J As Integer
Dim Rng As Range
If SaveAsUI Then
      Dim rs As Worksheet
      For Each rs In Sheets
        If rs.Name = "JOB list" Then
          rs.Range("K2:L1001").Value = rs.Range("K2:L1001").Value
        End If
        If rs.Name = "EmpTbl" Then
          rs.Range("A1:E8000").Value = rs.Range("A1:E8000").Value
          rs.Range("G1:H8000").Value = rs.Range("G1:H8000").Value
          rs.Range("L1:L8000").Value = rs.Range("L1:L8000").Value
        End If
        If rs.Name = "Outsourced PD" Then
          rs.Range("A3:C298").Value = rs.Range("A3:C298").Value
        End If
        If rs.Name = "POV" Then
          rs.Range("D1:E1001").Value = rs.Range("D1:E1001").Value
          rs.Range("U1:U1001").Value = rs.Range("U1:U1001").Value
        End If
      Next rs
    End If
End Sub

 

[tangnfi]

Even better would be to add a confirmation step to the VBA .... so that users can Opt-In or Opt-Out of replacing those formulas with values. I would say, replacing the formulas with values should be a choice to "Opt-In" to, with the default choice being to not replace the formulas with values.

 

[tangnfi]

I think I figured it out. It is working, though does not have a confirmation step. Would love some advice on how to add that.. and verification that I didn't leave any other holes in it that I should plug. I was never formally trained on VBA. Or most things ;-p

>>> As You've noted <<<
>>> use code - tags <<<

Sub Workbook_Convert_External_References_to_Values()
       
Dim I As Integer
Dim J As Integer
Dim Rng As Range
      Dim rs As Worksheet
      For Each rs In Sheets
        If rs.Name = "JOB list" Then
          rs.Range("K2:L1001").Value = rs.Range("K2:L1001").Value
        End If
        If rs.Name = "EmpTbl" Then
          rs.Range("A1:E8000").Value = rs.Range("A1:E8000").Value
          rs.Range("G1:H8000").Value = rs.Range("G1:H8000").Value
          rs.Range("L1:L8000").Value = rs.Range("L1:L8000").Value
        End If
        If rs.Name = "Outsourced PD" Then
          rs.Range("A3:C298").Value = rs.Range("A3:C298").Value
        End If
        If rs.Name = "POV" Then
          rs.Range("D1:E1001").Value = rs.Range("D1:E1001").Value
          rs.Range("U1:U1001").Value = rs.Range("U1:U1001").Value
        End If
      Next rs
   
End Sub

 

[BobBridges]

The confirmation part is easy. Either InputBox or MsgBox will work, but I'll go with the second because the user can reply with a single button push or mouse click rather than picking out text keys. Something like this:

vr = MsgBox("Hit Yes to convert formulae to values.",vbYesNoCancel,"Saving workbook")
Select Case vr
  Case vbYes 'do conversion and save
  Case vbNo 'skip the conversion and save
  Case vbCancel 'cancel save
  Else Stop 'this cannot happen
  End Select