Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) | Excel Discussion (Misc queries) | |||
multiple cell reference from sheet to sheet | Excel Worksheet Functions | |||
Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) | |||
Reference Addin Module from sheet code | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |