OK, the subject should say a lot about my Excel programming experience.
I'm quite the flaming novice. :)

I'm trying to change the text in a sheet named "Cert" on a cell
starting in column C to the text in a cell in another sheet in the same
workbook named "Info" when the cells in the A column change. I was
thinking this should be really easy and doable in 2 or 3 lines.

Here's what I came up with:

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
Range("C:" & Target.Row).Text = sheets("Info") ... and then the
cell, based on target cell's value.

It's the part after the "=" that I think is really getting me. It
can't be that hard... but I'm managing to make it so. Any advice would
be greatly appreciated.

Max.

Text is a read only property

Also, you cleverly left out any explanation of how to use the value of
Target to determine the appropriate cell in Info. I will assume it is a
named range.

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
Range("C" & Target.Row).Value = sheets("Info").Range(Target.Value).Text
end if

Note several changes to your original code.

--
Regards,
Tom Ogilvy


"Max C" wrote in message
ups.com...
OK, the subject should say a lot about my Excel programming experience.
I'm quite the flaming novice. :)

I'm trying to change the text in a sheet named "Cert" on a cell
starting in column C to the text in a cell in another sheet in the same
workbook named "Info" when the cells in the A column change. I was
thinking this should be really easy and doable in 2 or 3 lines.

Here's what I came up with:

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
Range("C:" & Target.Row).Text = sheets("Info") ... and then the
cell, based on target cell's value.

It's the part after the "=" that I think is really getting me. It
can't be that hard... but I'm managing to make it so. Any advice would
be greatly appreciated.

Max.

Hey Tom. Thanks for the reply. I tried something nearly exactly like
what you wrote below, only I was using the actual code to select the
range I want. Basically, when the user types in a number on the Cert
page in Column A, I use it to grab the description from a corresponding
line on the Info page from Range DXX:JXX. Every time I'd enter
something on the info page, though, I'd get the error "the method of
range class failed."

I have a new problem, though. I saved my workbook and closed Excel
last night. This morning when I went in to try your suggestion, the
workbook won't run the Macro I wrote when I change a value on the A
column on the Cert sheet. I'll keep plugging away at it. Believe it
or not, 10 years ago I was pretty good at writing Excel Macros. I was
even MS certified in VB. Just goes to show... if you don't use it,
you'll loose it.

Thanks for your help.
Max.

Tom Ogilvy wrote:
Text is a read only property

Also, you cleverly left out any explanation of how to use the value of
Target to determine the appropriate cell in Info. I will assume it is a
named range.

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
Range("C" & Target.Row).Value = sheets("Info").Range(Target.Value).Text
end if

Note several changes to your original code.

--
Regards,
Tom Ogilvy

Tom, thanks for the reply

OK, getting back in to this. I can't believe this is turning out to be
so hard for me. It's more than a little embarassing. Here's what I
have so far:
__________________________________________________ __________
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedText As String

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
SelectedText = Sheets("Info").Range("D" & (Target.Value + 24) &
":J" & (Target.Value + 24)).Text
Range("C" & Target.Row & ":I" & Target.Row).Text = SelectedText
End If

End Sub
__________________________________________________ __________

I realize the string variable is unnecessary, but it helps me see it
better. The thing is, the variable assignment line doesn't work. I
get Error 94: Invalid use of Null. Is there no such thing as a Text
property for a range of cells?

By the way, I'm using ranges of cells because in this workbook the 2
ranges holding text are merged cells. When I ran the macro recorder
and selected one of those merged cell blocks, the recorder showed I
selected a range of cells. Maybe that's my problem?

Thanks for looking again.
Max.

And dang it! Every time I save and close a workbook with this code in
it, when I reopen the workbook, the change event stops firing. Once
again I don't even get an error message when I change something in
column A. What's the deal?!

Max.


Max C wrote:
OK, getting back in to this. I can't believe this is turning out to be
so hard for me. It's more than a little embarassing. Here's what I
have so far:
__________________________________________________ __________
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedText As String

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
SelectedText = Sheets("Info").Range("D" & (Target.Value + 24) &
":J" & (Target.Value + 24)).Text
Range("C" & Target.Row & ":I" & Target.Row).Text = SelectedText
End If

End Sub
__________________________________________________ __________

I realize the string variable is unnecessary, but it helps me see it
better. The thing is, the variable assignment line doesn't work. I
get Error 94: Invalid use of Null. Is there no such thing as a Text
property for a range of cells?

