# Solved: Vba code to enter password



## Teacherless (Jun 28, 2012)

Hi guys, this one is directed at the professionals. I am trying to find some vba code that will do the following. I would like, if it's possible, to have a macro button on my Excel toolbar, which, when selected, will trigger a personal macro that will insert a pre-determined password into the vba password dialogue box that appears when I try to open my workbook's vba editor. All my workbooks are protected with the same vba protection password, and as I am constantly needing access to the editor, this would save me having to type in, or copy and paste the password every time. I don't know if this is possible or not, so I am seeking the advise of the masters.
Hoping for some help here, regards, John


----------



## DataBase (Jan 15, 2004)

Hi John,

i found this and tested it and it worked.

Basically i had a workbook names "ABook.xlsm" and the password was "Blah123".

i added a button to the tob of the ribbon using the customise ribbon option in 'Options'

Click on the 'File' at the top left of Excel
Click Options
Click 'Customise Ribbon'
Then on the right Click 'New Tab'

now when you add you macro into the vba editor module you will be able to assign the macro to your new tab.

so, when i opened my excel document i tried to open the VBA project but it was paswword protected, so i hit the button and it unprotected it. give it a go, if you need help please let me know.

http://www.ozgrid.com/forum/showthread.php?t=13006


----------



## Teacherless (Jun 28, 2012)

Thanks for your interest DataBase, glad to see someone has given this some thought. Can you post the Personal macro code that you used as it is this that I can't seem to get right. I will try out your suggestion. If it works ok it will save me a lot of repetitious work. I should be able to let you know the results in a couple of hours. 
Thanks again, John


----------



## DataBase (Jan 15, 2004)

Hi,

no problem the code is in the link i provided in my first reply. however here it is: hope it helps

'need reference To VBA Extensibility 
'need To make sure that the target project Is the active project 
Sub test() 
UnprotectVBProject Workbooks("ABook.xls"), "password" 
End Sub

Sub UnprotectVBProject(WB As Workbook, ByVal Password As String) 
' 
' Bill Manville, 29-Jan-2000 
' 
Dim VBP As VBProject, oWin As VBIDE.Window 
Dim wbActive As Workbook 
Dim i As Integer

Set VBP = WB.VBProject 
Set wbActive = ActiveWorkbook

If VBP.Protection <> vbext_pp_locked Then Exit Sub

Application.ScreenUpdating = False

' Close any code windows To ensure we hit the right project 
For Each oWin In VBP.VBE.Windows 
If InStr(oWin.Caption, "(") > 0 Then oWin.Close 
Next oWin

WB.Activate 
' now use lovely SendKeys To unprotect 
Application.OnKey "%{F11}" 
SendKeys "%{F11}%TE" & Password & "~~%{F11}", True

If VBP.Protection = vbext_pp_locked Then 
' failed - maybe wrong password 
SendKeys "%{F11}%TE", True 
End If

' leave no evidence of the password 
Password = "" 
' go back To the previously active workbook 
wbActive.Activate

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
Sub ProtectVBProject(WB As Workbook, ByVal Password As String)

Dim VBP As VBProject, oWin As VBIDE.Window 
Dim wbActive As Workbook 
Dim i As Integer

Set VBP = WB.VBProject 
Set wbActive = ActiveWorkbook

' Close any code windows To ensure we hit the right project 
For Each oWin In VBP.VBE.Windows 
If InStr(oWin.Caption, "(") > 0 Then oWin.Close 
Next oWin

WB.Activate 
' now use lovely SendKeys To unprotect 
Application.OnKey "%{F11}" 
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password & "~" 
Application.VBE.CommandBars(1).FindControl(Id:=2578, recursive:=True).Execute 
WB.Save 
End Sub 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


----------



## DataBase (Jan 15, 2004)

If you are still struggling maybe attached you workbook and i will add the code and button on it for you? 

you may want to remove and sensistive data from it before you attached it though.


----------



## Teacherless (Jun 28, 2012)

Hi there DataBase, I've been trying to get this code to work, and so far I have murdered the garden boy, eaten all the chocolate I had saved up, been to the toilet 4 times to think, and I still am not so clever as I thought I was. Maybe it's the cold weather here, I don't know but the fact is I can't get it to work! 
I have attached a simple wb with buttons,(at least I know how to do that), in the hope that you can rescue me from my murderous moods. 
Cheers, John


----------



## DataBase (Jan 15, 2004)

View attachment 212531
Hi John,

ok i have edited your workbook, placed two buttons in the ribbon itself rather than the worksheet.

