[RESOLVED] Word - Insert TextBox formatted to accept code

[AD_Taylor]

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:

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!

[koolsid]

>>> 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

[AD_Taylor]

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?

[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

[AD_Taylor]

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

[westconn1]

http://www.vbforums.com/showthread.p...ight=rich+text

[AD_Taylor]

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!

[westconn1]

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

[AD_Taylor]

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