+ Reply to Thread
Results 1 to 18 of 18

How to disable / enable the "ShareWorkbook" button via VBA?

  1. #1
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Question How to disable / enable the "ShareWorkbook" button via VBA?

    Hi everyone,

    I simply need to disable / enable the “Share Workbook” button on demand via VBA in Excel 2010.

    I have seen Ron de Bruin’s ribbon explanation using XML, but I could not get that to work.

    I have tried Chip Pearson's VBA sample from http://www.cpearson.com/excel/menus.htm, but could not get that to work.

    I also have already tried (from Google search) application.commandbars.FindControl(ID:=2040).Enable=False … or true… but that does not work either.

    I need to be able to do this from VBA and on demand.

    “Help me, Obi-Wan Kenobi. You're my only hope. “

    Thanks in advance!

    NJDevil

  2. #2
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Macros cannot be viewed or edited in shared workbooks. You cannot even see the code (after using Macro Recorder) for the instruction to Share Workbook.
    Probably it cannot work.
    Click on the star if you think I helped you

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    You can always record the code into another workbook in order to see whats needed.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Thank you for the replies. However, I think you misunderstand. I need to be able to Di sable (grey out) and enable (non-grey) the "Share Workbook" button. I do not want to programmatically share/unshared the workbook. Does this help clarify the requirement?

    There is a way to do it but I just can't seem to figure it out.

    Thanks for your help!

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    I have added a new group and button to the review tab which will toggle the sharedworkbook button.

    Please Login or Register  to view this content.
    customUI xml
    <customUI
    xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    onLoad="Ribbon_onLoad">
    <commands >
    <command
    idMso="ReviewShareWorkbook"
    getEnabled="SharedWorkbook_getEnabled"/>
    </commands >
    <ribbon >
    <tabs >
    <tab idMso="TabReview" >
    <group
    id="Group1"
    label="Group1">
    <button
    id="Button1"
    imageMso="ReviewShareWorkbook"
    label="Toggle Shareworkbook Enabled"
    size="large"
    onAction="Button1_onAction"/>
    </group >
    </tab >
    </tabs >
    </ribbon >
    </customUI >
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Very nice, Andy, but I need a way to do this without the user hitting a button. How would you code it to disable the ShareWorkbook upon Workbook_Open?

    I'm having difficulty understanding how to invoke the subroutines outside of the ribbon set up.

    Can you please send me some same code that will do the above?

    Thanks so much fo ryour help! This is great!

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Thanks, Andy. So if I want to invoke this upon file open (but with out the custom tab showing) then do exit the XML? Something like:

    <!--RibbonX Visual Designer 2.31 for Microsoft Excel CustomUI . XML Code produced on 2014/05/31-->
    <customUI
    xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    onLoad="Ribbon_onLoad">
    <commands >
    <command
    idMso="ReviewShareWorkbook"
    getEnabled="SharedWorkbook_getEnabled"/>
    </commands >
    <ribbon >
    <!tabs >
    <!tab idMso="TabReview" >
    <!group
    id="Group1"
    label="Group1">
    <!button
    id="Button1"
    imageMso="ReviewShareWorkbook"
    label="Toggle Shareworkbook Enabled"
    size="large"
    onAction="Button1_onAction"/>
    <!/group >
    <!/tab >
    <!/tabs >
    </ribbon >
    </customUI >


    I simply commented out the tabs, tab, and group, but this does nt work. I apologize for my ignorance but I am new to the ribbon setup and its integration with the subroutines.

    Can you please take one more shot to help me get this working? I know we are close.

    Also, I think you meant to name the second routine EnableShare(), right?

    Thank you so much for your help!

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Ribbon XML code
    <customUI
    xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    onLoad="Ribbon_onLoad">
    <commands >
    <command
    idMso="ReviewShareWorkbook"
    getEnabled="SharedWorkbook_getEnabled"/>
    </commands >
    </customUI >
    Module1 code
    Please Login or Register  to view this content.
    As the starting value is false by default you do not need to call anything from the workbook_open event.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Thanks, Andy. Few more questions:

    1) Where can I learn more about ribbon setup? Can you please provide some educational links that will delve into the basics, background and then more advanced features?

    2) As I step through the code, it will not execute the "gRibbonUI.Invalidate" step. Do you know what might cause that?

    3) The way I see this, you are using the XML set grey out the button and the VBA code to share the workbook. I got that working ok. What I'd liek to do is grey out the button from VBA with some logic around it. So:
    a) I could not get the "gRibbonUI.Invalidate" to work straight away. How might I invoke that to get it to tun?
    b) Is there another way to force the button to grey-out using VBA?


    THANK YOU SO MUCH!

    I really appreciate your help. Fantastic!


  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    http://www.rondebruin.nl/win/section2.htm
    http://www.oaltd.co.uk/DLCount/DLCou...ogRef_ch14.pdf

    Ribbon references have a tendency to lose connections.
    http://www.rondebruin.nl/win/s2/win015.htm

    Don't understand point 3. The 2 routines will toggle the state of the button , only you can know the logic that should determine which of the macros to call.

    Alternative way will not workin in xl2007 or newer. In fact it does not really work properly in xl2003

  12. #12
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Thanks for the info sites, Andy. I will check them out.

    The reason for my confusion:

    1) When I used VBE to step through the EnableShare routine, I get mixed results:
    a) Sometimes it runs and steps through without any errors, but it does not enable (un-grey) the button
    b) Usually it gives error "Run-time error '91': Object variable or With block variable not set". This occurs on the "RibbonUI.Invalidate" instruction.

    2) Assigning the EnableShare routine to a push button results in the same error as 1b above.


    Interestingly, when I initially opened the workbook and step through the code, it ran fine without any run-time errors. Unfortunately, although the EnableShare code ran, the button remained greyed-out. Is there some special case or some type of environmental setting or condition that we are not considering and accounting for in the routine?

    I feel like we are almost there, but this is challenging.

    Appreciate all your help on this and sorry it's taking so long to complete.



  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    you would need to post the workbook you are using as I don't see any of the issue you describe when testing.

  14. #14
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Hi Andy,

    Attached is the test workbook.

    Especially see the MakeExclusive and MakeShared routines.

    Some observations:
    1) If I invoke your EnableShare and DisableShare from the immediate window, no problems.
    2) If I try to call your EnableShare or DisableShare routines from another routine, it fails with a run-time error.

    So how to invoke your routines within my logic?

    Also, so other questions:

    a) Is it possible to set these routines as Private? How would that be done to ensure all code is accessible as necessary at run-time?
    b) Does code that's placed *after* the saveas mode=xlShared run even though the VB Editor closes after that statement in test? Or does it just simply stop execution after the saveas?

    Thanks again!
    Attached Files Attached Files

  15. #15
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    This file works for me. Only change was to move the Workbook_Open code to the ribbonload routine to ensure ribbon reference is initiated before any other code runs.

    The two buttons run your MakeShare and MakeExclusive routines.

    to hide routines use Option Private Module at top of code module.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Awesome! Thanks so much for all your help and for the time you put in on this, Andy!

    I've refined it a bit to suit my needs, but your work did it.

    Thank you!

  17. #17
    Registered User
    Join Date
    08-28-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Hi Andy,

    I was able to get it to work using your code and the Ribbon refresh code from Rory Archibald. Many thanks to both of you!

    How can I set it up to disable more than one button? I've tried, but cannot seen to get it to work.

    Can you please use sample you sent me with expanded XML and VBA code to demonstrate how to do this?

    Thanks again!

    NJDevil

  18. #18
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: How to disable / enable the "ShareWorkbook" button via VBA?

    Why don't you post what you have tried.

    You need to add more callbacks or use the existing one and add code to handle different controls and states of enabled.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to change a Command Button caption from "Enable" to "Disable"?
    By Infinity in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2007, 12:14 AM
  2. "Enable, Disable macros" window does not pop up, security is medi
    By Miguel Montes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2006, 09:05 AM
  3. Suppress "Disable/Enable Macros" and Query Refresh dialog on open
    By Sharon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2006, 05:25 PM
  4. How to disable annoying &amp;quot;Enable Macro&amp;quot; popup
    By Eric in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2005, 01:10 PM
  5. "Query Refresh" Enable or Disable popup...how do i get rid of it!
    By dark86 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2005, 11:31 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1