Printing out the code for a VBA module using VBA

3175379

New Member
Joined
Jun 4, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
Hi

Hope that I have posted this in the right place.
Quite a while ago a forum member (Herve) posted the following code

*** start of Herve posting
If you are interested, the code below lets you choose a module/object/userform among the list of available items in your project (generated automatically) or the complete project, and print it.
Long lines are wrapped around, footer indicates module name, page x of x, and date.
The left margin, font, and point size are easily editable in the code.

You will have to create a UserForm (Print_Module_MsgBox) with an ListBox (Module_List), an OptionButton (Complete_Project), and a CommandButton (code: Print_Module_MsgBox.Hide).

Hope it's useful to someone :)

-----------------
Option Explicit


Sub Print_Module_Select()

Dim i As Integer
Dim Module() As String
Dim ModuleToPrint As String
Dim Count As Integer
Dim User_Answer As String
Dim Msg As Integer
Dim Complete_Project As OptionButton
Dim One_Module As OptionButton
Dim Module_List As ListBox

On Error GoTo No_Selection

With Application
.ScreenUpdating = False
.DisplayAlerts = False

'Find the names/number of the various Objects/Forms/Modules in the Project
Count = ThisWorkbook.VBProject.VBComponents.Count
ReDim Module(1 To Count)
For i = 1 To Count
Module(i) = ThisWorkbook.VBProject.VBComponents(i).Name
Next i

'Load the list box with the various modules for selection
With Print_Module_MsgBox.Module_List
.Clear
For i = 1 To Count
.AddItem Module(i)
Next i
End With

'User to select which module(s) to print
Print_Module_MsgBox.Show vbModal

If Print_Module_MsgBox.Complete_Project Then 'Print complete project
Unload Print_Module_MsgBox
Msg = MsgBox("Are you sure you want to print the whole project?", vbCritical + vbYesNo, "It's a lot of paper!")
If Msg = 7 Then ' Ooops!
Exit Sub
End If
For i = 1 To Count
ModuleToPrint = Module(i)
Call Print_Module_Print(ModuleToPrint)
Next i
Else 'Print single module
ModuleToPrint = Print_Module_MsgBox.Module_List.Value
Unload Print_Module_MsgBox
Call Print_Module_Print(ModuleToPrint)
End If

.DisplayAlerts = True
.ScreenUpdating = True

End With
Exit Sub

No_Selection:

Msg = MsgBox("You must select a module or select 'Print Complete Project' option.", vbCritical + vbOKOnly, "No Selection!")
Unload Print_Module_MsgBox

End Sub

Sub Print_Module_Print(ModuleToPrint)

ThisWorkbook.VBProject.VBComponents(ModuleToPrint).Export ModuleToPrint & ".txt"
Workbooks.OpenText ModuleToPrint & ".txt", DataType:=xlDelimited
Columns("A:A").ColumnWidth = 90
Columns("A:A").Select
With Selection
.WrapText = True
.Font.Name = "Courier"
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(1.5)
.LeftFooter = "&F"
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
End With

With ActiveSheet.Cells.PrintOut
End With

ActiveWorkbook.Close
Kill ModuleToPrint & ".txt"

End Sub

*** end of Herve posting

I was wondering if anyone could help me at all.
I am an absolute complete novice but willing to learn. I would like to get this working so that I could print out some of the coding for some spreadsheets that I have been given.
This would enable me to better understand what is going on within them.
The code above states "You will have to create a UserForm (Print_Module_MsgBox) with an ListBox (Module_List), an OptionButton (Complete_Project), and a CommandButton (code: Print_Module_MsgBox.Hide)."
I have tried to do this and added two OptionButtons as in the code it had one listed for Complete Project and other for One Module, if I understood it correctly.
I have added several jpgs showing where I am at at the moment and now I am just stuck with my wheels spinning.

User_Form_001.jpg User_Form_002.jpg User_Form_003.jpg

where do I go from here?
any help would be greatly appreciated.

Thanks
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
Welcome!

Can you give File → Print from the VBA code editor a try? Otherwise, why do you need to use VBA?
 

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
174
I want to easily leave modules in " *.Bas" files becuase I often need to read them into other workbooks.
 

Forum statistics

Threads
1,141,606
Messages
5,707,360
Members
421,503
Latest member
Rickys03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top