VBA to Convert Selected Pages to Values Only in separate workbook

jgamse

New Member
Joined
Mar 17, 2014
Messages
2
Good morning! I have this amazing bit of VBA that allows me to select pages and print in a proper order & aligns the page numbers. I want to keep this, but add an option to convert those pages to values & formats only instance of excel. Either by creating a copy of the pages into a new workbook (preferred) that is macro & formula free .... or deleting all tabs not selected and removing all formulas & macros. Also, all of the pages are typically locked, this would of course be unlocked and stay that way.

Note: The private subs i have to un-protect all sheets is named UnprotectAll or for specific sheet is UnprotectSheet and conversely ProtectAll & ProtectSheet

BONUS: Once complete, if able to make the document name from cells on a specific worksheet (worksheet titled START_HERE cells named PROJ_TYPE &" "& PROJ_NUM + today's date would be amazing!

Unfortunately due to the nature of the document I can not post the document on here.

Here is the lovely piece of VBA I have liberated & modified from others that is working very well for printing:

Code:
Sub SelectAnyAndAction()
    Dim i As Integer
    Dim TopPos As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    
'   Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If
    Application.ScreenUpdating = False
    
'   Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add


    SheetCount = 0


'   Add the checkboxes


    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
'       Skip empty sheets and hidden sheets
        If Application.CountA(CurrentSheet.Cells) <> 0 And _
            CurrentSheet.Visible Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                PrintDlg.CheckBoxes(SheetCount).Text = _
                    CurrentSheet.Name
            TopPos = TopPos + 13
        End If
    Next i


'   Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240


'   Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        If Range("QJ_ActionSelect").Value = 1.5 Then
            .Caption = "Select sheet"
        Else
            .Caption = "Select sheets to print"
        End If
    End With


'   Change tab order of OK and Cancel buttons
'   so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront


'   Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    
    
    If Sheets("START_HERE").Range("QJ_ActionSelect") = ("2") Then
        If SheetCount <> 0 Then
            If PrintDlg.Show Then
                      For Each cb In PrintDlg.CheckBoxes
                          If cb.Value = xlOn Then
                              Worksheets(cb.Caption).Select Replace:=False
                          End If
                      Next cb
                      ActiveWindow.SelectedSheets.PrintOut Copies:=1
                      ActiveSheet.Select
              End If
        
        Else
            MsgBox "All worksheets are empty."
        End If
        '   Delete temporary dialog sheet (without a warning)
            Application.DisplayAlerts = False
            PrintDlg.Delete
              
        '   Reactivate original sheet
            Sheets("START_HERE").Activate
    
    Else
    If Sheets("START_HERE").Range("QJ_ActionSelect") = ("1.5") Then
    
        If SheetCount <> 0 Then
        If PrintDlg.Show Then
                  For Each cb In PrintDlg.CheckBoxes
                      If cb.Value = xlOn Then
                          Worksheets(cb.Caption).Select Replace:=False
                      End If
                  Next cb
                  ActiveWindow.SelectedSheets.Select
                  
          End If
    
        Else
            MsgBox "Select Valid Specific Worksheet"
        End If
    
    '   Delete temporary dialog sheet (without a warning)
        Application.DisplayAlerts = False
        PrintDlg.Delete


    End If
    End If
End Sub
THANKS AGAIN TO ALL FOR YOUR ASSISTANCE! The last time I attempted to change this to function myself, let's just say it did not go well....
Jason.
 

Forum statistics

Threads
1,082,629
Messages
5,366,645
Members
400,908
Latest member
currong

Some videos you may like

This Week's Hot Topics

Top