Refedit format in VBA

  • Thread starter Thread starter CL
  • Start date Start date
C

CL

Excel 97
OS: XPSP2

I have a worksheet in XLA full of RefEdit format cell references (e.g.,
[Book1]Sheet1!$A$1). Using an App event module, I'm trapping target
values, which I process, to put into the locations referenced on this
sheet. Can I use references in this format ([Book1]Sheet1!$A$1) in VBA
to place the results? Or do I need to parse the reference?

Thanks
 
Sure.

But I'd check to see if it was valid range before depending on it. (The other
workbook has to be open, too.)

Option Explicit
Sub testme02()
Dim myCell As Range
Dim testRng As Range
Dim wks As Worksheet

Set wks = ThisWorkbook.Worksheets("sheet2")

Set myCell = wks.Range("a14")
'just for testing
myCell.Value = "[book2.xls]sheet1!c19"

Set testRng = Nothing
On Error Resume Next
Set testRng = Application.Range(myCell.Value)
On Error GoTo 0

If testRng Is Nothing Then
'that value isn't really a range reference
Else
testRng.Value = "Hi there!"
End If

End Sub
Excel 97
OS: XPSP2

I have a worksheet in XLA full of RefEdit format cell references (e.g.,
[Book1]Sheet1!$A$1). Using an App event module, I'm trapping target
values, which I process, to put into the locations referenced on this
sheet. Can I use references in this format ([Book1]Sheet1!$A$1) in VBA
to place the results? Or do I need to parse the reference?

Thanks
Click to expand...
 
Thanks Dave.

I just needed to know if that was even *possible*, as I was getting
quite discouraged. I figured out that I erred by passing .Value rather
than .Text. The interpreter couldn't handle the former. Doh!

Now if I could just solve one more headache--the blasted RefEdit. I
have a Listbox before the RefEdit so that the user can easily select
one of the open workbooks, but RefEdit only gives Sheet!$A$1 references
on the active sheet. The user can CTRL+TAB to get the *inactive*
workbook which WILL give [Book1]Sheet1!$A$1 type references, but that's
a bad design (I know if I do it that way I'll get a deluge of "Why
doesn't this work right?" questions).
Thanks for all of your assistance, Dave.
 
I bet you could choose the other workbook via the Windows option on the menubar,
too.

(Or maybe you could tile the display so that all the open workbooks have at
least one window showing???)

Or you could just use the dropdown for the name and the tweak the address by
dropping the workbook/worksheet name.
Thanks Dave.

I just needed to know if that was even *possible*, as I was getting
quite discouraged. I figured out that I erred by passing .Value rather
than .Text. The interpreter couldn't handle the former. Doh!

Now if I could just solve one more headache--the blasted RefEdit. I
have a Listbox before the RefEdit so that the user can easily select
one of the open workbooks, but RefEdit only gives Sheet!$A$1 references
on the active sheet. The user can CTRL+TAB to get the *inactive*
workbook which WILL give [Book1]Sheet1!$A$1 type references, but that's
a bad design (I know if I do it that way I'll get a deluge of "Why
doesn't this work right?" questions).
Thanks for all of your assistance, Dave.
Click to expand...
 
I was making it too difficult. Activate ThisWorkbook (hidden xla) and
then activate the visible workbook I want. That gives the [Book]
format.

Cheers :)
 
Back
Top