Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Seymour, Connecticut, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto_Open Macro Problem

    I use an Auto_Open macro to display a message box when a small database file i have created opens. However, I also want to use Auto_Open to create and format the file instead of opening the "master file" and running the macro to create the file manually. What type of code would I have to add to the module that contains the message displaying auto_open macro so that it doesn't open when I create the file but opens only when the file is opened to be used. I'm using Windows 97.

    Thanks in advance.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    Could you incorporate option buttons or get some input from the person opening the file as to what action is required, and then branch the macro accordingly.

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    From the top of my head, try using the fact that a created and unsaved workbook does not know "where it is". Put this code in the ThisWorkbook level, Microsoft does not recommend to use Auto_Open macros anymore. The On Error is necessary as otherwise Sub will crash, as VBA, as usually, does not handle undefined situations well.

    Perhaps there is a much better solution, therefore please wait a bit.

    Private Sub Workbook_Open()
    On Error GoTo BailOut
    If ActiveWorkbook.Path <> "" Then
    MsgBox "Hello"
    End If
    BailOut:
    End Sub

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    One point about using On Error. An On Error routine should always be exited with a Resume command. Otherwise, VBA does not get out of interrupt mode, and a number of things will not work properly. The best way to do what you want is:

    <pre>Private Sub Workbook_Open()
    Dim strPath
    On Error Resume Next
    strPath = ActiveWorkbook.Path
    On Error Go To 0
    If strPath <> "" Then
    MsgBox "Hello"
    End If
    End Sub
    </pre>


    However, in this case it should not be necessary to use On Error at all. ActiveWorkbook.Path should be defined as a null string if the workbook has not been saved, and the comparison should work as desired.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    If your file is an Excel Template, then the code that cri gave you should work for what you want to do. However, from your message, it does not sould like you are using a template to create the new files. If not, then you will need to use another method since your file has been saved and therefore has a path. There are several things that you can do in this case:

    1- If the file you are using to create your files from always has the same name, and always is located in the same directory, then you can compare the file's name and path to see which file is being opened.

    2- You can pick an unused cell in a worksheet (a hidden worksheet created just for this purpose if you want), and have the Workbook_Open code check to see if that cell contains something that the code put there to indicate that this is not the first open.

    There are probably some other things that you could do if neither of those work for you.
    Legare Coleman

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Seymour, Connecticut, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    Legare, Thanks for the ideas. Being a newbie and poorly versed in VBA I'll have to break out my "For Dummies" books asap. Another fly in the ointment is that the macro I recorded is in the this workbook section of the excel file i open (using workbook_open not auto_open) to create the database that i save to another drive. If i or another user opens the "saved" file the "creation" macro (which copies itself to the "saved" file) runs again. Can the "creation" macro be modified to deactivate itself in the "saved" macro without being deactivated in its original location? I've tried some suggestions outlined in "for dummies" books but i guess i'm to big a dummy.

    Thanks in advance

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    Yes, the methods that I described do exactly what you are describing. The Workbook_Open is what you want to use, Auto_Open is the old method and one day may stop working. If your macro creates the database on Sheet1, and always puts something into cell A1, and Cell A1 is always empty before your macro runs (Sheet1 and A1 can be any sheet and any cell on that sheet), then the VBA code would look something like this:

    <pre>Private Sub Workbook_Open()
    If Sheet1.Range("A1") = "" then
    'Your code to build the database goes here.
    End If
    End Sub
    </pre>

    Legare Coleman

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Seymour, Connecticut, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    Legare, Thanks for your patience. Your help is greatly appreciated.

    I think what I want to do can't be done with a macro. What i wanted was to be able to run my macro like using the right click button to print a word document. I wanted to left click(or right click) the form containing the macro and have it open, enable the macro, run, and then close itself. From this experience it seems thats not possible.

    Thanks again

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    I am not sure of what you mean by "the form containing the macro." VBA code (macros) are in modules, not forms. Forms contain objects. If you are talking about an Excel user form, then the user form can contain a button which would execute the macro. If by form you mean an Excel spreadsheet that has been formatted for your application, then there are several things that you can do. You can add a button to the sheet that will run the macro for you. More difficult to do, you can modify the context menu that you get when you right click on a cell to add a command that would execute your macro. You can also use the Worksheet Change event to execute you code anytime a cell on the sheet is changed. There are probably some other options depending on exactly what you are trying to do.
    Legare Coleman

  10. #10
    Lounger
    Join Date
    Jan 2001
    Location
    Seymour, Connecticut, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    Legare, I think modifying the context menu is the solution I am looking for. How can this be done?

    Thanks in advance

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto_Open Macro Problem

    The following is hashed together from several different routines and has not been tested, it is meant fro illustrative purposes only. I don't have any code readily handy that does exactly what you are asking.

    <pre>Dim MenuControl As Object
    Dim SCMenu As CommandBar
    Set SCMenu = CommandBars("Cell")
    Set MenuControl = SCMenu.Controls.Add(Type:=msoControlButton)
    MenuControl.Caption = "Do My Stuff"
    MenuControl.BeginGroup = True
    MenuControl.Enabled = True
    MenuControl.Visible = True
    MenuControl.OnAction = "DoMyStuff"
    </pre>


    The code above that adds the control to the command to the context menu was copied from a routine that was adding a command button to a command bar, so I don't know if it is exactly appropriate for the context menu. I also seem to remember that there are two different context menues named "Cell", and I don't remember what the difference is or how you get the one that you want. I think you have to loop through the CommandBars assigning each to an object variable, and then either modify both or just the one that you want.

    You will also have to have some code to remove what you add to the context menu. You will have to find the appropriate palace to put this code, but the workbook activate and deactivate event routines might be a good place.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •