How to reference in sheet module value on another sheet?
 

Excel 2002 sp1

How can I reference, in a sheet module, a value on another sheet?

Ex. In a sheet module (Sheet1) I have the following code (simplified
for the example):

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("ceUserName").Value < "Joe" then
MsgBox "You are not authorized to change this."
Exit Sub
End If
End Sub

Range("ceUserName").Value - is a value from a range on another sheet
(Sheet1). It is a simple named cell.

My code, here in the sheet module for Sheet 1, will not reference to
that value. I get a 1004 error.

How can I reference, in a sheet module, a value on another sheet?

Thanks for your help,
Gregg

How to reference in sheet module value on another sheet?
 

CORRECTION:

Range("ceUserName").Value - is a value from a range on another sheet
(Sheet1). It is a simple named cell.

SHOULD HAVE READ:

Range("ceUserName").Value - is a value from a range on another sheet
(Sheet2). It is a simple named cell.

How to reference in sheet module value on another sheet?
 

If Worksheets("Sheet2").Range("ceUserName).Value < "Joe" then

--
Regards,
Tom Ogilvy


"Gregg" wrote in message
oups.com...
Excel 2002 sp1

How can I reference, in a sheet module, a value on another sheet?

Ex. In a sheet module (Sheet1) I have the following code (simplified
for the example):

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("ceUserName").Value < "Joe" then
MsgBox "You are not authorized to change this."
Exit Sub
End If
End Sub

Range("ceUserName").Value - is a value from a range on another sheet
(Sheet1). It is a simple named cell.

My code, here in the sheet module for Sheet 1, will not reference to
that value. I get a 1004 error.

How can I reference, in a sheet module, a value on another sheet?

Thanks for your help,
Gregg

How to reference in sheet module value on another sheet?
 

Ok, that makes sense. I thought I need to specify the worksheet, but
was stuck trying ...

Range("Worksheet1!ce*UserName").Value < "Joe" then

Thanks for the quick solution!!!

How to reference in sheet module value on another sheet?
 

But I think you would have to use Data Validation to
prevent a change from taking place.
http://www.contextures.com/xlDataVal06.html
http://www.mvps.org/dmcritchie/excel/validation.htm

=MyValidation("Joe")

since you cannot refer directly to another worksheet
in a validation formula.

Create a Function to make use of the above similar to Tom's
correction within the macro you had. You might use the userid
http://www.mvps.org/dmcritchie/excel/userid.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tom Ogilvy" wrote in message ...
If Worksheets("Sheet2").Range("ceUserName).Value < "Joe" then

--
Regards,
Tom Ogilvy


"Gregg" wrote in message
oups.com...
Excel 2002 sp1

How can I reference, in a sheet module, a value on another sheet?

Ex. In a sheet module (Sheet1) I have the following code (simplified
for the example):

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("ceUserName").Value < "Joe" then
MsgBox "You are not authorized to change this."
Exit Sub
End If
End Sub

Range("ceUserName").Value - is a value from a range on another sheet
(Sheet1). It is a simple named cell.

My code, here in the sheet module for Sheet 1, will not reference to
that value. I get a 1004 error.

How can I reference, in a sheet module, a value on another sheet?

Thanks for your help,
Gregg

How to reference in sheet module value on another sheet?
 

Tom's fix worked, but, yeah, I see what you're saying. My code is only
meant to stop a "revision date" from being entered into the record upon
change when the user's name doesn't match the record's owner. You see,
only those records with new revision dates get merged into a final
book. So, the change, although is not prevented, is ultimately
discarded in the merge.

BUT... having said all that, I'm now wondering how to absolutely
prevent the change when the user name is unequal to the record's owner.
And I have what I think is an easy situation to work with...
---- The user's name is in a named cell within the book. It's in Sheet2
in
range("ce*UserName).
--- The worksheet I'm watching for the change event is Sheet1.
--- The record's owner is named in column 8 of each record.

So, I don't have to look up a userID or anything fancy like that. The
information is right in the workbook.

I'd love to hear some ideas to stop the change. Do I make it undo?

I'm all ears. Thanks.