Assigning VBA variable to defined name

[alpine_21]

Hello,

I have a user form that imports some defined names into text boxes (via the ControlSource property). The user can edit any of these values, which updates the name in the workbook (which is desired, as there is a chart in the user form which is refreshed upon any changes). In case the user does not want to save the changes, I have backed up the defined names by assigning the values to VBA variables upon userform initialization. All of this works fine.

If the user does not want to save any changes, I want a control button that will write back the VBA variables to the defined names. This is where the issue lies.

For example, I have the name '_Var1' assigned to cell A1 on worksheet 1 with a value of 100.

Here is a snippet of test code:

----------------------

Sub SaveName ()

'Save defined name - executed upon form loading

Dim S_Var1 As Double
S_Var1 = Evaluate(ThisWorkbook.Names("_Var1").RefersTo)

End Sub

---

Sub WriteBackName ()

'Assign VBA var to existing defined name - excuted upon closing/cancelling form via control button

ThisWorkbook.Names("_Var1").Value = S_Var1


----------------------

The form loads and saves the name to a VBA variable (I checked), then the text box is changed to a new value (200), and then the cancel button invoked. The result is that worksheet 1, cell A1 has the new value (200), but it does NOT have a name linked to it and the name is deleted.

I tested further by assigning a constant (e.g., ThisWorkbook.Names("_Var1").Value = 123). This results in the defined name being updated with '123' and remaining active. The name does not have any cell reference in the worksheet though.

I've tried deleting the name then adding it back with the cell reference, but had problems assigning the value...

Is there a way to update a name via a variable and keep the assigned cell/range? I'm using Excel 2003. I'm not very fluent in VBA.

Thanks