Using values defined in Name Manager from VBA

[cbuchman]

I am new to this forum and VBA in Excel. I have a routine that I've managed to cobble together that performs erases any values in adjacent columns when the value in the current column is changed. I also need to provide a default value in a cell 3 columns over. I defined a name in the Name Manager (Task_Dept_Default) and just want to set this value into the cell. This is in a table and I didn't want to use a formula to set the default since the user can overwrite it and hence erase the formula causing problem in the table.

The line is written into the routine is:

Target.Offset(0, 3).Value = ThisWorkbook.Names("Task_Dept_Default").Value

(Target is passed into to the routine and is the current cell)

However, this statement puts the formula for Task_Dept_Default into the cell and not the resultant value. I'm sure there is a simple solution to this, I'm just having trouble finding it.

Many thanks
Carrie

 

[cstimart]

Try something like...

ThisWorkbook.Names.Add Name:="Task_Dept_Default", RefersTo:=Target.Offset(0, 3)

 

[cbuchman]

Try something like...

ThisWorkbook.Names.Add Name:="Task_Dept_Default", RefersTo:=Target.Offset(0, 3)

OK this is not intuitively obvious. So please forgive me..... This looks like it sets the value in Name manager to the value that is in Target.offset(0.3) when I need it the other way around. Here is the entire routine so that you can see it in context.
(all suggestion for improvement, greatly appreciated!)

Private Sub Worksheet_Change(ByVal Target As Range)
'This routine deletes values in Activity (Column 3) and Activiy Descriptor (Column4)
'when task (Column 2) is modified. This is done to eliminate the possibility
'of incorrect data combinations being used.

Dim oldVal As String
Dim newVal As String


On Error Resume Next
On Error GoTo exitHandler

Application.EnableEvents = False


Select Case Target.Column
Case 3
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Value <> oldVal Then
Target.Offset(0, 1).Select
Selection.ClearContents
Target.Offset(0, 2).Select
Selection.ClearContents
Target.Select
End If


Case 4
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal

If Target.Value <> oldVal Then
Target.Offset(0, 1).Select
Selection.ClearContents
Target.Select
End If

'Move the value in "Task_Dept_Default" into the cell 4 columns over.
ThisWorkbook.Names.Add Name:="Task_Dept_Default", RefersTo:=Target.Offset(0, 4)


End Select

exitHandler:
Application.EnableEvents = True
End Sub

 

[cstimart]

If you need it the other way around...

Target.Offset(0, 3)=[Task_Dept_Default]

Note: I'm on my mobile, so reading the code is difficult, so I am going off the first part of your post.

 

[cbuchman]

Thanks for your help! When I saw this and thought, Oh My I was only missing the [], but alas, this doesn't appear to work either. It appears to copy the null string into the cell and Task_dept_Default is not a null string. At this point, at least until I can devise a more elegant solution, I am placing the Task_dept_Default in a column at the end of my table and then copying it into the cell using Target.offset(0,4).Value = target (0,11).Value. This is my very first attempt at VBA and I admit it is much harder than I expected, even for someone with load s of traditional programming experience. I'll need to get some good reference and/or tutorial books.