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
Macro for Page Formatting
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Macro for Page Formatting
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for Page Formatting
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for Page Formatting
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
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
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for Page Formatting
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
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for Page Formatting
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:
2. Macro for List Options dated 3rd July 2021
3. Include superscripts and Special character in Data Validations
4. Macro for page formatting.
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
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
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
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
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
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
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for Page Formatting
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.
#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...
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.
#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
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for Page Formatting
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
"#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
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for Page Formatting
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
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Macro for Page Formatting
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?
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?
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands