Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set up an Object ??
Hi;
I have several named ranges eg "Bar1, Bar2, Bar3 etc" . Each represents 1 column I want to lock. I want to lock and on lock them as a group. I have tried every way I can think of to use 'Set' but I keep getting error messages. I would like to lock and unlock them with one statement but if that is not possible can I just use a 'For Each ' loop? Can someone get me started on the right path? Regards bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set up an Object ??
Hi;
Sorry for replying to my own post but I would like to add some info. I am working with the following procedure. Sub LockDef() Dim BarsToLock As Range Dim AddressToLock As Range Dim FeesToLock As Range Set AddressToLock = Worksheets("Member_List").Range("Addresses").Addre ss Set FeesToLock = Worksheets("Member_List").Range("Fees").Address Set BarsToLock = Worksheets("Member_List").Range("Bar1E, Bar2O, Bar3V, _ Bar4AC, Bar5AI, Bar6AN, Bar7AT, Bar8BC, Bar9BL").Address End Sub Whatever I try I keep getting error messages. Different messages for different attempts. The "Bar1E, Bar2O etc." are Range Names for Columns "$E:$E", "$O:$O" etc. I can't get them set up as an object or a range. Nor, can I get VBA to accept "Fees" or "Addresses" as a range either, however, this works in the immediate window of the VBE: ? Worksheets("Member_List").Range("Addresses").Addre ss $P:$U - which is the correctly returned address. So -- now what? AS a double check, would I lock and unlock each of the ranges with a 'For' loop or, as *I* understood the manual, can locked = true lock a whole range with one command or statement? Regards Bill "Bill Case" wrote: Hi; I have several named ranges eg "Bar1, Bar2, Bar3 etc" . Each represents 1 column I want to lock. I want to lock and on lock them as a group. I have tried every way I can think of to use 'Set' but I keep getting error messages. I would like to lock and unlock them with one statement but if that is not possible can I just use a 'For Each ' loop? Can someone get me started on the right path? Regards bill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set up an Object ??
To treat a set of ranges as a group use UNION:
say you have Named Ranges as "first" and "second" Sub gsnu() Dim rt As Range, r1 As Range, r2 As Range Set r1 = Range("first") Set r2 = Range("second") Set rt = Union(r1, r2) rt.Select End Sub -- Gary's Student "Bill Case" wrote: Hi; Sorry for replying to my own post but I would like to add some info. I am working with the following procedure. Sub LockDef() Dim BarsToLock As Range Dim AddressToLock As Range Dim FeesToLock As Range Set AddressToLock = Worksheets("Member_List").Range("Addresses").Addre ss Set FeesToLock = Worksheets("Member_List").Range("Fees").Address Set BarsToLock = Worksheets("Member_List").Range("Bar1E, Bar2O, Bar3V, _ Bar4AC, Bar5AI, Bar6AN, Bar7AT, Bar8BC, Bar9BL").Address End Sub Whatever I try I keep getting error messages. Different messages for different attempts. The "Bar1E, Bar2O etc." are Range Names for Columns "$E:$E", "$O:$O" etc. I can't get them set up as an object or a range. Nor, can I get VBA to accept "Fees" or "Addresses" as a range either, however, this works in the immediate window of the VBE: ? Worksheets("Member_List").Range("Addresses").Addre ss $P:$U - which is the correctly returned address. So -- now what? AS a double check, would I lock and unlock each of the ranges with a 'For' loop or, as *I* understood the manual, can locked = true lock a whole range with one command or statement? Regards Bill "Bill Case" wrote: Hi; I have several named ranges eg "Bar1, Bar2, Bar3 etc" . Each represents 1 column I want to lock. I want to lock and on lock them as a group. I have tried every way I can think of to use 'Set' but I keep getting error messages. I would like to lock and unlock them with one statement but if that is not possible can I just use a 'For Each ' loop? Can someone get me started on the right path? Regards bill |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set up an Object ??
Worksheets (Member_List)
With Range("Bar1E, Bar2O, Bar3V, Bar4AC, Bar5AI, Bar6AN, Bar7AT, Bar8BC, Bar9BL") .Locked = Not .Locked End With End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bill Case" wrote in message ... Hi; Sorry for replying to my own post but I would like to add some info. I am working with the following procedure. Sub LockDef() Dim BarsToLock As Range Dim AddressToLock As Range Dim FeesToLock As Range Set AddressToLock = Worksheets("Member_List").Range("Addresses").Addre ss Set FeesToLock = Worksheets("Member_List").Range("Fees").Address Set BarsToLock = Worksheets("Member_List").Range("Bar1E, Bar2O, Bar3V, _ Bar4AC, Bar5AI, Bar6AN, Bar7AT, Bar8BC, Bar9BL").Address End Sub Whatever I try I keep getting error messages. Different messages for different attempts. The "Bar1E, Bar2O etc." are Range Names for Columns "$E:$E", "$O:$O" etc. I can't get them set up as an object or a range. Nor, can I get VBA to accept "Fees" or "Addresses" as a range either, however, this works in the immediate window of the VBE: ? Worksheets("Member_List").Range("Addresses").Addre ss $P:$U - which is the correctly returned address. So -- now what? AS a double check, would I lock and unlock each of the ranges with a 'For' loop or, as *I* understood the manual, can locked = true lock a whole range with one command or statement? Regards Bill "Bill Case" wrote: Hi; I have several named ranges eg "Bar1, Bar2, Bar3 etc" . Each represents 1 column I want to lock. I want to lock and on lock them as a group. I have tried every way I can think of to use 'Set' but I keep getting error messages. I would like to lock and unlock them with one statement but i f that is not possible can I just use a 'For Each ' loop? Can someone get me started on the right path? Regards bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
Option button object proeprties or object not found in vba | Excel Programming | |||
Confusion about how the Window object fits into the Excel object model | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |