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...

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...

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...

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...

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...

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