Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi Files with same Button Code (2003 sp3)

    All,
    The specific problem I have is that my users could possibly have multiple files open that have the same button control code. This creates a conflict. Is there a way to have several workbooks open that utilize the same code for button controls? If they have several open will the button control remain focused for the spreadsheet they are working in and not try to execute on another file?

    I attached the code.

    Thanks,
    Brad

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    One scheme would be to add and remove the menu items on the window activate/deactivate so that the appropriate button/code would be available for only the active workbook and would call the code from that.

    But if it is "general" code for all the workbooks, why not compile them? It sounds to me that you should have 1 file with all the code. This could be done with by Creating Addins

    Then all the code would be run from this workbook.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    Steve,
    So, assuming I went this direction, If they had 3 files open that are of the same type, and 1 that is not related, how would I keep the button from showing in that workbook, and allow it to function in the 3 related workbooks?

    For your first comment, how would I accomplish the action of activating/deactivating for each workbook as they switch from one to the other?

    Thanks,
    Brad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    See <post:=497,804>post 497,804</post:> for an example.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    I think that is basically what I have. But when 2 or more workbooks are open at the same time, the second workbook to be opened gets a error on the macro at the line as indicated in my attachment.

    I was hoping there was an easy fix to ensure the each workbook button worked with the specific workbook regardless if multiple files were opened, calling the same basic code.

    Brad

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    If the button needs to call code from teh active workbook, instead of hiding/unhiding the buttons (as in Hans' example), you should delete the button in the deactivate and create it new in the activate.

    The code in workbook Open/Close should be used in Window activate/deactivate

    Steve

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    Here is a thought.

    The files I have the folks use will be 'masters'. If I go the route of making this an addin, could I hide some information for the code to verify. If the information exists, then show the button(s). If the information does not then the addin code will not run to have the buttons available.

    any thoughts?


    Brad

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    On the window activate you could just unhide (or create) only if certain conditions were met.

    Or you could have the buttons themselves available and check/verify the active file for info and run if it is there or if not there give a message


    Steve

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    Steve,
    Given the code provided, how exactly do I do what you suggest? I tried what I thought but it did not work.

    Brad

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    Does this code work (in addition to your "CreateBOMDropDown" code?)

    <pre>Option Explicit
    Private Sub Workbook_Activate()
    DeleteBOMDropDown
    CreateBOMDropDown
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteBOMDropDown
    End Sub

    Private Sub Workbook_Deactivate()
    DeleteBOMDropDown
    End Sub

    Private Sub Workbook_Open()
    DeleteBOMDropDown
    CreateBOMDropDown
    End Sub

    Sub DeleteBOMDropDown()
    On Error Resume Next
    Application.CommandBars("BOM Creation").Delete
    End Sub</pre>


    Steve

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    Steve,
    Pasted as written, no this did not work. I commented out my code down to
    ' Private Sub Workbook_Open()
    ' Call CreateBOMDropdown
    'End Sub

    now the button does not appear for olny one spreadsheet open.

    I did figure out how to 'verify' they are using the official file or not.

    Brad

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    What does not work? Could you be more specific?

    It worked as I expected after I added the line (after the cbr.position line):
    cbr.visible = true
    In your macro to display your menu item.

    I even tested it and it ran code from the active workbook even with mulitple copies of the workbook were used.

    I have attached an example.

    Steve

  13. #13
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    Steve,
    That took care of the prob.

    Thanks for all the help!

    Brad

  14. #14
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    Steve,
    with the code changes we have made to date, the function works fine. The focus remains to the proper file, and that is fine, however here is something odd. Once I click on the BOM Creation button, and complete the "save" dialog box, the button disappears. If I switch to another spreadsheet, and return the code works to show the button again.

    Attached is a file containing the "this Workbook" information and the Module 1 Macro data as well.


    Thanks,
    Brad

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multi Files with same Button Code (2003 sp3)

    Could you attach an example file which shows the problem so I do not have to recreate your file?

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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