VBA Code Not Working In China?

[ComPI]

I have a rather elaborate workbook that is basically a fully loaded financial calculator for a specialized industry. I primarily use VBA to force the workbook to expire and close after a preset date (so our customer can share their proprietary financial calculator on a temporary basis for marketing purposes). To force users to have macros enabled to use this workbook (so the expiry feature will run), I have a large picture of a lock that covers the entry fields (basically rendering the calculator useless without enabling macros).

I have run with this method for the better part of two years without issue (it runs with multiple languages and currencies and has been used all over the world, again without issue). However our customer has now contracted some work out to an temporary employee in China, and despite following steps to allow macros in their settings, the lock will not remove (which tells me that the VBA macros are not running).

Any idea what could be the cause here?

The code that SHOULD unlock it, but isn't for this particular user can be found below. It functions perfectly for all of the remaining users throughout the Americas and Europe, as we all use the same copy of the workbook.



Sub New_unlock()
'
' New_unlock Macro
'
Sheets("Assumptions").Select
ActiveSheet.Unprotect Password:="password1"
ActiveSheet.Shapes.Range(Array("Block")).Select
ActiveSheet.Shapes.Range(Array("Block", "Lock2")).Select
ActiveSheet.Shapes.Range(Array("Block", "Lock2", "Message")).Select
ActiveSheet.Shapes.Range(Array("Block", "Lock2", "Message", "MessagePic")). _
Select
Selection.Cut
Range("BA3").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Assumptions").Select
ActiveSheet.Protect Password:="password1"
End Sub

[snb]

What Office version is he using ?
Has VBA been installed in the first place ?

BTW; I'd use:


Sub New_unlock()
With Sheets("Assumptions")
.Unprotect "password1"
.Shapes.SelectAll
Selection.Cut .Range("BA3")
.Protect "password1"
End With
End Sub

[ComPI]

I didn't realize it had to be installed, I thought it was always coupled with Excel. We are both running Excel 2010.

If I swapped to use the code below, would it also copy my buttons and image of our customer's company logo?

Thanks for your reply!


What Office version is he using ?
Has VBA been installed in the first place ?

BTW; I'd use:


Sub New_unlock()
With Sheets("Assumptions")
.Unprotect "password1"
.Shapes.SelectAll
Selection.Cut .Range("BA3")
.Protect "password1"
End With
End Sub

[Paul_Hossler]

What are their Macro Security settings?

15770

[ComPI]

I ran through the instructions with him (he has 'with notification' enabled), and even talked him through overriding the macro security setting manually each time if it doesn't appear near the ribbon upon opening, but he says it doesn't remove the unlock.


What are their Macro Security settings?

15770

[snb]

Does he get a notification ?

Is he able to run the macro he himself creates in a workbook:


Sub tst()
msgbox "Ni Hao"
end sub

[SamT]

Copy and Paste from: Microsoft Forum
(http://answers.microsoft.com/en-us/office/forum/office_2013_release-access/executing-vba-code-in-china/72c2b962-7c74-448f-b403-efc89c58b001?auth=1) It could be that your friend does not have the right Excel library loaded. Tell him to open the code window by pressing Ctrl+G. Then click on Tools>References and check to see if any are marked as MISSING. If you included your 2013 Excel library in your references I bet that's the problem.

You can work around the differences between the two of you if you use late-binding. There are lots of code samples available in the web that show how to use late binding.

Bill Mosca
ThatllDoIT.com

Me? :dunno



(http://answers.microsoft.com/en-us/office/forum/office_2013_release-access/executing-vba-code-in-china/72c2b962-7c74-448f-b403-efc89c58b001?auth=1)

[ComPI]

The customer sent me the following screenshot after I asked him to open Excel and press ALT+F11. So I'd assume that he does indeed have VBA installed.

[ComPI]

15773

[ComPI]

I've asked the customer to check the Tools-->References for anything "missing". However, I am not sure how I would go about late binding. I have been reading it through the last 15 mins and it confused me more than it helped. lol If it wasn't apparent, I'm more of a google-and-do-it VB coder.

[snb]

Don't worry too early; I don't think you'll need it.
Ask the customer to produce a screenshot of

VBEditor/tools/ references ( Alt-F11 / Alt-T / R )

and post it here (in the right direction).

[ComPI]

15776

[ComPI]

The only reference selection difference between his and mine is that I also have 'Microsoft Forms 2.0 Object Library' checked off.

[snb]

Next step:

- if he opens 'your' workbook, has anything changed in vbeditor/tools/references ?

[Paul_Hossler]

Long shot -- are there REALLY macros in the troublesome workbook? The sideways screen picture doesn't seen to have any standard macro sheets.

I have seen people take an XLSM they were sent, and save it as an XLXS.

Also, possibly an anti-malware gateway program stripped the macros ???

[ComPI]

I hadn't thought about him Saving As something else from the emailed attachement.

It is my understanding that the screenshot of the references above was with my workbook open. I will get him to confirm that.

[ComPI]

I finally received a response. He confirmed that the file is still in XLSM format, even after I resent the working-for-everyone else file. So no save error unfortunately, that would have been a nice easy issue to resolve.

The other concern I have with the references, is I normally don't direct users to the VBA since I have that password protected (so more technically inclined users can't just go to the code and modify the expiration date). So you can't see macro sheets by default until you enter a password (so why are there sheets even showing in his screenshot? You cannot expand the list without the password). This hasn't stopped any previous users from successfully running the workbook.

So to recap where things sit as of right now:

The macros are not even being recognized, Excel does not give a security warning when opening my XLSM file.
The screenshot of the references below (identical to the one posted above) is when my file is open by the customer.
15817

How can I tell if his firewall or antivirus is stripping this from the workbook? I attempted to get him to extract it from a compressed folder to attempt to get around this (though I wasn't sure if that would have helped - figured it couldn't hurt).

[Paul_Hossler]

Send him a simple XLSM file as a control: one that has no protections and only has a Msgbox popup when the user runs the macro

If that works, then there must be something wrong/special/unique/weird about the real one

Just because it ends with XLSM doesn't mean that there are any macros in it

[snb]

I second that; see #6

[jonh]

put the file in a trusted location or create a new one

file > options > trust centre > trust centre settings > trusted locations

[Paul_Hossler]

The customer sent me the following screenshot after I asked him to open Excel and press ALT+F11. So I'd assume that he does indeed have VBA installed.


Ask for the screen shot with the troublesome workbook selected and showing (also mention the [Print Screen] key)

[twa8hr]

Was this solved? I do not see a clear answer but I may be unable to understand if it was posted above? What was the final solution?

We are also having trouble in China with Chinese Excel with VBA and even simple macros spreadsheet.

[twa8hr]

I did ask the Chinese contact to load the "Book1.xlsm" sheet and enter "ALT+F11" and send a screen capture of the results. I also asked him to load our VBA spreadsheet and enter "contro+G" and send a screen capture. This is not available yet.