Macro for Page Formatting

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Macro for Page Formatting

Post by vilas desai »

Dear Experts,

I have a directory in on my Desktop named CV
I have a file name CV_BFY saved in this directory
CV_BFY has a wsh named same as its file name. wsh = CV_BFY
CV_BFY has merged cells, formulas and a WB macro.

What I need to achieve using a macro which runs when the file is opened.
1. CV_BFY to be page formatted as below and 4 copies to be made in the directory CV with file names CV_BLC, CV_EMG, CV_GLB and CV_PRG
2. Before making the copies,
a. fix the current page margins with no headers and no footers and T/B/L/R margins set as 1.7, 0.5, 1.0 and 0.8 respectively, col widths and row heights, Page Layout: Portrait, Paper Size: Letter.
b. cells which have a content must be un-editable
c. Cells which do not have a content should be editable
d. Fix Row Ht & Col Wd as 15 pixels each along with fixed page range as A1 : AT 67.
e. The work book should have only one worksheet (all other wshs cannot be created.

Attached is a sample wb as explained above
Thanks and best regards
Vilas Desai
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for Page Formatting

Post by HansV »

You don't need a macro for a. and d. - those steps only need to be done once.
For step e. you could protect the workbook for structure. This also needs to be done only once.

I have added a Workbook_Open event procedure for 1. and 2b. and c.

CV_BFY.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for Page Formatting

Post by vilas desai »

Thanks a lot Hans.
I missed out on describing an exception for 2b. Some ranges / cells ex R14, $AQ15: $AQ20, etc, shoud be edited. (Because they have options list and I should be able to change the options in the option List Drop down.)
Best regards
Vilas Desai

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for Page Formatting

Post by HansV »

Change the Worksheet_Change event procedure as follows:

Code: Select all

Private Sub Workbook_Open()
    Dim r As Range
    With Sheet1
        .Protect Password:="secret", UserInterfaceOnly:=True
        .Cells.Locked = False
        On Error Resume Next
        .Cells.SpecialCells(xlCellTypeConstants).Locked = True
        .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
        On Error GoTo 0
        For Each r In .Cells.SpecialCells(xlCellTypeAllValidation)
            If r.MergeArea(1).Address = r.Address Then
                If r.Validation.Type = xlValidateList Then
                    r.MergeArea.Locked = False
                End If
            End If
        Next r
    End With
    Me.SaveCopyAs Filename:=Me.Path & "\CV_BLC.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_EMG.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_GLB.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_PRG.xlsm"
End Sub
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for Page Formatting

Post by vilas desai »

Hello Hans, thanks a lot. I am struggling to combine 4 of my previous topics into one macro. These are Listed below:

1. Dated 26th April, which converts .xls into .xlsm files.
code is as below:

Code: Select all

Sub SaveSheets()
    Dim strPath As String
    Dim wbkS As Workbook
    Dim wsh As Worksheet
    Dim wbkT As Workbook
    Application.ScreenUpdating = False
    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Convert\"
    Set wbkS = ActiveWorkbook
    For Each wsh In wbkS.Worksheets
        wsh.Copy
        Set wbkT = ActiveWorkbook
        wbkT.SaveAs Filename:=strPath & wsh.Name & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        wbkT.Close SaveChanges:=False
    Next wsh
    Application.ScreenUpdating = False
End Sub
2. Macro for List Options dated 3rd July 2021

Code: Select all

Private Sub Workbook_Open()
    Dim wsh As Worksheet
    [b]Dim rng1 As Range[/b]
    Dim rng2 As Range
    Set wsh = ActiveSheet ' instead of Worksheets("PT")
  [b][i]  Set rng1 = wsh.Range("K1")[/i][/b]
  [b][i]  If rng1.Value = "Status" Then[/i][/b]
        Set rng2 = wsh.Range("K21")
        With rng2.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="In Progress,Issued for Review,Issued for Purchase"
            .InputTitle = "Select option"
            .InputMessage = "Please select an item from the list"
            .ErrorTitle = "Incorrect input"
            .ErrorMessage = "You may only select items from the drop down list!"
        End With
        rng2.Value = "In Progress"
[b][i]    End If[/i][/b]
    Set rng1 = wsh.Range("K2")
    If rng1.Value = "Logic" Then
        Set rng2 = wsh.Range("K22")
        With rng2.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="d,e,f"
            .InputTitle = "Select option"
            .InputMessage = "Please select an item from the list"
            .ErrorTitle = "Incorrect input"
            .ErrorMessage = "You may only select items from the drop down list!"
        End With
        rng2.Value = "d"
    End If
End Sub
3. Include superscripts and Special character in Data Validations

Code: Select all

Sub SetDV()
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="°C,m²,cm³"
        .ErrorTitle = "Boo!"
        .ErrorMessage = "Wrong, wrong, wrong!"
    End With
End Sub
4. Macro for page formatting.

Code: Select all

Private Sub Workbook_Open()
    Dim r As Range
    With Sheet1
        .Protect Password:="secret", UserInterfaceOnly:=True
        .Cells.Locked = False
        On Error Resume Next
        .Cells.SpecialCells(xlCellTypeConstants).Locked = True
        .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
        On Error GoTo 0
        For Each r In .Cells.SpecialCells(xlCellTypeAllValidation)
            If r.MergeArea(1).Address = r.Address Then
                If r.Validation.Type = xlValidateList Then
                    r.MergeArea.Locked = False
                End If
            End If
        Next r
    End With
    Me.SaveCopyAs Filename:=Me.Path & "\CV_BLC.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_EMG.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_GLB.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_PRG.xlsm"
End Sub
My problem is I dont understand which code to be placed in which module and where to use Option Explicit.
so when I combine all these 4 topics, I am expecting to achieve
1. Convert xls file int .xlsm files
2. getting List options with superscripts
3. page formatting.

Thanks and best regards
Vilas Desai

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for Page Formatting

Post by HansV »

Option Explicit should be the first line in every module that you use.
Excel will create this line automatically in new modules if you tick the check box "Require variable declaration" in Tools > Options... in the Visual Basic Editor.
You will have to add it manually in already existing modules.

S0587.png

#1 saves every sheet of the active workbook as a separate .xlsm workbook.
#2 and #3 add data validation. You only need to do that once.
#4 locks some cells and saves 4 copies of the active workbook.

Do you really want to combine #1 and #4? You'd create an enormous number of workbooks over time...
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for Page Formatting

Post by vilas desai »

Thanks Hans.
"#2 and #3 add data validation. You only need to do that once."
Yes, actually I think for every new workbook that may be created, validations should be executed, though only once.
So in effect please help me combining all 4 in a macro.
Thanks and best regards
Vilas Desai

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for Page Formatting

Post by HansV »

It makes no sense to me, but here you go. The code should go into the ThisWorkbook module, replacing the existing Workbook_Open procedure.

Code: Select all

Private Sub Workbook_Open()
    Dim rng1 As Range
    Dim rng2 As Range
    Set rng1 = Range("K1")
    Dim r As Range
    Dim strPath As String
    Dim wbkS As Workbook
    Dim wsh As Worksheet
    Dim wbkT As Workbook
    With Sheet1
        .Protect Password:="secret", UserInterfaceOnly:=True
        .Cells.Locked = False
        On Error Resume Next
        .Cells.SpecialCells(xlCellTypeConstants).Locked = True
        .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
        On Error GoTo 0
        For Each r In .Cells.SpecialCells(xlCellTypeAllValidation)
            If r.MergeArea(1).Address = r.Address Then
                If r.Validation.Type = xlValidateList Then
                    r.MergeArea.Locked = False
                End If
            End If
        Next r
    End With
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="°C,m²,cm³"
        .ErrorTitle = "Boo!"
        .ErrorMessage = "Wrong, wrong, wrong!"
    End With
    If rng1.Value = "Status" Then
        Set rng2 = Range("K21")
        With rng2.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="In Progress,Issued for Review,Issued for Purchase"
            .InputTitle = "Select option"
            .InputMessage = "Please select an item from the list"
            .ErrorTitle = "Incorrect input"
            .ErrorMessage = "You may only select items from the drop down list!"
        End With
        rng2.Value = "In Progress"
    End If
    Set rng1 = Range("K2")
    If rng1.Value = "Logic" Then
        Set rng2 = Range("K22")
        With rng2.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="d,e,f"
            .InputTitle = "Select option"
            .InputMessage = "Please select an item from the list"
            .ErrorTitle = "Incorrect input"
            .ErrorMessage = "You may only select items from the drop down list!"
        End With
        rng2.Value = "d"
    End If
    Application.ScreenUpdating = False
    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Convert\"
    Set wbkS = ActiveWorkbook
    For Each wsh In wbkS.Worksheets
        wsh.Copy
        Set wbkT = ActiveWorkbook
        wbkT.SaveAs Filename:=strPath & wsh.Name & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        wbkT.Close SaveChanges:=False
    Next wsh
    Application.ScreenUpdating = False
    Me.SaveCopyAs Filename:=Me.Path & "\CV_BLC.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_EMG.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_GLB.xlsm"
    Me.SaveCopyAs Filename:=Me.Path & "\CV_PRG.xlsm"
End Sub
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Macro for Page Formatting

Post by vilas desai »

Thanks again, Hans
I am sorry Hans. I now understand your advise.
If the macro to lock cells for editing except those with formulas is executed once, it need not be executed again because the cells are already locked
So the same should be true for the other macro also which is used to create desired option list with superscripts. Is this correct?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro for Page Formatting

Post by HansV »

Yes, that is correct.
Best wishes,
Hans