i have also added some information for you please read that.

Any questions please reply.

Thanks.


----------



## DataBase (Jan 15, 2004)

Please note i have just uploaded another wokbook, the previous one had an error.


----------



## DataBase (Jan 15, 2004)

I couldnt help myself, had to figure out the password issue, here it is all complete with your original password.


----------



## Teacherless (Jun 28, 2012)

Hi there DataBase, I must congratulate you on your dedication to this problem, shows true spirit. 
I downloaded and tried your wb but I seem to have two problems, both of which are probably due to my lack of knowledge. The first one is that I can't find the "Custom Macro" option in my ribbon. the second one is that when I try to run the macros from the vba editor, I get error messages. I have attached a .zip file containing two screen shots for you to look at. When I run the "View Macros, I can't see the ones that you placed in sheet1. I would appreciate it if you could walk me through the steps that I need to take in order to get your method working. Another thought is that I will need to place these macros in my "Personal Macros" in order to be able to use them on any workbook containing my password. Will I just have to copy and paste them there?
Again, thanks for your persistence, and putting up with the less educated, like me.
Cheers, John


----------



## DataBase (Jan 15, 2004)

Hi,

ok here are the instructions for adding the 'Custom Macro' Button/Tab in the ribbon:

1) Click on File
2) Click on Options
3) Click on Customize Ribbon
4) You will now see two list box's, click anywhere in the right hand size box and at the botton click 'New Tab'
5) Now click on 'New Tab (Custom)" and from the bottom click 'Rename' and rename it 'Custom Macro'
6) Now click on 'New Group' underneath your custom macro so that it is selected. 

Note for the next part the code must already be in the project. so use the spread sheet i attached earlier instead of using a new workbook.

6) On the left list box, you will see a drop down menu at the top called 'choose commands from' drop that down and choose 'Macros' this will now show you two macros.

7) click on the first Macro and click the 'Add' button. you shold see that the macro has now moved to your custom macro new group?
8) click the second macro and press Add again. 
9) Now click on each of the macro and select rename you can assign a picture to each one too. see the attached.

probably wise to print this out and see if you can follow it.


Now to address the error. this is occuring because the code needs a reference to a library. Here is how you need to add that reference. 

1) open the VBA editor (Alt + F11)
2) click on Tools
3) click on References
4) look for 'Microsoft Visual Basic for Applications Extensibility 5.3' 
5) tick the box next to it and press ok. 

you may have a different version other than 5.3 - this should not matter.

Thats it....should work. 

any problems let me know.


----------



## Teacherless (Jun 28, 2012)

Hi DataBase, I did what you suggested regarding the 'Microsoft Visual Basic for Applications Extensibility 5.3', but I couldn't find it in Office 2007. I did however find and tick the box in Office 2003. Does this mean that I will have to download it, or do I need an update for 2007? Meanwhile I am going to experiment with 2003 and see if I can get somewhere.
Thanks for you patience, John


----------



## DataBase (Jan 15, 2004)

Hi,

I think you may be looking for 'Microsoft Visual Basic for Applications Extensibility 5.3' in the wrong place. You need to be in the VB editor, where you write the code, then look under Tools > Reference

Is that where you were looking?

Can you attach a print screen of that window?

The file should be located at

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\Vbe6ext.olb

If you can find this file we should be able to get around the problem


----------



## Teacherless (Jun 28, 2012)

Hi DataBase, I opened the workbook with your latest password adjustments(screen shot as proof), and went into the references and the Microsoft Visual Basic for Applications Extensibility 5.3 was ticked. As I have both 2003 and 2007 running on my machine, and I found and ticked it in 2003 yesterday, it looks as though the references apply, and now appear in both versions, so that's solved that problem. However, I have just tried to run the macros again from the Visual Basic Editor and still get the same error message.(See attached screen shots).
Any ideas?


----------



## DataBase (Jan 15, 2004)

an thats a pitty its not working, though we are getting closer. its a long shot but try this, under the developer tab, macro security, make sure the 'Trust access check box is ticked" see the screen shot. if it unticked, tick it, save and close then try again.


----------



## DataBase (Jan 15, 2004)

Also check if you have all these references checked. see attached.


----------



## Teacherless (Jun 28, 2012)

Hi, yes, I have just checked, and they are all ticked, as you can see by my last screen shots. The only difference is that yours has a Microsoft Office 14 Object Library and mine an Office 12. Shouldn't make a difference though. The "Trust Access Box " is ticked, as I work a lot with macros and I had to tick it a long time ago. I am starting to feel guilty now because this problem is taking up a lot of your time and we haven't fixed it yet. But still, it's a learning curve for us all.
Cheers John.


