How do you get Visual Basic to either recognize text as a cell reference or to update its cells auto

Posted by Mark Foss on March 22, 2001 6:17 PM

Hi, I want to know if there is a way to change a cell refence automatically or possibly with some ingenuity in the Visual Basic Code. In the code below the cell "X67" needs to bechanged. I have written some other match and index functions that give me the new cell cooridnates (this updates automatically) however I can't get the VB code to read the contents of the cell as a cell reference only as text.

If you can help I would appriciate it greatly!!

Thanks,

Mark Foss

If Sheets("Selection Sheet").Range("P144") = False Then
MsgBox Range("Q144"), vbCritical
Cancel = True
Range("X67").FormulaR1C1 = "FALSE"
End If

Posted by Mark foss again on March 22, 2001 6:18 PM

Re: How do you get Visual Basic to either recognize text in an Excell cellas a cell reference or to update its cells automatically?

Posted by Dave Hawley on March 22, 2001 9:53 PM

Re: How do you get Visual Basic to either recognize text in an Excell cellas a cell reference or to update its cells automatically?

Hi Mark

I'm not too sure I follow you, but palce this in the Worksheet module. It will fire each time the sheet re-calculates. It uese the contents of Range P144 as the cell reference.

Private Sub Worksheet_Calculate()
On Error Resume Next
Dim CellRef As String
CellRef = Range("P144")
MsgBox Range(CellRef).Address
End Sub

Dave
OzGrid Business Applications