[Teacherless]

Firstly I am a beginner of beginners but I try to learn as much as I can.
I have put the following protection code into an Excel 2003 workbook and everything runs ok. I then opened the workbook in Excel 2007, and saved it as a .xlsm file. When I try to open the .xlsm file in Excel 2007, I get a vba Runtime error 13, "Type mismatch", and the code sticks at the first line below("If Worksheets...). I have tried everything that my limited knowledge allows, searched the web for 2 days, and still have no answers.
Any help would be greatly appreciated.

Private Sub Workbook_Open()
If Worksheets(28).[A51] > "" And Worksheets(28).[A50] = Worksheets(28).[A51] = False Then
MsgBox "Sorry, this program is not registered for this computer, please e-mail .......... for more info."
Application.DisplayAlerts = False
Application.Quit
End If
End Sub


My system specs are shown below.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i3 CPU M 380 @ 2.53GHz, Intel64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2934 Mb
Graphics Card: Intel(R) HD Graphics, 1243 Mb
Hard Drives: C: Total - 290143 MB, Free - 172186 MB;
Motherboard: Dell Inc., 0WXY9J
Antivirus: avast! Antivirus, Updated and Enabled

[Jimmy the Hand]

Try this way

If Worksheets(28).Range("A51") > "" And Worksheets(28).Range("A50") = Worksheets(28).Range("A51") = False Then

Although I find this statement a little curious...
Are you sure it does what you think it does?

Jimmy

[Keebellah]

These erros are programming errors and as Jimmy syas these statements are 'a little curious'
Where do the macro's come from?
Are macro's enabled?

[Teacherless]

Hi guys, thanks for your prompt replies to my call for help. I will try to explain my code a little better.
The workbook in question I want to give to another person and I do not want it copied and used on another pc unless I give permission. I placed the code in "This Workbook". What happens is this, in sheet28(A50) there is a formula "GetName(3)" which displays the users pc name. Other code I have written into the sheet copies (A50) and pastes the value into (A51) thus leaving a permanent record of the first users pc name. If the workbook is then copied and opened in another pc, the Cells(A50) and (A51) will not be equal and the workbook will close. The only way that the workbook will function is if Cell(A51) is empty when first opened, thereafter Cells(A50) and (A51) must be equal, which they will be unless opened on another pc.
As I said, everything works perfectly in 2003 but the code sticks on the first line in 2007.
I have tried Jimmy's suggestion but still get the error.
I have also inserted code into "This Workbook" which forces the user to "Enable Macros" otherwise the workbook will not open.
I hope this explains thing a little better.

Teacherless.

[Keebellah]

I don't know what the getname(3) formula does, but that's not really an issue.
I assume you use the username variable to regsiter this
First if you have sheet28 and somebody deteles it you'll have a problem.
I suggest a (very) hidden sheet wher you store th value.
You can leave it hidden and just do this.

For the eaxmple
create a sheet named user.
You will have to include an option to test if it exists and if does not exist create it and hide useing the vba xlsheetveryhidden, it won't show in the availabe hidden sheets.

the simply a1 the current user and a2 the using user.

Sub TestUser()
if sheets("User").range("A1").text = "" then
 Sheets("User").Range("A1;A2").value = environn$("username")
elseif if environ$("username") <> sheets('User").Range("A2") then 
Activeworkbook.Close False
end if
end Sub

See if you can work it out further, I typed thsi without Excel so syntax is not checked but this would be the main idea.
The sheet does not have to be unhidden to referto

[Jimmy the Hand]

I suggest trying this code:

If (Worksheets(28).Range("A51") <> "") And (Worksheets(28).Range("A50") <> Worksheets(28).Range("A51")) Then

Jimmy

[Teacherless]

Hi again, I tried your suggested code Jimmy, but still got the same error, (Type mismatch).
Hans, I created a new workbook, placed your code in "This Workbook" using "Private Sub Workbook_Open()", but when I ran the code I got an error "Sub or Function not defined" and
the line after "Then" was in red text. By the way, my sheet(28) is "Veryhidden" by default.
What I can't understand is why everything works fine in 2003 but the same code creates errors in 2007!!!
Again thank you guys for taking the time to help.
John (Teacherless).

[Jimmy the Hand]

Three more suggestions:
1. Try this code

If (Worksheets(28).Range("A51").Value <> "") And (Worksheets(28).Range("A50").Value <> Worksheets(28).Range("A51").Value) Then

2. Try separating the conditions so that you can see which one is disliked by Excel. E.g.

If (Worksheets(28).Range("A51").Value <> "") Then
    If (Worksheets(28).Range("A50").Value <> Worksheets(28).Range("A51").Value) Then
        'something
    End If
End If

3. Make sure that all references (VB editor - Tools - References) to outer libraries are in order.
Excel can throw very strange exceptions when any of those references is missing.

If none of these helps, then I'm out of ideas. You should post the workbook so that we can have a close look.

Jimmy

[Keebellah]

I put the code i suggested in the file

Try it out.

[Teacherless]

Thank you everyone for being so patient and persistent trying to solve my problem. First Jimmy, I tried your code suggestions, the first one created the same error, the second one produced the same error, but stuck on the second "If". It looks as though that if more than one cell address is in the code line, that's where the trouble starts.
Next Hans, I tried your workbook, (thanks for sending). What I need to know is how do I test this wb as I only have one pc. Could you explain what I must do to the wb in order to duplicate opening it on another pc.
What I also going to do tomorrow(it's getting late here in S.A.), is create 2 workbooks, one in 2003 and one in 2007 both using the same code and set up exactly the same as my original but using only 3 sheets as opposed to 30, which my original contains, and attach them here for you to examine.
Have a good day(or night) and thanks again.

[Keebellah]

How to reproduce? weel on one PC i don't know.
You could open the book, place another username in the USER sheet in both ranges.

Disable the before close macro so that the range is not cleared befor closing, save it, close it and open it again.
You don't need two version, just one xls file which works in both versions with no porblems

[Teacherless]

Hi there, I've created two workbooks and attached them. One is a working version in Excel 2003, the other is the problem version in Excel 2007. I have placed some information in sheet 1 (2003). I hope with these wbs you will be able to solve my problem.

[Keebellah]

All I did was reorganize your macro's, the non sheet macro's should always be in a separate module
You can elimintae the Call because it's a macro and no parameters.
I tried them and they both work, no syntax notihing.

I would however change your code to specific sheet names , not sheet(1) or so because you never know when someone reindexe them then Sheet(1) could all at once become Sheet(2) and then your whole plan goes down the drain.

I don't see any issues (anymore) and once again, you can use let's say the 2003 version under 2007 or 2010 without any problems unless you have a specific reason for both versions.

Let me know it it works, you're in the same timezone as I am (The Netherlands)

[Teacherless]

Good day my friend, you where remarkably quick to reply with 2 workbooks, thanks. I opened the 2003 version and it worked perfectly as you said it would. I then opened it using Excel 2007 and the same problem is still there, it sticks at the first line in "This Workbook".(Below Private Sub Workbook_Open()).
I then opened your 2007 version in Excel 2007, same, still sticks in the same place.
It would be good if I could have one version that opened in all versions of Excel but I don't mind having two, and giving the user a choice, depending upon the version of Excel that they are using.
Do you have 2003, 2007 or both on your pc as it is impossible to check both wbs using only one version ?
Changing the subject slightly, you guys made me smile when you commented on my code, "These statements are a little curious", because I know very little about writing vba from scratch, and this line in question I just used my knowledge of normal cell "If And" formulas and tried to write what I thought would work. After many attempts I did get it to work, but only in Excel 2003, as you know. I just wondered what it is about the code that caused that comment as I am always willing to learn.
Thanks, John.

[Keebellah]

I suggest the following.

Throw away the 2007 version.
use the attached 2003 version in borth Excels, they work here.

I have a virtual PC running Office 2003 and the 'normal' one wiht 2010

Both work perfectly with this version.

If needed then just open the 2003 version and choose saveas 1007 macro enabled and you will have the exact copy of the 2003 version

Try and let me know.