----------



## DataBase (Jan 15, 2004)

Dont worry about it mate as you said its all learning for all of us. Did you check if you had that file in c:\program files ...... In the reply i sent earlier?


----------



## Teacherless (Jun 28, 2012)

Yes, just checked, all there. (See Att)


----------



## Teacherless (Jun 28, 2012)

Hi again, just wondered about our time zone differences, I'm in South Africa and the time here is 15.36hrs. What are your "Specs"?


----------



## DataBase (Jan 15, 2004)

Hi,

im in the UK, London, time here is 16:43...

There are 3 things i am going to suggest, try these in this order one of them should hopefully work.

1) manually select the Vbe6ext.olb file from the VBE console. see the attached picture. from the VB editior under tools > references click browse and go to where the file is, then select it. once you have done that, you will have to find it within the list and tick it.

2) try to register the file. All library files need to be registered, sometime something wrong and the files are not registered. to register the file to the following.

click Start > type Run > press enter

now type the following

regsrv C:\Program Files\Common Files\microsoft shared\VBA\VBA6\Vbe6ext.olb

make sure that path is correct to where the file is on your machine.....

3) try to download the Visual Basic runtime

http://www.microsoft.com/en-us/download/details.aspx?id=24417


----------



## Teacherless (Jun 28, 2012)

Mornin DataBase, I'm trying your suggestions out with a fresh mind after a good nights sleep. Ok, suggestion No1. I did this, everything seems normal there. Suggestion 2, I typed in the run box the address you gave but I think that there must be a slight mistake here as I got the message back(see att) that the address could be wrong. The path is correct, just the "regsrv" bit may not be correct. I have a feeling that once this operation is carried out successfully, things will improve. :up:
Cheers, John


----------



## DataBase (Jan 15, 2004)

Morning mate,

Ahh yes iv made an error in that, sorry must have been half asleep. Try regsvr32


----------



## Teacherless (Jun 28, 2012)

Hi, I copied the address into Wordpad, edited it, then copied and pasted it into the run box. Another message appears now. See att.


----------



## DataBase (Jan 15, 2004)

Hi Teacherless,

Try this. Open windows explorer then go to the file vbe6ext.olb. 

now open another instance of windows explorer and navigate to where regsvr32.exe is located (do a search)

now drag the vbe6ext.olb file over regsvr32.exe

i am hoping this will allow you to register it..


one question, within your workbook, in the VBE window, did you try to browse for the vbe6ext.olb file like i mentioned in my previous reply? and if you did what happen? that should have worked...... once you have browsed for it and added it, you will need to go and tick the checkbox next to the entry 

"microsoft visual basic extensibility 5.3" 

did you do that?


----------



## Teacherless (Jun 28, 2012)

Hi DataBase, thanks for getting back to me. I found the vbe6ext.olb file and the regsvr32.exe file ok and dragged like you said. Then I got the attached message. I think Bill Gates has put a curse on all SA imports of Windows7.
Cheers, John


----------



## DataBase (Jan 15, 2004)

ok will need to look into that, not sure what is causing that. May need a different way to register it.

however, what about browsing for it like i have said before, what happen?


----------



## Teacherless (Jun 28, 2012)

Ok, I whent into the vba console, browsed, found and selected the Vbe6ext.olb file, then ticked the Microsoft Visual Basic for Applications Extensibility 5.3 box, which was not ticked. I then opened the workbook that you sent, opened the vba editor and tried to run the 3 macros there but got the same message each time. See att.
Cheers, and thanks for your persistence, John


----------



## DataBase (Jan 15, 2004)

Have a look at this

http://support.microsoft.com/kb/840926

in references, look for any entery that has the word MISSING before it and delete it from the list.


----------



## Teacherless (Jun 28, 2012)

Hi DataBase, quite an interesting article, however, they were referencing Windows Xp and I am using Windows 7, don't know if that makes any difference or not. I checked through the references twice very carefully but didn't find any that where missing. I think that you can enter this problem in the Olympic Games while it's in the UK, you'd get a gold, I'm sure.


----------



## DataBase (Jan 15, 2004)

lol im sure we can get a gold for this indeed haha. 

mate i am very confused now, not sure what more to suggest. i am at a loss myself. 

will need to think about it a bit more. Any chance you can use another machine and see if it works?

also possibly try repair installing office?

you are using windows 7 

and office 2010 right?


----------



## Teacherless (Jun 28, 2012)

It's funny you should mention Office 2010. I have Windows 7 and I run Office 2003 and 2007. I had quite a job to make them behave themselves and not fight over who is the default. I smacked them into submission by altering the dword value in the registry. I would like to install 2010 but I'm not sure if they would put up with having another contender. I have been looking for the answer to this question- I create quite a lot of spreadsheets for other users and would like to know if there is any version of vba code which will work properly in 2003/2007 and 2010? (This one is for 2 golds). 
If I install and use 2010 vba, will my spreadsheets work with 2003 and 2007?
I have tried one of my spreadsheets on 2010 and nothing works. (Sorry,only plastic medals that melt in the sun for Bill Gates)
Meanwhile DataBase, have a break and think some more and I also. If you come up with the answer, I'll get it published in the "London Times" and the "Man of the Year" magazines.
Cheers, enjoy the Olympics, John


----------



## DataBase (Jan 15, 2004)

Hi John,

***************PART 1***************************

See the attached this picture is from one of your early replies with a documents.zip file attached. i see a missing file mateyy 

please check this carefully the word MISSING appears before one of the references. untick this reference.

check and double check them all. 

if you like, take screen shots of the entire reference box from top to bottom and i will check it for you. 

however this shold only apply to those items you have ticked. please check this ultra carefully, as if we can absolutely rule this out i can try and look for another solution.


*****************PART 2**********************

might be a long shot but....

look at the second attachment.

there are two entries for VBA Project.

tick one of them at a time and try the macro

if it doesnt work, tick the next one.

and see what happens.

i think the issue is that you have two very different versions of office installed the reference libraries are possibly conflicting or both versions are using the old libraries.


----------



## Teacherless (Jun 28, 2012)

GOoooood morning DataBase, at last, something is beginning to come right here. I found the missing reference, and unchecked it. Now, the macros seem to be working ok. I've just got to assign them to a couple of buttons and everything should be unkydory. The reason that this took me so long is the fact that I was looking in the references by just opening Excel and not opening your workbook. The London Times said that they will not publish this fact, but they will however, be recommending 3 Golds for you as you have come first in the "Patience and Persistence" event, winning by 2 and 2/7 weeks. Well done DataBase. I will post again and mark solved when I have got the buttons installed and working. 
All the best, John


----------



## DataBase (Jan 15, 2004)

Woohoo i get gold... 

awesome mate glad its working finally. get yu buttons on, assign them a macro each and see what happens. please do come back with your results..... thanks.


----------



## Teacherless (Jun 28, 2012)

Will do. Bill Gates has attacked me again!! It must be something that I have done whilst messing about with the references, but now, every time I open Excel, even with no file, I get this message.(See att). This only occurs in 2003 not in 2007. One step forward and one back. I ran the Office 2003 repair but still the same. I haven't run system restore yet as it means reinstalling 2 or 3 programs. Again, Windows 7 goes down in my estimation as the system restore only seems to set restore points if you install a program, whereas Xp set one automatically every couple of days. If I have to, I _will_ restore, but I thought that maybe you had some other remedy or ideas. (Don't worry mate, your 3 golds are safe, you can take them home with you).
Cheers John


----------



## DataBase (Jan 15, 2004)

i dont really ever recommend system restore, it never does a good job in my opinion.

what iw ould do is, right click the file, select 'open with' then browse for where you have installed Excel and open it with that.


----------



## Zack Barresse (Jul 25, 2004)

Excuse me, if I may step in? I have an opinion (about everything, hehe).

Could that error be from a Personal.xlsb created in 2007 and attempting to open in 2003? Since you're getting this when you open the application I would suspect 1) Personal file, 2) an add-in, but the latter doesn't make much sense unless it's an xlam (still a possibility). You could install the compatability pack (http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=3) to open 2007+ file formats in previous versions.

On your other question:


> I would like to install 2010 but I'm not sure if they would put up with having another contender. I have been looking for the answer to this question- I create quite a lot of spreadsheets for other users and would like to know if there is any version of vba code which will work properly in 2003/2007 and 2010? (This one is for 2 golds).
> If I install and use 2010 vba, will my spreadsheets work with 2003 and 2007?


In short - yes. You can happily have 2003, 2007, 2010 all installed side-by-side and working. There are some side effects however. Let me get to your code question regarding this first.

If you have code which doesn't work in 2010 but did in prior versions, it could be from a few things. Of course we'd have to see your code to figure out why. There are usually very few things that don't work forwards in versions. It's going backwards where the issue usually lies. Some things that won't play nice are some control files (i.e. calendar control), FileSearch method, as well as issues like increased row/column sizes. Coding practices come into scruitiny here, and most of these can be avoided with knowledge of version (subtle) changes in the Object Model.

Personally I only use the latest version, and usually have the most previous version prior installed. Since the 2013 customer preview was lauched, I'm running 2010 and 2013, both working well side-by-side.


----------



## Teacherless (Jun 28, 2012)

Hi there Zack, thanks for you interest. Firstly I solve the (Personal.xls) problem. I don't know how I did it but somehow I managed to "Save" my personal macros in the form of a worksheet in a workbook which, every time I opened Excel, produced a message saying that "this file is not in the correct format". To rectify this I deleted the Personal workbook from the XLStart folder. This got rid of my other macros as well so I just rewrote them and everything is back to normal once again.
Regarding the Office versions. To get 2003 to be the default and to make 2003 open a 2003 file, and 2007 open a 2007 file, I had to create a new DWord value in the registry. I have no idea, if I installed 2010, how I would be able do dictate which version open what when a file is selected. I really would like to have 2010/13 installed as I never know, when I create spreadsheets for other users, what version they are using and I must be able to provide to there needs. What would you suggest?
Cheers, John


----------



## DataBase (Jan 15, 2004)

Morning all,

Zack, always welcome to step in my friend, in fact i am glad as my expertise here are coming to an end. i read online somewhere that you can indeed install Office 2003/2007/2010 all without any problems, it involved editing the registry and adding some new values. I will wait to see how you guys do this, will be something for me to learn


----------



## Teacherless (Jun 28, 2012)

Hey DataBase, If you do that you will have to take one of your medals back.
John


----------



## DataBase (Jan 15, 2004)

Hey i got your message, can you attach you 'personal.xls' file so i can add in the macros? i only have office 2010 here so im not sure if i can save the macro within the workbook without converting it to '.xlsm' a macro enabled workbook. 

anyways, setup the workbook as you want, put your buttons where you want them, attach it here and i'll add the macros in for you..


----------



## Teacherless (Jun 28, 2012)

Thanks DataBase, I've attached the personal workbook.


----------



## DataBase (Jan 15, 2004)

i downloaded your personal workbook and it seems empty, there are no worksheets?

Also i you have the macros in there already, i guess the only problem you have is adding the buttons in the ribbon right?


----------



## Teacherless (Jun 28, 2012)

Hi DataBase, yes, you are correct, I have 2 macros in there that will protect/unprotect any active sheet in any workbook. I have two buttons on my main Excel toolbar to activate them and they work fine. These macros are in a module, not a sheet, so I assumed that your macros should also be in a module. However, I have assigned your macros, also to two buttons on the main Excel toolbar, but when I select, I get a message(See att) that the subject is out of range. I don't know, but I suspect it is because the macros are targeting a workbook (Book3.xlsm), and not any workbook that is open, and selected, in the editor. I would also like to know, is it possible to put two buttons in the VB editor toolbar as I can't seem to find a way to achieve this. 
Cheers, John


----------



## DataBase (Jan 15, 2004)

Right the reason you have the error is because, you are no longer using Book3.xlsm you are now using PERSONAL.xls (converted to PERSONAL.xlsm).

so you have to modify that within the macro. which i have done for you.

All you need to do now in your workbook is add to buttons, one to protect and one to unprotect your VBA Project.

Once button will need to call UnprotectProject
The other button will need to call ProtectProject

either add two ActivX buttons from the Developer tab / insert menu

or add the buttons in a custom group in the ribbon like i showed you in my earlier response.

i dont think you can put buttons on the actually vba editor ribbon.


----------



## Teacherless (Jun 28, 2012)

Hi DataBase, I'm going to tell you exactly what's going on at my end.
Firstly, the Personal.xls wb that I sent you, I had parked off in the XLStart folder. When I opened 2003, everything was fine, new book2, with 3 default sheets, as normal. Same when I opened 2007. The problem cropped up when I tried to run your macros, I thought because they where targeting Book3.xls. Ok, now I deleted this wb(mine) from the XLStart folder and replaced it with the one that you just sent me. Now, when I open either of the Excel versions, they open up as a wb named Personal.xlsm with only one sheet.
For some reason the personal macros have taken over the default normal book2 in both versions!!! Do you know exactly where Bill Gates lives and if he is in at the moment........


----------



## DataBase (Jan 15, 2004)

im getting confused now.

Where did book 2 come from?

The personal.xlsm workbook that i sent you, is the same one that you sent me, the worksheet was hidden so i had to undide it, there was only one worksheet. 

ok lets start over..


What we have:
1) Macros that unprotect and protect your vba project
2) Macros that unprotect and protect your worksheets

What we need:
1) ?
2) ?

What we need to clarify
1) book2 - what is this and how does this interface with personal.xlsm?


----------



## Teacherless (Jun 28, 2012)

Ok, book2 is what you get when you open your Excel application. This is what should happen every time Excel is opened, unless you double click on an Excel file first, then it opens that file. What's happening now is, after I put the "Personal.xlsm" file that you just sent back to me, in the XLStart folder, instead of the normal "Book2" opening when I open Excel, the file in the XLStart folder is opening, complete with it's one page. I hope this makes things a bit clearer even if it makes the challenge harder.
There's something that is puzzling me, besides the obvious, all the personal macros that I have ever used, I have placed in modules in the "personal macros" file and never used a sheet. The two macros of mine, the "unprotect/protect sheet" have always been stored in a module there, and they work perfectly. I stand to be corrected big time here, but would it not be safe to assume that your "protect/unprotect vba " macros may also work if placed in a module? Don't get me wrong, I'm just clutching at straws here...


----------



## Zack Barresse (Jul 25, 2004)

The "default version" is a pain for sure. The registry edit (hack?) is the only confirmed way I know to make this happen. I've heard (not sure, have not tried it myself), that choosing the specific "excel.exe" for the file association has worked, and that is supposed to be going to the right version (folder) of the executable.

You can add buttons to the ribbon, but you need to do so with xml. The CustomUI editor is how you do it, and it's a free download. It can be tricky though, even one character, even the wrong case, can cause it to not work and throw errors. It's not a very user friendly experience, but it certainly can be done. You can, however, do minimal customizations to the ribbon natively in 2010. It doesn't look very good, and you can only add native controls, nothing custom.

I'm on Pacific time, so I apologize, but I'm having to catch up on your conversation. 

A workbook MUST have at _least_ one worksheet. You shouldn't ever need to put code into a worksheet module of your Personal file. All of the time they should either be a standard module, a userform, or a class module. You could possibly use the ThisWorkbook module if you want code to execute everytime you open Excel, but I would think about using an add-in before I did that.

Also, the Personal file should open with Excel, that is good. Although it should be a hidden file. If it's not a hidden file, probably from re-creating from scratch, just hide it when it opens and close/re-open Excel.


----------



## Teacherless (Jun 28, 2012)

Hi Zack, I must say that I am thinking very seriously about installing 2010. What I need to do though, is try to get the method of manipulating the registry key DWord Values (assuming that's what's needed), to make sure that if a 2003/2007 or 2010 file is double clicked on, it opens with the correct version. It would be very useful to me to be able to compare code reactions in the 3 versions. It's very frustrating to have say, a wb working perfectly in 2003, then opening the wb on a machine using 2010 and finding that nothing works. This happened to me 3 days ago. I have a workbook that downloads the latest rate of exchange and calculates diamond prices. It works perfectly using 2003/2007 but when opened in 2010, just sits there and looks at me like I'm from another planet. If you know of anyone that has successfully installed the 3 versions, I would appreciate their contact details, as I could end up with a big mess without some guidance. As I see it, hacking the registry to sort out two versions is bad enough, but even if I manage to make one of three versions the default, the other two will still be fighting for dominance. 
By the way, you say you are on "Pacific Time" so in which country are you living? 
Cheers, John (English but living in South Africa)


----------



## Zack Barresse (Jul 25, 2004)

I'm in the western United States.

For your exchange/calculation prices, I'd suggest putting up a post with your code asking for it to be updated to the latest version. If you don't want to clutter up this thread, shoot me a private message with the link.

As for the other versions, they won't really be "fighting for dominance", they'll just be acting independently. At one time I had Excel 2000, 2002, 2003 and 2007 all side-by-side. Nowadays it's just 2010 and 2013.

If you're having problems with versions trying to "fight for dominance", which can happen with Word 2007 and later (a know "problem"), edit this entry in each version...


```
HKEY_CURRENT_USER\Software\Microsoft\Office\Version\Word\Options\NoRereg
```
Change "Version" for whatever version you want, where 2007 is 12.0, 2010 is 14.0 (no, there is no version 13 - and yes, someone at Microsoft was superstitious), then set the NoRereg DWORD value to 1 (00000001). Usually, so long as you install oldest to newest, Excel doesn't usually behave that way. I can't really vouche for the other Office applications.


