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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

OBP

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

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Sorry guys, I'll have to finish is tomorrow, sample will follow then too.
 
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!
 

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

Attachments

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
 

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 
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 :)
 

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Unzipping was no problem here, I use winzip but it's built in wit servant salamder (something like good old Norton Commander)
 
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.
 

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top