There must be a way!!!--set cells' values equal to calculated values in another range
 

I must set up an extra column that only contains values--values that
are equal to calculated values from another column. This has to be
done in VBA and not a regular formula (the column must contain
values--not formulas). I've tried...

Range("NameValues").Value = Range("NamesConcatenated").Value

The problem is that the NameValues must be updated after worksheet
change.

However, this is creating a never-ending 'filling cells' process. When
forced quit (Error 1004) the data values, however, are in the
NameValues column--so it's partially working--can this work?

Any help would be incredibly appreciated.

Eric

There must be a way!!!--set cells' values equal to calculated values in another range
 

Eric,

Try code like the following in the worksheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("NamesConcatenated")) Is Nothing
Then
Application.EnableEvents = False
Range("NamesConcatenated").Copy Destination:=Range("NameValues")
Application.EnableEvents = True
End If
End Sub


The line
Application.EnableEvents = False
prevents the change from triggering the Change event, which would put you in
a loop.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)





"Arnold" wrote in message
oups.com...
I must set up an extra column that only contains values--values that
are equal to calculated values from another column. This has to be
done in VBA and not a regular formula (the column must contain
values--not formulas). I've tried...

Range("NameValues").Value = Range("NamesConcatenated").Value

The problem is that the NameValues must be updated after worksheet
change.

However, this is creating a never-ending 'filling cells' process. When
forced quit (Error 1004) the data values, however, are in the
NameValues column--so it's partially working--can this work?

Any help would be incredibly appreciated.

Eric

There must be a way!!!--set cells' values equal to calculated values in another range
 

Hi Chip,

Now the column/range "NameValues" does not contain anything--it won't
fill with the values of "NamesContatenated" on worksheet change. No
error though.

The column NameValues should always equal NamesContatenated--when a
user happens to modify a name, the worksheet change event should fire
and the values should be updated. Possible?

Thanks, Eric

There must be a way!!!--set cells' values equal to calculated values in another range
 

Did you step through the code in the Change event? Are the ranges on the
same worksheet?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Arnold" wrote in message
oups.com...
Hi Chip,

Now the column/range "NameValues" does not contain anything--it won't
fill with the values of "NamesContatenated" on worksheet change. No
error though.

The column NameValues should always equal NamesContatenated--when a
user happens to modify a name, the worksheet change event should fire
and the values should be updated. Possible?

Thanks, Eric

There must be a way!!!--set cells' values equal to calculated values in another range
 

The ranges are on the same worksheet.

Right now, if NamesContatenated changes, NamesValues does not change at
all.

Note that I've pasted special--values (names) into the NamesValues
column/range, as this is what the sheet would look like if the code did
work.

The code is:

If Not Application.Intersect(Target, Range("NamesContatenated")) Is
Nothing Then
Application.EnableEvents = False
Range("NamesContatenated").Copy Destination:=Range("NamesValues")
Application.EnableEvents = True
End If

Also note that "NamesContatenated" is a calculated column based on
columns A and B, LastName and Firstname. NamesContatenated contains a
simple formula that combines LastName and FirstName and inserts a comma
in between.

So, NamesContatenated only changes when a Firstname or Lastname is
changed. If a firstname or lastname remains the same on worksheet
change, NamesContatenated, and subsequently, NamesValues, should also
remain the same.

There must be a way!!!--set cells' values equal to calculated values in another range
 

Make sure events are turned on

manually run a macro like this

Sub TurnOnEvents()
Application.EnableEvents = True
End sub

that will enable events.

Since you have a name like NamesConcatentated (and say "calculated values"),
perhaps this range contains formulas and changes are made in another cell.
If so, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng1 As Range
If Target.Count 1 Then Exit Sub
On Error Resume Next
Set rng = Target.Dependents
On Error GoTo 0
If Not rng Is Nothing Then
Set rng1 = Union(rng, Target)
Else
Set rng1 = Target
End If

On Error GoTo ErrHandler

If Not Application.Intersect(rng1, _
Application.Range("NamesConcatenated")) Is Nothing Then
Application.EnableEvents = False
Application.Range("NameValues").Value = _
Application.Range("NamesConcatenated").Value
End If
ErrHandler:
Application.EnableEvents = True
If Err.Number < 0 Then _
MsgBox "Error occured " & Err.Number & vbNewLine & _
Err.Description
End Sub

--
Regards,
Tom Ogilvy


"Arnold" wrote in message
oups.com...
Hi Chip,

Now the column/range "NameValues" does not contain anything--it won't
fill with the values of "NamesContatenated" on worksheet change. No
error though.

The column NameValues should always equal NamesContatenated--when a
user happens to modify a name, the worksheet change event should fire
and the values should be updated. Possible?

Thanks, Eric

There must be a way!!!--set cells' values equal to calculated values in another range
 

Tom and Chip,

You guys are great. That appeared to do the trick. Happy New Year.

There must be a way!!!--set cells' values equal to calculated values in another range
 

Thanks Tom and Chip,

It appeared that application events was part of the problem. The code
appears to work now. However, is it possible to use the column
"NameValues", which is now set with code, as a source for a data
validation list--basically create a named range "NameValues" and then
set the range as the source for drop-down lists in another sheet.

I just tried, and Excel stalled out, but 30 sec, showed the drop-down
arrow and no data in the list. When selected, the first row (name)
appeared and could be entered into the cell, but no other names could
be seen or selected. This is my very last issue to resolve for
hopefully a long while. Thanks,

Eric

There must be a way!!!--set cells' values equal to calculated values in another range
 

Insert=Name=Define

Name: NameValues
Refersto:=Sheets1!$A$1:$A$10

in Data validation (equal sign is necessary)

=NameValues

So using a defined name works for getting values from other sheets. It is
unclear what you are doing incorrectly.

--
Regards,
Tom Ogilvy


"Arnold" wrote in message
oups.com...
Thanks Tom and Chip,

It appeared that application events was part of the problem. The code
appears to work now. However, is it possible to use the column
"NameValues", which is now set with code, as a source for a data
validation list--basically create a named range "NameValues" and then
set the range as the source for drop-down lists in another sheet.

I just tried, and Excel stalled out, but 30 sec, showed the drop-down
arrow and no data in the list. When selected, the first row (name)
appeared and could be entered into the cell, but no other names could
be seen or selected. This is my very last issue to resolve for
hopefully a long while. Thanks,

Eric