----------



## Teacherless (Jun 28, 2012)

Hi there Zack, thanks again for your support. Dealing first with the "Exchange Rate" spreadsheet that I have. When opened in 2003, everything works fine, but when opened in 2007 (and maybe later versions, yet to be tried), I get a "Windows Security" message, asking for a password.(Nobody can tell me what password is being asked for). I posted this problem earlier but nobody seems to have any answers. I have attached one 2003 and one 2007 simplified version of this workbook for anyone to download and maybe have some suggestions. If it is possible, maybe you could find a machine using 2003 and try the 2003 version, you will find it downloads perfectly, with no message. 
My meaning regarding my last reference to this workbook, is that when opened in 2010, none of the macros seem to work. This is a different problem, which I maybe can sort out when I have 2010 installed. 
Regarding the 2010 version that I am going to install, yes, the registry modification you posted is exactly what I did to get the 2003 and 2007 versions to work properly. It will be interesting to see if things stay the same when I install 2010 or whether I will have to repeat this process.


----------



## Zack Barresse (Jul 25, 2004)

I believe you have to do this after every install. 

As for your code, you can use something quite a bit faster, and a little more elegant...


```
Sub GetRateR()

    Const sURL As String = "http://finance.yahoo.com/q?s=USDZAR=X"
    
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim rDest As Range
    Dim XML As Object
    Dim vRet As Variant
    Dim iStart As Long
    Dim dblExchange As Double
    
    Call TOGGLEEVENTS(False)
    
    Set WB = ThisWorkbook
    
    '/// Set destination worksheet and range here
    Set WS = WB.Worksheets("Sheet1")
    Set rDest = WS.Range("I2")
    
    Set XML = CreateObject("MSXML2.XMLHTTP.6.0")
    
    XML.Open "GET", sURL
    XML.Send
    vRet = XML.ResponseText
    
    iStart = InStr(1, vRet, "yfs_l10_usdzar=x", vbTextCompare) '+ 18
    iStart = InStr(iStart, vRet, ">", vbTextCompare) + 1
    dblExchange = Mid(vRet, iStart, 6)
    
    If dblExchange <> 0 Then
        rDest.Value = dblExchange
    End If
    
    Call TOGGLEEVENTS(True)
    
    Beep
    If dblExchange <> 0 Then
        MsgBox "Your exchange rate has been retreived.", vbOKOnly, "SUCCESS!"
    Else
        MsgBox "There was a problem retreiving your exchange rate.", vbOKOnly, "ERROR!"
    End If
        
'    On Error GoTo ErrorExit
'    Sheets("Sheet2").Select
'    Range("I4").Select
'    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
'    Application.CommandBars("Web").Visible = False
'    Windows("quotes.csv").Activate
'    Range("A1").Select
'    Application.CutCopyMode = False
'    Selection.Copy
'    Windows("Book2.xlsm").Activate
'    Sheets("Rate").Select
'    Range("A1").Select
'    ActiveSheet.Paste
'    Range("A2").Select
'    ActiveCell.FormulaR1C1 = Range("A1").Value
'    Sheets("Sheet1").Select
'    Range("A1").Select
'    Windows("quotes.csv").Activate
'    ChDir "C:\"
'    saveName = "quotes.csv"
'    ActiveWorkbook.SaveAs (saveName)
'    ActiveWindow.Close
'    MsgBox "If your R.o.E. does not update when you click OK, and the file [quoted.csv] is in your task bar, right click on it and select [Close Window], then check your Internet Connection, and try again."
'ErrorExit:

End Sub

Public Sub TOGGLEEVENTS(blnState As Boolean)
    Application.DisplayAlerts = blnState
    Application.EnableEvents = blnState
    Application.ScreenUpdating = blnState
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub
```
HTH


----------



## Zack Barresse (Jul 25, 2004)

Btw, I do not get asked for a password, only to enable macros.


----------



## Teacherless (Jun 28, 2012)

Hi Zack, that's quite a code you have there. Just one question, will I have to alter the code in any way in order to use it in the 2003/2007/2010 versions of Excel, or will it work in all versions?


----------



## Zack Barresse (Jul 25, 2004)

Nope, it was tested in 2010 and 2013.


----------



## Teacherless (Jun 28, 2012)

Ok, thanks, I will try it out tomorrow as it is getting late here, and I will let you know my results. 
Enjoy your day, John


----------



