1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA: Excel Referring to a named range in another workbook

Discussion in 'Business Applications' started by Keebellah, Jan 17, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I wonder if somebody has come accross a solution for the following:
    I have a workbook with quite a lot of macro's and functions (vba).
    To simplify matters I use named ranges for easy reference etc.

    One of these functions adds a number of cell controls with certain sheets, but when I open another workbook, I need these options deleted since they will not function there.

    This works perfectly if I change to a worksheet that does not need this within the same workbook, but of course, when I open another workbook the function will not recognize the named range and throws an error (I avoid these by usin the on error ...)

    My question is, is there an option or way to hardcode the workkbook's name togehether with the named range when defineing it, so that when that particular range is referred to the code 'understands' where to look for it?

    Something like "My other workbook"!Range("NAMEDRANGE")

    If you use Refer.Name it only refers to the sheet but not the workbook. :confused:

    Thanks in advance.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you post your function code? I'm still a bit confused by what you are asking.

    Rollin
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Rollin, I think he wants to use something like Application.Goto.
     
  4. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Hi guys, not really.

    The vba code to create the right mouse buttons is below.

    Code:
    Function WBAct()
        Dim NewControl As CommandBarControl
        Dim x As Integer
        For x = 2 To 21
            If Sheets("BASIS").cells(x, 1).Value = "" Then Exit For
            Set NewControl = Application.CommandBars("Cell").Controls.Add
            With NewControl
                .Caption = Sheets("BASIS").cells(x, 2).Value
                .FaceId = Sheets("BASIS").cells(x, 4).Value
                .OnAction = Sheets("BASIS").cells(x, 3).Value
                .BeginGroup = Sheets("BASIS").cells(x, 5).Value
            End With
        Next x
    End Function
    Function WBDeact(wBName As String)
        'If ActiveWorkbook.Saved = True Or ActiveWorkbook.ReadOnly = True Then GoTo NATD
        Dim x As Integer
        For x = 2 To Workbooks(wBName).Sheets("BASIS").Range("B" & Workbooks(wBName).Sheets("BASIS").Range("B22").End(xlUp)).Row
            On Error Resume Next
            Application.CommandBars("Cell").Controls(Workbooks(wBName).Sheets("BASIS").cells(x, 2).Value).Delete
            On Error GoTo 0
        Next x
    natd:
    End Function
    
    The sheet basis contains in columns 1-5 (A-E) the necessary commands and face Id's for the buttons

    The function WBAct adds the commands and WBDeact removes them and this one I translated to a named range and the code below is the new WBDeact function

    Code:
    Function WBDeact()
        Dim rng As Range
        On Error Resume Next
        For Each rng In Range("RMOUSE")
            On Error Resume Next
            Application.CommandBars("Cell").Controls(rng.Value).Delete
        Next rng
        On Error GoTo 0
    natd:
    End Function
    
    What I need is that if you open a another workbook I need these command to be removed
    I put the above code in tThiswokbook and in Sub Workbook_Deactivate() part, it does start, but I get an error that the named range cannot be found (I commented the On Error ... line to check this.

    I hope this makes sense, I'm putting together a simple workbook with this part only to attach but it's not finished yet
    Thanks for taking the time
     
  5. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Sorry guys, I'll have to finish is tomorrow, sample will follow then too.
     
  6. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    I put this all in one macro, for simplicity, but in the real world you'd probably split it up. The first line shows you how to add names, via macro, and specify book and sheet names. The rest of the macro shows you how to check for the existence of a named range within a workbook. The idea is to proceed with the macro, if the name exists, and to stop the macro or skip over the named range portions, if the name doesn't exist.

    Code:
    Sub named_ranges_test()
    
        ' Add named range to Sheet1 one of Book1
        Workbooks("Book1").Names.Add Name:="Test_Name", RefersTo:=Sheets("Sheet1").Range("b2:b8")
        
        ' Checks for existence of name before proceeding with macro
        Dim nm As Variant
        Dim exists As Boolean
        exists = False
        
        ' If there are any names in workbook
        If Workbooks("Book1").Names.Count > 0 Then
           
            ' check for the appropriate name
            For Each nm In Workbooks("Book1").Names
                If nm.Name = "Test_Name" Then
                    exists = True
                End If
            
            Next nm
        End If
        
        If exists = True Then
        
            ' proceed with your macro
            
        End If
    
    End Sub
    
    If I've misunderstood your goals, I'll be happy to try to assist you further. Good luck!
     
  7. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Hi gyclone, thanks for your tip / input.
    In the code I posted I showed what my macro does, I didn't however include the naming ranges part, there is a whole routine that does that for me. The problem is not the naming of the range but the moment I leave the sheet to work on another sheet without closing the planner sheet.
    When working with my planner sheet I remove all taskbars, scroll bars, formula bar and just have a customized task bar and menu bar present with the right click options active.
    Alle these I restore to default but when returning to this palnner sheet they are shown again.
    The named range that I used to clear the cell control (right click) throws the error because the moment another sheet is opened (the first is deactivade) the named range is no longer recognized.
    I'll post a working sample later today to show what I mean, my work-around (2nd code block I posted) works but it's no longer dynamic.
    I think when the sample's posted it will make things clear.
    Just like what I ask posters to do, explaining what you want is the hardest part, so... I'll show it. Back soon.
     
  8. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Here's the file ...
    We use this to Plan the Inset and availability of our colleagues.

    I attached the whole file, there is no sensitive data, only names. I also include the vbs code to open it with the macro security limit.

    The Files are packed in a 3-part zipped file.

    You will have to rename inzet-planner.z01.zip to inzet-planner.z01 as well as inzet-planner.z02.zip to inzet-planner.z02 before unpacking this multi file zip (this is the only way to allow it to be uploaded)

    The password to open this file is tgs (lower case). The choice is read-only or read and write, open it read and write

    Most of the actions are done using the menu, right-mouse and other buttons.

    Below is the code in Thisworkbook for the option Workbook_Deactivate

    rMouseGone is my worksround, you can find the function if you open the
    VBA project and look in Thisworkbook
    The Sub WBDeactivate is the one that refers to the named range that doesn't work when
    you open a new sheet because it cannot find the named range.


    Code:
    Private Sub Workbook_Deactivate()
        'Call MenuMakerModule.WBDeact
    ' Delete item from shortcut menu on close
        rMouseGone
    '    WBDeactivate                   <<---  This is the non working option usin the Named Range "RMOUSE"
        Call MenuMakerModuleNew.WBDeact
        Application.DisplayFormulaBar = True
        Application.Caption = ""
        If Sheets("MEDEW").Visible = True Then Call ThisWorkbook.RemoveMenubar
        WbDeleteMenu
        WbDeleteMenuOntw
        Call CBToolbarShow("Standard", True)
        Call CBToolbarShow("Formatting", True)
        Application.DisplayFormulaBar = True
    End Sub
    
    

    The ApplInit code in the AaBasics module initializes all the named ranges and checks if the range is still the same and expands or reduces it dynamically

    To see all the Sheets, which are hidden, You can choose the option from the Excel menu (Version 2003 only) "Ontwikkel en Beheer Menu tonen". The password is Adm1n.
    If this doesn't work you can invoke the function Adm_Sheets

    The Sheet "BASIS" contains the data to build the cell command contents The Sheet "MenuSheet" builds the Menu's and has some extra settings and automatic generated prompts and dialogs for the different options.

    It's quite some code together with it all. I did my first steps in VBA in 2006 and using found code a new code I wrote has led to this version 6.0.3

    My Office 2007 and up version is not yet implementen because I need an alternative to my Taskbar Menu and the Menu Options

    BTW The password to unprotect the sheets is 1234 but is hardcoded, from the main menu you can use "Werkbladbeveiling Uit/Aan"

    The English translation is in the make. :)

    Thanks in advance for taking a look at this.
     

    Attached Files:

  9. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    Keebelah,

    I finally got a chance to take a look at this, but I can't get the files to unzip, with either 7-zip or native Windows unzipper. I've never dealt with multi-part zips before, so maybe I'm doing something wrong (I did rename the files, as you suggested). If you, or someone else, can suggest free software that you're sure will open this, I'll definitely take a look (I'm really curious about this one).

    Thanks,

    gyclone
     
  10. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    What you need to do is make sure that all 3 files are in the same folder and just unpaz the inzet-planner.zip file, it'll find the other two of they are named .z01 and .z02 automatically.
    If this doesn't work I'll see if I can post a zelf extracting version

    BTW there is a lot of zip and unzip free.
     
  11. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    Yeah, I moved all three files into a single, new folder, renamed the 2 sub-files, and then tried to extract just the inzet-planner.zip. I tried several different methods with the 2 programs I have and all failed. Have you tried to download and then unzip the files from here, since you uploaded them? If you have, and were successful, what unzipper did you use? I just don't want to keep trying until someone has confirmed that they've downloaded those 3 files and successfully unzipped them, just in case the problem is the files and not my system or my ignorance :)
     
  12. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I used rar now, see if this one works
     

    Attached Files:

  13. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Unzipping was no problem here, I use winzip but it's built in wit servant salamder (something like good old Norton Commander)
     
  14. gyclone

    gyclone

    Joined:
    Jan 16, 2011
    Messages:
    119
    Okay, I've got working files, thanks! It's getting late here, so I'm not sure how far I'll get tonight, but I'll get back to you when I can.
     
  15. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Don't worry, I hope the Dutch prompts won't throw you off.

    To reproduce the problem you will have to open VBA Project and edit the following two line ins ThisWorkbook:

    Code:
    ' Delete item from shortcut menu on close
        rMouseGone
    '    WBDeactivate                   <<---  This is the non working option usin the Named Range "RMOUSE"
    
    
    Deactivate the rMouseGone and activate the WBDeactivate

    Then open another Excel sheet and the error will come if the On Error lines are disabled
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975211

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice