How do I set up an Object ??

[Guest]

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

 

[Guest]

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").Address
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").Address
$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

 

[Guest]

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

 

[Bob Phillips]

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)