MrExcel Publishing
Your One Stop for Excel Tips & Solutions

REPOST: NEED VB CODE TO AUTO END AND AUTO ENABLE MACRO.!!!


Posted by MILAN on September 21, 2001 6:38 AM

: Subject:macro named auto_open.


Posted by Ben O. on September 21, 2001 7:08 AM

You can't bypass the Enable / Disable dialog box with a macro since the macro will never run unless the user presses enable. You would have to change their security level in Excel to Low, which will enable macros automatically.

To have it display the Open dialog box, put this in your code:

x = Application.GetOpenFilename("Excel Spreadsheets (*.xls),*.xls", , "Open File")
Workbooks.Open(Filename:=x).Activate


-Ben

Posted by MILAN on September 21, 2001 7:28 AM

__________________________________________________________________________
Ben
I don't want to bypass, I want to the macro to choose enable.
The Open box already displays. If the user selects cancel in the Open box
and when the end or debug box appears I want the macro to
automatically choose end. Sorry if I was not clear on what I
needed. - Milan
__________________________________________________________________________

Posted by Ben O. on September 21, 2001 7:56 AM

This code will make it so that if the user hits "Cancel" it will exit the macro and not go to the VBA editor or offer the option to debug.

x = Application.GetOpenFilename("Excel Spreadsheets (*.xls),*.xls", , "Open File")
If x = False Then
Exit Sub
End If
Workbooks.Open(Filename:=x).Activate

As for having a macro select "Enable" on the Enable/Disable macros dialog box--it can't be done, since no macro can run until macros are enabled.

-Ben

Posted by MILAN on September 21, 2001 8:21 AM

Re: REPOST: Good Job Ben Thanks

: __________________________________________________________________________ : Ben : I don't want to bypass, I want to the macro to choose enable. : The Open box already displays. If the user selects cancel in the Open box : and when the end or debug box appears I want the macro to : automatically choose end. Sorry if I was not clear on what I : needed. - Milan : __________________________________________________________________________

Posted by MILAN on September 21, 2001 10:40 AM

Re: REPOST: OOPS! Ben Not quite but almost. Help.

__________________________________________________________________________________________________
Ben
The Open will not open the chosen file. I have tried to tweek your code but I am missing something.
My code prior to the BEN modification .
fileToOpen = Application.GetOpenFilename("text file (*.txt)
If fileToOpen <> False Then
MsgBox "Open" & fileToOpen

Workbooks.OpenText FileName:=fileToOpen, Origin:=xlWindows

Yours x = Application.GetOpenFilename("Excel Spreadsheets (*.xls),*.xls", , "Open File") End If Workbooks.Open(Filename:=x).Activate
____________________________________________________________________________________________________

This code will make it so that if the user hits "Cancel" it will exit the macro and not go to the VBA editor or offer the option to debug.

: __________________________________________________________________________ : Ben : I don't want to bypass, I want to the macro to choose enable. : The Open box already displays. If the user selects cancel in the Open box : and when the end or debug box appears I want the macro to : automatically choose end. Sorry if I was not clear on what I : needed. - Milan : __________________________________________________________________________

Posted by MILAN on September 21, 2001 10:42 AM

Re: REPOST: OOPS! Ben Not quite but almost. Help.

__________________________________________________________________________________________________
Ben
The Open will not open the chosen file. I have tried to tweek your code but I am missing something.
My code prior to the BEN modification .
fileToOpen = Application.GetOpenFilename("text files (*.txt), *.txt")
If fileToOpen <> False Then
MsgBox "Open" & fileToOpen

Workbooks.OpenText FileName:=fileToOpen, Origin:=xlWindows

Yours x = Application.GetOpenFilename("Excel Spreadsheets (*.xls),*.xls", , "Open File") End If Workbooks.Open(Filename:=x).Activate
____________________________________________________________________________________________________

This code will make it so that if the user hits "Cancel" it will exit the macro and not go to the VBA editor or offer the option to debug.

: __________________________________________________________________________ : Ben : I don't want to bypass, I want to the macro to choose enable. : The Open box already displays. If the user selects cancel in the Open box : and when the end or debug box appears I want the macro to : automatically choose end. Sorry if I was not clear on what I : needed. - Milan : __________________________________________________________________________

Posted by Ben O. on September 21, 2001 12:05 PM

Re: REPOST: OOPS! Ben Not quite but almost. Help.

Milan,

The only thing I changed was added and End If to your code. Try this, it works for me:

Sub Auto_Open()

fileToOpen = Application.GetOpenFilename("text files (*.txt), *.txt")
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows
End If

End Sub

-Ben O.


__________________________________________________
_______________________________________________ The Open will not open the chosen file. I have tried to tweek your code but I am missing something. My code prior to the BEN modification . fileToOpen = Application.GetOpenFilename("text files (*.txt), *.txt") If fileToOpen <> False Then MsgBox "Open" & fileToOpen Workbooks.OpenText FileName:=fileToOpen, Origin:=xlWindows Yours : x = Application.GetOpenFilename("Excel Spreadsheets (*.xls),*.xls", , "Open File") : If x = False Then : Exit Sub : End If : Workbooks.Open(Filename:=x).Activate __________________________