none
Macro Question RRS feed

  • Question

  • I am using Excel with a macro to build to build an Excel document that I then save as a new document. Is there a way to add code to my macro to have a message box pop up whenever the new file is opened?
    Friday, November 11, 2016 1:45 PM

All replies

  • Hi,

    Since your question is Macro related, I'm moving it to our dedicated Excel for Developer forum, there you should get more professional responses:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Regards,

    Ethan Hua


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, November 14, 2016 5:39 AM
  • Hi,

    >>Is there a way to add code to my macro to have a message box pop up whenever the new file is opened?

    Add reference "Microsoft Visual Basics for Applications Extensibility X" firstly, then you could use  AddFromString Method (VBA Add-In Object Model) to add macro text in the module.

    E.g.

    Sub test()
    Dim text As String
    Dim wb As Workbook
    Dim VBP As VBProject
    Dim VBM As VBComponent
    Dim VBModule As CodeModule
    Dim VBProc As VBComponent
    Set wb = Workbooks.Add
    text = "Private Sub Workbook_Open()" & vbCrLf & "Msgbox ""test""" & vbCrLf & "End Sub"
    Set VBP = Workbooks(wb.Name).VBProject
    Set VBModule = VBP.VBComponents.Item("ThisWorkbook").CodeModule
    VBModule.AddFromString (text)
    wb.SaveAs "test", xlOpenXMLWorkbookMacroEnabled
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MDevola Tuesday, November 15, 2016 4:56 PM
    • Unmarked as answer by MDevola Tuesday, November 15, 2016 4:56 PM
    • Proposed as answer by Chenchen LiModerator Friday, November 18, 2016 9:23 AM
    Tuesday, November 15, 2016 6:01 AM
    Moderator
  • I tried adding the code to my macro and could not get it to work. Dim VBP As VBProject - is giving error as undefined. Below is my code for saving a new workbook with the sheets I need. I have used code that gave me the message box, but only when Wb is first created.

    I appreciate any help you may be able to provide.

     Dim wbkNew As Workbook
        Dim wbkCurrent As Workbook
     Set wbkCurrent = ThisWorkbook
     'add and create a reference to new workbook
            Set wbkNew = Workbooks.Add
        'copy sheets across
        wbkCurrent.Sheets(Array("MHR", "Inspection Sheet", "Revision History")).Copy Before:=wbkNew.Sheets(1)
     'suppress delete warning message
      Application.DisplayAlerts = False
         'delete sheets
     wbkNew.Sheets("Sheet1").Delete
        Application.DisplayAlerts = True
           Dim sh As Worksheet
            Dim myPassword As String
                myPassword = "document"

    For Each sh In ActiveWorkbook.Worksheets
                sh.Protect Password:=myPassword, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowFormattingCells:=True
                  Next sh

    'save new workbook
         wbkNew.SaveAs Filename:="Z:\Public-30_Day_Limit\MHR (In Process)\" & Itemno & "(Rev " & MHRRev & ") " & Item & ".xlsx"
           End Sub

    Tuesday, November 15, 2016 5:07 PM
  •  

    Hi,

    >>Dim VBP As VBProject - is giving error as undefined.

    You need to add reference: Microsoft Visual Basics for Applications Extensibility X


     

    Note you need to save the file as MacroEnabled file

    Like: wb.SaveAs "test", xlOpenXMLWorkbookMacroEnabled

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 16, 2016 7:07 AM
    Moderator