Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: VBA Code Not Working In China?

  1. #1
    VBAX Regular
    Joined
    Mar 2016
    Posts
    10
    Location

    VBA Code Not Working In China?

    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
    Last edited by SamT; 03-29-2016 at 05:31 PM.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  3. #3
    VBAX Regular
    Joined
    Mar 2016
    Posts
    10
    Location
    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!

    Quote Originally Posted by snb View Post
    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

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    What are their Macro Security settings?

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Mar 2016
    Posts
    10
    Location
    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.

    Quote Originally Posted by Paul_Hossler View Post
    What are their Macro Security settings?

    Capture.JPG

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Copy and Paste from: Microsoft Forum
    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?



    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    VBAX Regular
    Joined
    Mar 2016
    Posts
    10
    Location
    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.

  9. #9

  10. #10
    VBAX Regular
    Joined
    Mar 2016
    Posts
    10
    Location
    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.

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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).

  12. #12

  13. #13
    VBAX Regular
    Joined
    Mar 2016
    Posts
    10
    Location
    The only reference selection difference between his and mine is that I also have 'Microsoft Forms 2.0 Object Library' checked off.

  14. #14
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Next step:

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

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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 ???
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    VBAX Regular
    Joined
    Mar 2016
    Posts
    10
    Location
    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.

  17. #17
    VBAX Regular
    Joined
    Mar 2016
    Posts
    10
    Location
    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.
    references2.jpg

    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).

  18. #18
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #19
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I second that; see #6

  20. #20
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    put the file in a trusted location or create a new one

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •