Solved

how to write a VBA code to enable macro in Excel?

Posted on 2000-04-26
33
9,366 Views
Last Modified: 2010-04-16
I wanted to write a VBA code that will triggered to enable the macro and don't let the user to choose whether to enable or disable when prompted at the startup of the file. This is bcoz my xl file will only work wif macro enabled as I have written my codes there. Thanks
0
Comment
Question by:holmium
  • 7
  • 7
  • 6
  • +4
33 Comments
 
LVL 13

Expert Comment

by:cri
ID: 2751960
Unless you are an avid PAQ buyer you must have approx. 1500 points left. Therefore, please observe the E-E value system.
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2753548
I also doubt whether this is possible as it's an anti-virus feature: if you were able to turn off the warning, then every virus would use the same code.
0
 
LVL 9

Expert Comment

by:antrat
ID: 2754204
Hmm, Noggy maybe this is what holmium has got in mind :)

holmium, there is workaround to your problem, but with 1500 points to spare and only offering 20 well...Good luck


antrat
0
 
LVL 1

Author Comment

by:holmium
ID: 2755166
Adjusted points from 20 to 300
0
 
LVL 1

Author Comment

by:holmium
ID: 2755167
So sorry everyone...I actually don't how many points should I gave...that's why I only offer 20 points for mayb a line of code...Antrat, I really wanted the solution if you really have one and I never intended to be that silly offering 20 points if I know there's actually a guideline abt points...I m willing to give many point as much as u want if you could solve my problems... Indeed I don't need much points as I very seldom asking a question...so my adjustment to 300 points, issit enuff? If not could someone pls let me know how much should I give or where chould I refer to the points guideline in E-E.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2755413
Hi Holmium, 300 pts is surely enough, but I still would answer you this is not possible, except if you put the file/workbook in the Xlstart diectory (C:\Program Files\Microsoft Office\Office\Xlstart\)

But you'll have to do this yourself for every user who uses your files.

Maybe Antrat (he seems to do) has a better solution.

Calacuccia

0
 
LVL 13

Expert Comment

by:cri
ID: 2755977
holmium, your _average_ point assignments are sensible...>8-P

Excerpt of

http://www1.experts-exchange.com/info/howto.htm#101

How does the point system work?

You assign the point value of the question based on its difficulty. As a guide, a basic question is worth 50 points; an intermediate question is 100 points, and an advanced question is 200 points. The more points assigned to a question, the more likely it will be answered.

0
 
LVL 23

Expert Comment

by:ahammar
ID: 2755993
Hi all,
I'm making a first in a long time appearance here, but here it goes.....

Although I'm not sure if this will work, I think it will.
Here is a possibility that is not very practical, so it may or may not work for you.
I tried this before a long time ago, and I think it worked (but I can't remember for sure), but it requires that you put your code in the personal.xls file on everyones computer instead of the actual file you are writing. Since personal.xls always opens by default, maybe it will work ok for you.
You will have to write your code in the open event of everyones Personal.xls, and have it open the actual file you want openned, and then runs any auto macros that are in that file. Something like this:

    Workbooks.Open(FileName:= _
        "C:\My Documents\Work Programs\Picalc\99Plus\99+Picalc5g.xls").RunAutoMacros _
        Which:=xlAutoOpen

I think that will work, but if it doesn't work exactly that way, then you will have to use code in the Personal.xls file to open the file you want, and write all the code in Personal.xls to run on the file you open.
I hope all that makes sense.


I know it's not a good solution so maybe calacuccia's solution is better, or maybe Antrat's is better yet, but I'm just trying to offer another solution so you can choose what best fits your needs (just in case mine does work........:-)).

If I'm wrong with the way this works, I'm sure someone will tell me so.

Cheers!
ahammar

0
 
LVL 23

Expert Comment

by:ahammar
ID: 2756022
Me again,
Just thought I'd mention that with this solution, all this will happen everytime you open Excel. After more thinking and checking, I remember that I came up with a way to run code on a file only when you open it (more like normal), but run it from Personal.xls, but it does require that all the code be in the Personal.xls file.
If you think any of this will help you out, let me know, and I'll explain more.

Cheers!
ahammar
0
 
LVL 23

Expert Comment

by:ahammar
ID: 2756048
Ok, I'm becoming a hog now, but I don't think I explained something right in my first comment.
Any code you put in the personal.xls file will run everytime you open excel. I think all you will have to do is put code in there that opens the file you want, and the code in the file you open will then run. I'm not sure though. That's what I meant by if it doesn't work that way, then all your code will have to be in the Personal.xls file, but have it effect the file that opens.

Keep in mind though, that I do have a solution that will work by openning the file like normal, but I know it requires ALL your code to be in Personal.xls.

As much typing as I've done here, I could have checked this all out myself......:-)

Cheers!
ahammar
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2756133
Nice to see you back Ahammar <Waves hello from over the ocean>

Indeed,
The only way to get Excel past the Macro Warning, is to put code in one of the files in the XLStart directory, or in the Alternate Startup File Location (Can be seen & set under Tools/Options/Tab General).

Any file outside those two locations will be subject to the Macro Warning Dialog, if set by the user.

The method Ahammar mentions will also work, that is, call the file from your personal.xls file.

Another method, more generally useful, would be to distribute your file with an installer, which would be a very simple executable .bat which copies the file to the right directory.

To make the .bat, just open notepad, enter these three lines:
Copy "dummy97.xls" "C:\Program Files\Microsoft Office\Office\Xlstart"
Erase "dummy97.xls"
Erase "Install.bat"

and save the file to Install.bat

When sending your application, give instructions, to put all files sent in a temporary directory, and to run install.bat

The .bat file will take care of saving file to correct directory, and will delete all traces of the temporary directory.

Good Luck
Calacuccia
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2757559
Ok, we've got some closet virus programmers here :-)......things that I've done in the past too - though not very practical on a user basis....
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2757567
So you once were in a closet with a virus in the past ?
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2757589
God almighty, there's someone more sad than me - answering questions at this time of night. Cal, I've just come back from a good beano in the pub (again!!) and my keyboard skills are....well, how shall I say.....well up the spout.

PS That took me 50 seconds to type that.....twice if you include the backspaces :-) .
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2757598
;-)

I've just returned from a good game of Squash, and am recuperating before the screen (had some beers, but out of thirst).
0
 
LVL 1

Author Comment

by:holmium
ID: 2757796
hi all
Thanks so much abt all the comments and suggestions....it seemed that my aim to disable the macro is rather complicated issue huh?...hmm...mayb Calacusia idea by using the bat file to put it inside the xlstart folders is a good one as writting a code on personal.xls is rather impossible to do it on user's PC....but I think the user may have difficulty to open up the file as he may need to open it from the xlstart folders....rite?..so Cal, wat u would suggest abt that?....nevermind, I m still anxiously waiting for Antrat's so called "workaround" which he sound so confident to resolve my issue...but as for others, if you do think of anything else, pls do drop a line or two okie?..
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:antrat
ID: 2757815
Ahammer
Good to see you back. Hey! what was that address again :)

holmium
Ahammer's and Calacuccia's solution is similar to what I was going to suggest.

Something like the code below placed in the The workbook_open Event. Of course it does mean the user must enable macros the first time it is opened. It may help the user if you place a big cell comment asking the user to open the workbook without "Disabling Macros". Just don't put the words "Tust Me!" in it :)

Option Explicit

Private Sub Workbook_Open()
Application.DisplayAlerts = False
If ThisWorkbook.FullName <> "C:\Program Files\Microsoft" _
     & " Office\Office\Xlstart\MyBook.xls" Then
     
 ThisWorkbook.SaveAs ("C:\Program Files\Microsoft" _
 & " Office\Office\Xlstart\MyBook1.xls")
  Workbooks("MyBook").Close SaveChanges:=False
    Kill ("C:\MyDocuments\MyFiles\MyBook")
     ThisWorkbook.SaveAs ("C:\Program Files\Microsoft" _
     & " Office\Office\Xlstart\MyBook.xls")
     
MsgBox "Thisworkbook has no been saved as: " _
        & Chr(13) & ThisWorkbook.FullName
Application.DisplayAlerts = True
End If
End Sub

Good luck
antrat
0
 
LVL 9

Expert Comment

by:antrat
ID: 2757832
Ok, now I feel stupid. Sorry holmium I didn't mean to get hopes up. Maybe if you could tell us what sort of enviroment you are working in i.e Work etc

antrat
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2758699
Holmium,

If the file is inside the XLStart folder, the file will be opened automatically with Excel, although it will be hidden.

In the file you could add some code to get a toolbar, though.

Put this part in the Workbook_Open of your application Workbook:

Private Sub Workbook_Open()
ActiveWindow.Visible = False
Set MyCtrl = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:= _
        msoControlButton, Before:=11)
    MyCtrl.Caption = "LaunchMyApplication"
    MyCtrl.Style = msoButtonCaption
    MyCtrl.OnAction = "ShowIt"
End Sub

and add a public module Show It somewhere

Public Sub ShowIt()
Windows(ThisWorkbook.Name).Visible = True
End Sub

Now Excel will start with an extra button in the menu bar, to make your application visible for users.

Hope this Helps
Calacuccia
0
 
LVL 23

Expert Comment

by:ahammar
ID: 2760385
Hi everyone again!
Thanks for all the warm welcomes; and drink a couple for me next time. I just recently met a new member of the opposite sex, so I'm enjoying my time elswhere right now....:-)

Anyway, although Calacuccia's solution is probably much easier (and all the other xlstart folder ideas), I just want to add a final little bit more info to what I already posted in case it might be of any value to someone in the future. I'm sure all you other experts could or already have figured this out, but just in case it helps:
(Any references I make to Personal.xls could probably be replaced with any file in the xlStart folder). The idea of how it works is you add code to the personal.xls file using one API call (FindWindow) and the Application.OnTime process to create a timer that starts when Personal.xls is openned, and continuously checks for whatever file you want it to.
As soon as the file is openned, the Api will see it, then run whatever code you put in it.(Personal.xls that is, or if macro virus protection is not enabled, then whatever code is in the file you open.) I think you can even have code in personal.xls that runs any macro in the openned file, and the virus protection will not activate. If you want, you can simulate dissabling the timer after the code runs, and even enable it again if the file of issue gets closed by using a boolean variable or something in your timer code.
That way, since Personal.xls is usually hidden, the user never knows any difference between openning that file, or any other file, and doesn't have any idea that any of this is happenning, and makes no difference whether virus protection is enabled or not.

This is not specific to the problem here I don't think, and I have no idea why I wrote this, or who I wrote it to, but here is an example of what I mean, that I wrote and saved a long time ago. I don't have time to edit it or anything, but maybe it will help a little bit explain what I'm talking about.

'********************************************************************************************************
First Open Personal.xls then follow these steps:

1. Put this in the Workbook Open event:
   Call MyStart
 
2. Add a module
3. Copy this code and paste it into the module

'////////////
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private OpenCount As Integer
Sub MyStart()
    Application.OnTime Now + TimeValue("00:00:01"), "MyStart"
        If FindWindow(vbNullString, "Microsoft Excel - YourFileName.xls") <> 0 Then
            OpenCount = OpenCount + 1
            If OpenCount >= 10000 Then OpenCount = 2
        Else
            OpenCount = 0
        End If
    If OpenCount = 1 Then
        OpenCount = 2
        Application.Run "YourFileName.xls!Test"
    End If
End Sub

'////////////

4. After you do that, replace "YourFileName" with your real workbook name.