## Teacherless (Jun 28, 2012)

Hi there Zack, congratulations, your code works like a charm in both 2003 and 2007, and it works fast eh?
I've given your particulars to President Zuma (President of SA). He said he may get you to fix his "Balance of Payments" spreadsheet with a protected vba code that makes the balance more favorable to investors.
If you have the time, I would be interested to see your code with comments on each line to give me an idea of what the procedure is. I am not that skilled in vba and would like to learn as much as I can, and there is code there that I have not seen before. My e-mail address is in my profile but only send if you have time.
Thanks a million Zack, you are a star
Kind regards, John


----------



## Zack Barresse (Jul 25, 2004)

Glad it works for you John! I'm more than happy to comment the code up for ya. It should be considerably faster, as it's approach is different than what you were doing, which was downloading a file from the internet and opening it in Excel, grabbing that number and closing it. Basically what the code does is it loads a webpage into an object variable, using xml http request method (fairly quick method of loading the page source for a web page), then it looks through the source code of that page and parses out the number it finds. Drawbacks of this method are running it multiple times (sometimes the xml http request doesn't update if run in multiples in succession), it requires an internet connection (doesn't sound like it's a problem for you), it requires the use of a system dll (msxml6.dll, or whatever the latest version is you have, found in C:\Windows\System32\), and thus requires Windows OS.

Here is the code with comments...


```
Option Explicit

Sub GetRateR()

    '/// Set a string constant to the webpage we want to look at
    Const sURL As String = "http://finance.yahoo.com/q?s=USDZAR=X"
    
    '/// Dimension variables for use
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim rDest As Range
    Dim XML As Object
    Dim vRet As Variant
    Dim iStart As Long
    Dim dblExchange As Double
    
    '/// Set Excel properties off to run faster
    Call TOGGLEEVENTS(False)
    
    '/// Set variables
    Set WB = ThisWorkbook
    Set WS = WB.Worksheets("Sheet1")
    Set rDest = WS.Range("I2")
    
    '/// Create the xml object to send request via http protocols (i.e. internet)
    Set XML = CreateObject("MSXML2.XMLHTTP.6.0")
    
    '/// Tell the xml request to "GET" the web page (i.e. load the web page)
    XML.Open "GET", sURL
    
    '/// Send the request through the xml object
    XML.Send
    
    '/// Grab the response (i.e. load the source code into a variable)
    vRet = XML.ResponseText
    
    '/// Look in the source code for the attribute which houses the value
    iStart = InStr(1, vRet, "yfs_l10_usdzar=x", vbTextCompare)
    
    '/// Check if attribute was found
    If iStart = 0 Then GoTo ExitHere
    
    '/// Get the starting point after the closing tab of the attribute found
    iStart = InStr(iStart, vRet, ">", vbTextCompare) + 1
    
    '/// Grab the value found in the source code, it's only 6 characters long
    dblExchange = Mid(vRet, iStart, 6)
    
    '/// If there was a return, set the cell to that value
    If dblExchange <> 0 Then
        rDest.Value = dblExchange
    End If
    
ExitHere:
    
    '/// Reset Excel properties back on
    Call TOGGLEEVENTS(True)
    
    '/// Give a little sound
    Beep
    
    '/// Check if our variable was set to anything.  If not, something probably didn't work right!
    If dblExchange <> 0 Then
        MsgBox "Your exchange rate has been retreived.", vbOKOnly, "SUCCESS!"
    Else
        MsgBox "There was a problem retreiving your exchange rate.", vbOKOnly, "ERROR!"
    End If
    
End Sub

Public Sub TOGGLEEVENTS(blnState As Boolean)
    '/// Pass a boolean (true/false) variable to toggle application
    '/// properties for optimizing code run in Excel
    Application.DisplayAlerts = blnState
    Application.EnableEvents = blnState
    Application.ScreenUpdating = blnState
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub
```
I'm afraid I don't know President Zuma, but I would be more than happy to help with his balance.


----------



## Zack Barresse (Jul 25, 2004)

Btw, here is some additional information regarding the xml request method...

http://msdn.microsoft.com/en-us/library/ie/ms535874(v=vs.85).aspx


----------



## Teacherless (Jun 28, 2012)

Hi Zack, thanks a mill for the comments and link, even though you've made my head ache trying to understand it all. Makes me realize how much I don't know and how much I still have to learn. I am going to send you a private e-mail in a day or two, let me know your thoughts.
I really appreciate the help that you have given me, thanks big time.
Kind regards, John


----------

