-
Apr 7th, 2012, 11:21 AM
#1
Thread Starter
Member
[RESOLVED] Word - Insert TextBox formatted to accept code
I'm trying to build a document containing my current working macros in Word so that I can just open it up, find the code I want and copy and paste it to where it is needed.
The problem I'm having is that some of my code is a little extensive. See below my custom event code for forcing a user to enable macros when using a file.
This code for instance spans about 3/4 pages of the Word document and is therefore causing a bit of a problem when navigating that particular section.
What I would really like is a way of creating a code box automatically inside the Word document, preferably at the place where my cursor is currently.
The workings of the code box would hopefully be very similar to using [CODE] tags on this forum: grey background, fixed-width font and horizontal and vertical scroll bars.
The scroll bars are a must to help cut down on the space.
Can anybody help with this?
Here is the long code sample as mentioned above, in case you want an idea of the size of text I am looking to include:
Code:
Option Explicit
'Set to name of sheet to be shown when macros are disabled
Const strMacroSplash As String = "Macros Disabled"
Sub prCust_WbOpen()
With Application
.EnableCancelKey = xlDisabled 'So that user cannot cancel the macro
.ScreenUpdating = False 'Stop screen flashing
'Show all sheets except the macros are disabled sheet
Call prShowShts
'Set to True so that the 'Are you sure...' prompt does not show straight after being opened
ThisWorkbook.Saved = True
.ScreenUpdating = True
.EnableCancelKey = xlInterrupt 'Reset application settings
End With
End Sub
Sub prCust_WbBeforeClose(Cancel As Boolean)
Dim WB As Workbook
Dim lngCount As Long
With Application
.EnableEvents = False
.EnableCancelKey = xlDisabled
End With
With ThisWorkbook
'If file has had changes made since the last save...
If Not .Saved Then
'Emulate normal 'Are you sure you want to save?' prompt
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'If Yes to save call our custom save procedure
Call prCust_SaveUI
Case Is = vbNo
'If No, we do not need to do anything
Case Is = vbCancel
'If Cancel, set the variable Cancel to True so that file is not closed.
Cancel = True
End Select
End If
If Cancel = False Then
'If Cancel is False we can set the Saved property to True, as we know it is either saved via
'the custom save procedure or does not need to be.
'This means that the 'Are you sure...' prompt will not show up any more.
.Saved = True
End If
End With
clean_up:
With Application
.EnableCancelKey = xlInterrupt
.EnableEvents = True
End With
If Cancel = False Then
'Loop through all currently open Workbooks
For Each WB In Workbooks
'If the Workbook is not this Workbook or the 'PERSONAL' Workbook...
If WB.Name <> ThisWorkbook.Name And (UCase(WB.Name) <> "PERSONAL.XLS" Or UCase(WB.Name) <> "PERSONAL.XLSB") Then
'Add 1 to our count
lngCount = lngCount + 1
End If
Next WB
'If the count of Workbooks is 0 or, put another way, if this Workbook is the only one open...
If lngCount = 0 Then
'Quit the application. Avoids leaving a blank Excel Application behind.
Application.Quit
End If
End If
End Sub
Sub prCust_WbBeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
'Call our custom save procedure, passing on whether it is SaveAs or just Save
Call prCust_SaveUI(SaveAsUI)
'Set Cancel to True to cancel the normal Excel save procedure
Cancel = True
Application.EnableEvents = True
End Sub
Private Sub prCust_SaveUI(Optional bSaveAs As Boolean = False)
Dim ws As Worksheet
Dim wsAct As Worksheet
Dim strFName As String
Dim bSaved As Boolean
Application.ScreenUpdating = False
'Keep a reference to the active sheet so that we can reselect it later on
Set wsAct = ActiveSheet
'Hide all sheets except the macros disabled sheet
Call prHideShts
'If the save is a SaveAs Type, show the SaveAs dialog for these file types
'Since this is a macro enabled file, make '.xlsm' the default selection (FilterIndex part)
If bSaveAs = True Then
strFName = Application.GetSaveAsFilename( _
fileFilter:="Excel 97-2003 Workbook (*.xls), *.xls," & _
"Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", _
FilterIndex:=2)
If Not strFName = "False" Then
'If Cancel wasn't clicked SaveAs the Workbook, and set our variable to True
'The variable is used later on to check if the file was actually saved,
'since there is a chance for the user to cancel
ThisWorkbook.SaveAs strFName
bSaved = True
End If
Else
'If not SaveAs, just Save the Workbook normally. Also sets the variable to True
ThisWorkbook.Save
bSaved = True
End If
'Show all sheets except the macros are disabled sheet again,
'and activate the previous ActiveSheet
Call prShowShts
wsAct.Activate
'If a save occurred the Saved property will be set to True, so we do not get the
' "Are you sure?" prompt
ThisWorkbook.Saved = bSaved
Application.ScreenUpdating = True
End Sub
Private Sub prHideShts()
Dim ws As Worksheet
'Make the macros are disabled sheet visible
'(uses the constant variable defined at the top of this code)
Worksheets(strMacroSplash).Visible = xlSheetVisible
'Loop through all Worksheets and hide them if they are not the macros are disabled sheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = strMacroSplash Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
'Make sure the macros are disabled sheet is active
Worksheets(strMacroSplash).Activate
End Sub
Private Sub prShowShts()
Const strH As String = "Hidden"
Dim ws As Worksheet
'Loop through all Worksheets and show them if they are not the macros are disabled sheet
'Do not show them if they start with the string contained in the variable strH
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = strMacroSplash And Not Left(ws.Name, Len(strH)) = strH Then
ws.Visible = xlSheetVisible
End If
Next ws
'Hide the macros are disabled sheet
Worksheets(strMacroSplash).Visible = xlSheetVeryHidden
End Sub
Thanks in advance for any suggestions!
Please test any VBA code I suggest in a copy of your file. If the code errors or deletes your data it is not able to be undone.
Home: Mac Book Pro | Snow Leopard | Excel for Mac 2011
Home: Windows 7 | MS Office 2010 (Running on Parallels Desktop as a VM)
Work: Windows 7 | MS Office 2010
-
Apr 8th, 2012, 08:56 PM
#2
Re: Word - Insert TextBox formatted to accept code
>>> I'm trying to build a document containing my current working macros in Word so that I can just open it up, find the code I want and copy and paste it to where it is needed.
Are you just trying to in a way manage code (sort of creating a database for your self?)
If yes then you might want to download this? I use it almost on a daily basis.
Topic: Handy Code Viewer
Link: http://www.experts-exchange.com/Prog...de-Viewer.html
Last edited by Siddharth Rout; Apr 8th, 2012 at 09:00 PM.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Apr 14th, 2012, 11:58 AM
#3
Thread Starter
Member
Re: Word - Insert TextBox formatted to accept code
It's exactly what I'm trying to do
It looks nice and seems to do exactly what I'd want, I'll just have to see how the work security filters handle it. There have been times when I can't use an .exe file without needing an admin password - something I obviously don't have.
Looking at the options I had I went with Word as the best - however it doesn't really handle large code blocks well.
2 Questions:
How does this program handle code that would need to go in 2 places?
E.g. I have some code that needs to go into the ThisWorkbook area and some that needs to be in a standard module. The code works together to do something specific.
I also like to include a fair amount of explanation as to how the code works, what it does and in some places a step by step on how to use the code.
This helps when keeping it in line for things like posting as solutions on the forums, passing on to colleagues who ask if it's possible (can just pass on the step by step without having to do much work).
Is this type of thing possible with the program?
Please test any VBA code I suggest in a copy of your file. If the code errors or deletes your data it is not able to be undone.
Home: Mac Book Pro | Snow Leopard | Excel for Mac 2011
Home: Windows 7 | MS Office 2010 (Running on Parallels Desktop as a VM)
Work: Windows 7 | MS Office 2010
-
Apr 15th, 2012, 05:15 AM
#4
Re: Word - Insert TextBox formatted to accept code
there is a submission in codebank (vb6) for formatting code, similar to the vba ide, into a richtextbox, i would assume you could insert a rtf document(s) into your word document at any position, with the code formatted into rich text
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 15th, 2012, 10:32 AM
#5
Thread Starter
Member
Re: Word - Insert TextBox formatted to accept code
Originally Posted by westconn1
there is a submission in codebank (vb6) for formatting code, similar to the vba ide, into a richtextbox, i would assume you could insert a rtf document(s) into your word document at any position, with the code formatted into rich text
Thanks, I'm not having much look finding it but will try digging deeper
Please test any VBA code I suggest in a copy of your file. If the code errors or deletes your data it is not able to be undone.
Home: Mac Book Pro | Snow Leopard | Excel for Mac 2011
Home: Windows 7 | MS Office 2010 (Running on Parallels Desktop as a VM)
Work: Windows 7 | MS Office 2010
-
Apr 16th, 2012, 05:59 AM
#6
Re: Word - Insert TextBox formatted to accept code
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 16th, 2012, 06:00 PM
#7
Thread Starter
Member
Re: Word - Insert TextBox formatted to accept code
Not sure if I'm doing something wrong but when I go to that link and download the file there is no .exe for me to use?
Anyway Koolsid's suggestion seems to work ok on the work computers so I'm probably going to use that from now on.
Thanks both for the answers!
Please test any VBA code I suggest in a copy of your file. If the code errors or deletes your data it is not able to be undone.
Home: Mac Book Pro | Snow Leopard | Excel for Mac 2011
Home: Windows 7 | MS Office 2010 (Running on Parallels Desktop as a VM)
Work: Windows 7 | MS Office 2010
-
Apr 17th, 2012, 04:40 PM
#8
Re: Word - Insert TextBox formatted to accept code
there is no .exe for me to use?
not supposed to post exes!!
i would believe you can use the code in VBA, may have to find and download the richtextbox ocx
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 17th, 2012, 05:16 PM
#9
Thread Starter
Member
Re: Word - Insert TextBox formatted to accept code
There is potential to use the code in VBA, but I really feel out of my depth even looking at the code.
I haven't done any VB6 work before, and so far can only get the files to open in Notepad++
I think I'm supposed to be able to open a window on screen to paste my code into then format it, but can't even get that!
Also not sure what you mean by download the richtextbox ocx...
I do have Visual Studio 2008 installed if that's any help
Please test any VBA code I suggest in a copy of your file. If the code errors or deletes your data it is not able to be undone.
Home: Mac Book Pro | Snow Leopard | Excel for Mac 2011
Home: Windows 7 | MS Office 2010 (Running on Parallels Desktop as a VM)
Work: Windows 7 | MS Office 2010
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|