Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference on a different sheet
I'm trying to use this code
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyCell As String 'Change callout here as desired MyCell = "Z1" If Range(MyCell).ValueThen Exit Sub Else MsgBox "Please use a value of ""P"" or ""F"" in appropriate cells!", , "Invalid Entry" End If End Sub Which works fine if Z1 is on the current sheet (Sheet 1). But I want to reference a cell on a different sheet (Sheet 3) in the same workbook. How do I do this? MyCell = "Sheet3!Z1" MyCell = "'Sheet3!'Z1" doesn't work... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference on a different sheet
MycellVal = Sheets(Sheet1).Range("A1")
OR MycellVal = Sheets(Sheet1).Cells(1,1) OR MycellVal = Sheets(<Sheetindex).Range("A1") If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: I'm trying to use this code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyCell As String 'Change callout here as desired MyCell = "Z1" If Range(MyCell).ValueThen Exit Sub Else MsgBox "Please use a value of ""P"" or ""F"" in appropriate cells!", , "Invalid Entry" End If End Sub Which works fine if Z1 is on the current sheet (Sheet 1). But I want to reference a cell on a different sheet (Sheet 3) in the same workbook. How do I do this? MyCell = "Sheet3!Z1" MyCell = "'Sheet3!'Z1" doesn't work... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference on a different sheet
Now I'm getting Error: Type Mismatch. Do I have to change Dim...String?
"Jacob Skaria" wrote: MycellVal = Sheets(Sheet1).Range("A1") OR MycellVal = Sheets(Sheet1).Cells(1,1) OR MycellVal = Sheets(<Sheetindex).Range("A1") If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: I'm trying to use this code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyCell As String 'Change callout here as desired MyCell = "Z1" If Range(MyCell).ValueThen Exit Sub Else MsgBox "Please use a value of ""P"" or ""F"" in appropriate cells!", , "Invalid Entry" End If End Sub Which works fine if Z1 is on the current sheet (Sheet 1). But I want to reference a cell on a different sheet (Sheet 3) in the same workbook. How do I do this? MyCell = "Sheet3!Z1" MyCell = "'Sheet3!'Z1" doesn't work... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference on a different sheet
Try the below. Please change the workbook name and the sheet name accordingly
Dim strValue As String strValue = Workbooks("Book5").Sheets("Sheet1").Range("A1") strValue = ActiveSheet.Range("A1") strValue = ActiveSheet.Cells(1, 1) If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: Now I'm getting Error: Type Mismatch. Do I have to change Dim...String? "Jacob Skaria" wrote: MycellVal = Sheets(Sheet1).Range("A1") OR MycellVal = Sheets(Sheet1).Cells(1,1) OR MycellVal = Sheets(<Sheetindex).Range("A1") If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: I'm trying to use this code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyCell As String 'Change callout here as desired MyCell = "Z1" If Range(MyCell).ValueThen Exit Sub Else MsgBox "Please use a value of ""P"" or ""F"" in appropriate cells!", , "Invalid Entry" End If End Sub Which works fine if Z1 is on the current sheet (Sheet 1). But I want to reference a cell on a different sheet (Sheet 3) in the same workbook. How do I do this? MyCell = "Sheet3!Z1" MyCell = "'Sheet3!'Z1" doesn't work... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference on a different sheet
Now I'm getting Run-time Error: '1004' Method 'Range' of object '_Worksheet'
failed. Here's exactly what I have for code in Book 2 Sheet1: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyCell As String 'Change callout here as desired 'MyCell = "G1" MyCell = Workbooks("Book2").Sheets("Sheet3").Range("G1") If Range(MyCell).Value Then Exit Sub Else MsgBox "Please use a value of ""P"" or ""F"" in appropriate cells!", , "Invalid Entry" End If End Sub In Book2 Sheet3 is this formula: =COUNTA(Sheet1!A1:E5)=25 So frustrating... what am I doing wrong? "Jacob Skaria" wrote: Try the below. Please change the workbook name and the sheet name accordingly Dim strValue As String strValue = Workbooks("Book5").Sheets("Sheet1").Range("A1") strValue = ActiveSheet.Range("A1") strValue = ActiveSheet.Cells(1, 1) If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: Now I'm getting Error: Type Mismatch. Do I have to change Dim...String? "Jacob Skaria" wrote: MycellVal = Sheets(Sheet1).Range("A1") OR MycellVal = Sheets(Sheet1).Cells(1,1) OR MycellVal = Sheets(<Sheetindex).Range("A1") If this post helps click Yes --------------- Jacob Skaria "Bishop" wrote: I'm trying to use this code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyCell As String 'Change callout here as desired MyCell = "Z1" If Range(MyCell).ValueThen Exit Sub Else MsgBox "Please use a value of ""P"" or ""F"" in appropriate cells!", , "Invalid Entry" End If End Sub Which works fine if Z1 is on the current sheet (Sheet 1). But I want to reference a cell on a different sheet (Sheet 3) in the same workbook. How do I do this? MyCell = "Sheet3!Z1" MyCell = "'Sheet3!'Z1" doesn't work... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference on a different sheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As Range set mycell = ThisWorkbook.worksheets("Sheet 3").range("Z1") if mycell.value = "something" then 'do something else 'do something else End If End Sub But I don't understand why you'd be checking the value on a different sheet when you change the selection on this sheet????? Bishop wrote: I'm trying to use this code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyCell As String 'Change callout here as desired MyCell = "Z1" If Range(MyCell).ValueThen Exit Sub Else MsgBox "Please use a value of ""P"" or ""F"" in appropriate cells!", , "Invalid Entry" End If End Sub Which works fine if Z1 is on the current sheet (Sheet 1). But I want to reference a cell on a different sheet (Sheet 3) in the same workbook. How do I do this? MyCell = "Sheet3!Z1" MyCell = "'Sheet3!'Z1" doesn't work... -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
multiple cell reference from sheet to sheet | Excel Worksheet Functions | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Cell to reference sheet tab name | Excel Discussion (Misc queries) | |||
Is is possible for a cell to reference it's "sheet name"? | Excel Worksheet Functions |