By the way, I'm using ranges of cells because in this workbook the 2
ranges holding text are merged cells. When I ran the macro recorder
and selected one of those merged cell blocks, the recorder showed I
selected a range of cells. Maybe that's my problem?

Thanks for looking again.
Max.

Tom Ogilvy wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedText As String
On Error goto ErrHandler
If Not Intersect(Target(1), Range("A14:A35")) Is Nothing Then
Application.EnableEvents = False
SelectedText = Sheets("Info").Range("D" & (Target(1).Value _
+ 24) & ":J" & (Target(1).Value + 24)).Text
Range("C" & Target(1).Row & ":I" & Target(1).Row).Text = SelectedText
End If
ErrHandler:
Application.EnableEvents = True
End Sub

as to not working, it sounds like you have some code that disables events
and then does not turn them back on.

To test, run this macro

Sub ABC()
Application.EnableEvents = True
End Sub

after running that does the event macro start working?

This assumes you don't have security set to high and macros are silently
disabled.

--
Regards,
Tom Ogilvy

Tom, thanks once again for the attempted help, but I really don't
understand what your additions are supposed to do. I see that you put
"(1)" after each "Target" but it didn't seem to change anything. Also,
after the "If not" line you included "Application.EnableEvents = False"
before the line that does the actual work. I probably just don't
understand how VB reads this, but wouldn't that prevent the next line
from working at all?

At any rate, when I ran your code as it is, my worksheet didn't do
anything. I'd type in a number and nothing else would happen. When I
commented out the lines dealing with "Application.EnableEvents = " I
again got the "Invalid use of null" error.

I can't believe that what I'm trying to do could be so hard. It seems
like this would be a fairly common task.

Oh, and by the way, you were right about the Macros thing. I didn't
realize there was a security setting. I put it back to medium and now
all is well.

Thanks for everything.

Mark.

I just wanted to get back to everyone to say I finally played with this
freakin' thing long enough to get it to work. I honestly don't know
what combination of changes finally made it work, but I know it had
something to do with removing the ".text" property of the ranges I was
referencing in the sheet the text was supposed to be copied to. Here's
the resulting code.
___________________________
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A14:A35")) Is Nothing Then
If Target.Value < 1 Or Target.Value 180 Then
Range("C" & Target.Row & ":I" & Target.Row) = ""
Else
Range("C" & Target.Row & ":I" & Target.Row) =
Sheets("Info").Range("D" & _
(Target.Value + 24)).Text
End If
End If
End Sub
___________________________

Jeez, it looks so simple now. I'm embarassed that it took me this long
to figure it out.

Perhaps that'll help someone in the future. I can't be the first
person that's ever wanted to do this.

Max.


Max C wrote:
Tom Ogilvy wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelectedText As String
On Error goto ErrHandler
If Not Intersect(Target(1), Range("A14:A35")) Is Nothing Then
Application.EnableEvents = False
SelectedText = Sheets("Info").Range("D" & (Target(1).Value _
+ 24) & ":J" & (Target(1).Value + 24)).Text
Range("C" & Target(1).Row & ":I" & Target(1).Row).Text = SelectedText
End If
ErrHandler:
Application.EnableEvents = True
End Sub

as to not working, it sounds like you have some code that disables events
and then does not turn them back on.

To test, run this macro

Sub ABC()
Application.EnableEvents = True
End Sub

after running that does the event macro start working?

This assumes you don't have security set to high and macros are silently
disabled.

--
Regards,
Tom Ogilvy

Tom, thanks once again for the attempted help, but I really don't
understand what your additions are supposed to do. I see that you put
"(1)" after each "Target" but it didn't seem to change anything. Also,
after the "If not" line you included "Application.EnableEvents = False"
before the line that does the actual work. I probably just don't
understand how VB reads this, but wouldn't that prevent the next line
from working at all?

At any rate, when I ran your code as it is, my worksheet didn't do
anything. I'd type in a number and nothing else would happen. When I
commented out the lines dealing with "Application.EnableEvents = " I
again got the "Invalid use of null" error.

I can't believe that what I'm trying to do could be so hard. It seems
like this would be a fairly common task.

Oh, and by the way, you were right about the Macros thing. I didn't
realize there was a security setting. I put it back to medium and now
all is well.

Thanks for everything.

Mark.