MsOf10 [RESOLVED] Word - Insert TextBox formatted to accept code-VBForums
Results 1 to 9 of 9

Thread: [RESOLVED] Word - Insert TextBox formatted to accept code

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    50

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

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,925

    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.
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    50

    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

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,874

    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

  5. #5

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    50

    Re: Word - Insert TextBox formatted to accept code

    Quote Originally Posted by westconn1 View Post
    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

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,874

    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

  7. #7

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    50

    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

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,874

    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

  9. #9

    Thread Starter
    Member
    Join Date
    Jan 2012
    Posts
    50

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.