Now you have 2 choices. (Choice A won't work if you dissable macros when you open the file.
 Choice A. If you want to run the macro from where it's at in it's current workbook, then just change:
   "YourFileName.xls!MacroName" (commented as Second Change) with your real workbook name and the name of the macro to run. The names have to match exactly in all cases. I think whatever macro you run will also have to be in a module and not one of the already existing events like a sheet or workbook event.

 Choice B. If you just want to run the code right from Personal.xls, then take out that whole line:
            (Application.Run "YourFileName.xls!MacroName" 'Second Change)
           and put your code in it's place.

If you do that all correctly, then everytime you open Excel, when personal.xls opens(which is invisible if you want it to be), it starts checking to see if your workbook is open. It checks once every second. As soon as you open the workbook that you put in the code, it will run whatever code is in the If OpenCount = 1 statement and will only run once until you close your file and reopen it again: ie....
    If OpenCount = 1 Then
        Whatever code is here will run once
    End If

It's just a few short steps, and it works pretty good, unless you have some reason that you can't run your macro that way, but I can't think of any reasons right off.

A couple of tips: If you don't need it to check every second, then it would use a little less system resources if you changed it to a longer timer interval. If you choose to do that, just replace the 01 in
TimeValue("00:00:01")
with however many seconds you want. ie..TimeValue("00:00:02") would check every 2 seconds.
'********************************************************************************************************

Anyway, thanks again for all the warm welcomes back, and since I'm at a better time of life now, even though I don't ever seem to have much spare time so I'm still a little limited, maybe I'll be around a little more often then I have been.

Cheers!
ahammar
0
 
LVL 23

Expert Comment

by:ahammar
ID: 2760452
Hi everyone again!
Thanks for all the warm welcomes; and drink a couple for me next time. I just recently met a new member of the opposite sex, so I'm enjoying my time elswhere right now....:-)

Anyway, although Calacuccia's solution is probably much easier (and all the other xlstart folder ideas), I just want to add a final little bit more info to what I already posted in case it might be of any value to someone in the future. I'm sure all you other experts could or already have figured this out, but just in case it helps:
(Any references I make to Personal.xls could probably be replaced with any file in the xlStart folder). The idea of how it works is you add code to the personal.xls file using one API call (FindWindow) and the Application.OnTime process to create a timer that starts when Personal.xls is openned, and continuously checks for whatever file you want it to.
As soon as the file is openned, the Api will see it, then run whatever code you put in it.(Personal.xls that is, or if macro virus protection is not enabled, then whatever code is in the file you open.) I think you can even have code in personal.xls that runs any macro in the openned file, and the virus protection will not activate. If you want, you can simulate dissabling the timer after the code runs, and even enable it again if the file of issue gets closed by using a boolean variable or something in your timer code.
That way, since Personal.xls is usually hidden, the user never knows any difference between openning that file, or any other file, and doesn't have any idea that any of this is happenning, and makes no difference whether virus protection is enabled or not.

This is not specific to the problem here I don't think, and I have no idea why I wrote this, or who I wrote it to, but here is an example of what I mean, that I wrote and saved a long time ago. I don't have time to edit it or anything, but maybe it will help a little bit explain what I'm talking about.

'********************************************************************************************************
First Open Personal.xls then follow these steps:

1. Put this in the Workbook Open event:
   Call MyStart
 
2. Add a module
3. Copy this code and paste it into the module

'////////////
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private OpenCount As Integer
Sub MyStart()
    Application.OnTime Now + TimeValue("00:00:01"), "MyStart"
        If FindWindow(vbNullString, "Microsoft Excel - YourFileName.xls") <> 0 Then
            OpenCount = OpenCount + 1
            If OpenCount >= 10000 Then OpenCount = 2
        Else
            OpenCount = 0
        End If
    If OpenCount = 1 Then
        OpenCount = 2
        Application.Run "YourFileName.xls!Test"
    End If
End Sub

'////////////

4. After you do that, replace "YourFileName" with your real workbook name.

Now you have 2 choices. (Choice A won't work if you dissable macros when you open the file.
 Choice A. If you want to run the macro from where it's at in it's current workbook, then just change:
   "YourFileName.xls!MacroName" (commented as Second Change) with your real workbook name and the name of the macro to run. The names have to match exactly in all cases. I think whatever macro you run will also have to be in a module and not one of the already existing events like a sheet or workbook event.

 Choice B. If you just want to run the code right from Personal.xls, then take out that whole line:
            (Application.Run "YourFileName.xls!MacroName" 'Second Change)
           and put your code in it's place.

If you do that all correctly, then everytime you open Excel, when personal.xls opens(which is invisible if you want it to be), it starts checking to see if your workbook is open. It checks once every second. As soon as you open the workbook that you put in the code, it will run whatever code is in the If OpenCount = 1 statement and will only run once until you close your file and reopen it again: ie....
    If OpenCount = 1 Then
        Whatever code is here will run once
    End If

It's just a few short steps, and it works pretty good, unless you have some reason that you can't run your macro that way, but I can't think of any reasons right off.

A couple of tips: If you don't need it to check every second, then it would use a little less system resources if you changed it to a longer timer interval. If you choose to do that, just replace the 01 in
TimeValue("00:00:01")
with however many seconds you want. ie..TimeValue("00:00:02") would check every 2 seconds.
'********************************************************************************************************

Anyway, thanks again for all the warm welcomes back, and since I'm at a better time of life now, even though I don't ever seem to have much spare time so I'm still a little limited, maybe I'll be around a little more often then I have been.

Cheers!
ahammar
0
 
LVL 4

Expert Comment

by:Okki
ID: 2763733
Holmium:  Any Excel file with macro's will only work with macro's enabled.
If your users are to stupid to understand this you could hide all sheets in your file when you create it except one where you make a large sign that tell them: "Unless you enable macro's this is all that you will be able to see.  If you wish to use this file you must restart it with macro's enabled.
Worried ?  Well, back up your computer first then."

In the AutoStart macro of the file you "Unhide" sheets and hide the sheet that only appears IF they open without macro's enabled.
Reverse this procedure in an "BeforeClose" macro.

Of course you can create an exe-file that automaticly installs your file into the XLStart-folder but that would start your file everytime Excel starts.
This is only a good solution if you want this to happen. If you start another file with code from this workbook I think the macro dialog will appear unless the other book also is in the XLStart-folder.

If your users work in a "safe" environment (with good AntiVirus programs scanning all drives, floppy's and downloaded files) they could allways turn of the "Macro check" function permanently but I would suggest that someone that understands VBA investigate all code before any "Unknown" files were used in the system. If you don't trust the creator of a file, don't ever enable macro's before you have investigated the code.  There are very few limits to what you can do with a VBA macro and the damage can be devastating.

Johan
Sweden
0
 
LVL 1

Author Comment

by:holmium
ID: 2763796
hi all, thanks so much on all the suggestion...I cracked my head to think which methods should I use but it seemed the only way is to put inside the xlstart folders...but it seemed no way to auto enable macro...hmm...pretty true also...it let say there's then all macro virus will make use of this loop hole....mayb I should tell u guys wat and why I intended to auto enble it then u guys mayb suggest something to me...actually I've written a short program which will run with the macro enabled and of coz if let say the user disable it when open, my that program will not run and there're possibility and ways for it to hack it somehow...tat program I written actually required some security and I actually thought of secured it with a password and then when there's data entry, use code to unlocked it...but come to think of it, someone could just disable the macro n then go into the VBA editor and view all the codes including my code to unlocked the workbook...so all means of security will b gone....
If I could auto enabled the macro, then I could at least prevent user to go into VBA and my codes wont be revealed so easily....(of coz to those advanced user, there're still ways to get into it)....so anyone could suggest something to me to solve my problems??...
0
 
LVL 1

Author Comment

by:holmium
ID: 2763813
hi all, thanks so much on all the suggestion...I cracked my head to think which methods should I use but it seemed the only way is to put inside the xlstart folders...but it seemed no way to auto enable macro...hmm...pretty true also...it let say there's then all macro virus will make use of this loop hole....mayb I should tell u guys wat and why I intended to auto enble it then u guys mayb suggest something to me...actually I've written a short program which will run with the macro enabled and of coz if let say the user disable it when open, my that program will not run and there're possibility and ways for it to hack it somehow...tat program I written actually required some security and I actually thought of secured it with a password and then when there's data entry, use code to unlocked it...but come to think of it, someone could just disable the macro n then go into the VBA editor and view all the codes including my code to unlocked the workbook...so all means of security will b gone....
If I could auto enabled the macro, then I could at least prevent user to go into VBA and my codes wont be revealed so easily....(of coz to those advanced user, there're still ways to get into it)....so anyone could suggest something to me to solve my problems??...
0
 
LVL 17

Accepted Solution

by:
calacuccia earned 300 total points
ID: 2764009
Oh I see...

You can also protect your code !

Very simple to do, just go to VBE (Alt+F11), select your VBAProject and right-click, select VBAProject Properties, goto Tab 'Protection', check 'Lock project for viewing' and type in your password (twice).

That way, users will not be able to see the code (unless some high skilled password crackers).

Calacuccia
0
 
LVL 9

Expert Comment

by:antrat
ID: 2764922
Hi holmium

As calacuccia has said protecting your code is very simple. To make it very hard for anybody to get into with a password cracker use a 9 character mix of Alpha numeric i.e 4LtA59mR

Just be sure to write it down somewhere.


antrat
0
 
LVL 1

Author Comment

by:holmium
ID: 2850606
Comment accepted as answer
0
 
LVL 1

Author Comment

by:holmium
ID: 2850607
hi all...sorry for this real late reply...I was very busy n actually decided tat nothing much we could do to auto disable the macro protection option...anyway I think all of u have done the good job...thanks very much on that...I really appreciated much of tat...I think my points will go for Cal but I think everyone deserved the compliments...any comment pls write to me...any of coz, I'll give points to u all....thanks again...
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2850712
My pleasure, Holmium.

Thanks for the points ;-)
0
 
LVL 9

Expert Comment

by:antrat
ID: 2852650
Thanks holmium I'll take 500.








Just kidding :)

Glad you got your answer

antrat
0
 
LVL 23

Expert Comment

by:ahammar
ID: 2876937
antrat:

.................................             .......................  ........................
................                         ..............       ............
........................  ................. ...........
.......................................        ......................................
...........                 ..........               ...........................
..........
..............................       ....................................
.............................               ................
..................
                      .................             ..........................
.....................

.....                          ....

Here's 500 points I am giving up just for you:
You better count them and make sure they are all there.....:-)

Cheers!
ahammar
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2877851
Nice one, ahammar :-). Are you gonna multiply them by 4 for an A-grade?
0
 
LVL 9

Expert Comment

by:antrat
ID: 2878243
Thanks ahammer

There was 525 points so here's you change.

..........................


Now you can't say I'm not honest :O)

antrat
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now