Modifying a Defined Name's Contents

  • Thread starter Thread starter kittronald
  • Start date Start date
K

kittronald

I'm trying to create a macro that will modify the Refers to: field of a
defined name by doing the following:

1) Create a UDF called "SEARCHNAME" that searches the Refers to: field
of a defined name and returns a matched value.

For example, =SEARCHNAME(find_text As Range, within_text As Range)
where find_text is a named range containing text values and within_text is
the Refers to: field of a defined name.

2) Set value "a" = to the Refers to: field of the name "Test_Formula"
which contains =SUM(1,2).

3) Using the function SEARCHNAME, iterate through the values in the name
"Functions" and see if they occur in value "a" and set the value "b" as the
found value.

The name "Functions" refers to the range A1:A2 where A1 contains
the text SUM and A2 contains the text PRODUCT.

4) Set value "c" = to the selected item in ComboBox1 that contains the
text values SUM and PRODUCT.

5) Modify the Refers to: field in the name "Test_Formula" by replacing
value "b" with "c"


I'm not a developer, but I was thinking along the lines of the
following:

Sub Macro_Modify_Name

a = ActiveWorkbook.Names("Test_Formula").RefersToRange

b = SEARCHNAME("Functions","Test_Formula")

c = selected value of ComboBox1

d = Application.Substitute(a,b,c)

ActiveWorkbook.Names("Test_Formula").RefersToRange = d

End Sub


Any ideas ?


- Ronald K.
 
Ron,
Here's a procedure I use for wks UI settings. The procedure retrieves
UI settings that are stored in local defined names for each wks. It
should give you enough of an idea to get you where you want to go.

(Watch for line wraps)

'Defined names used for DEFAULT UI Settings
Private Const msUI_SETTINGS As String = "uiProgRows,uiProgCols," _
& "uiScrollArea,uiSelect," _
& "uiFilter,uiOutline," _
& "uiOutlineR,uiOutlineC," _
& "uiRowColHdrs,uiProtect," _
& "uiIsSet,uiVisible"


Sub Setup_WksUI(Optional Wks As Worksheet)
Dim sz As String
Dim vSetting, vSettings
Dim i As Integer

If Wks Is Nothing Then Set Wks = ActiveSheet

vSettings = Split(msUI_SETTINGS, ",")

'The sheet must be visible and not protected to do this
Wks.Unprotect PWRD
Wks.Visible = xlSheetVisible

For i = LBound(vSettings) To UBound(vSettings)
'Determine if the current sheet requires the current setting
vSetting = Empty
On Error Resume Next
If vSettings(i) = "uiScrollArea" Then '//it's a range object
Set vSetting = Application.Evaluate("'" & Wks.name _
& "'!" & vSettings(i))
Else '//it's a stored value
vSetting = Wks.Names(vSettings(i)).RefersTo
If Not (vSetting = Empty) Then _
vSetting = Application.Evaluate("'" & Wks.name _
& "'!" & vSettings(i))
End If 'rngCell.Value = "uiScrollArea"
On Error GoTo 0

If Not IsEmpty(vSetting) Then
Select Case vSettings(i)
Case "uiProgRows"
If vSetting > 0 Then _
Wks.Range("A1").Resize(vSetting).EntireRow.Hidden = True
Case "uiProgCols"
If vSetting > 0 Then _
Wks.Range("A1").Resize(, vSetting).EntireColumn.Hidden =
True
Case "uiScrollArea": Wks.ScrollArea = vSetting.address
Case "uiSelect": Wks.EnableSelection = vSetting
Case "uiFilter": Wks.EnableAutoFilter = vSetting
Case "uiRowColHdrs"
Wks.Activate: _
Application.ActiveWindow.DisplayHeadings = vSetting
Case "uiProtect": If vSetting Then wksProtect Wks.name
Case "uiVisible": Wks.Visible = vSetting
Case "uiOutline": Wks.EnableOutlining = vSetting

'Persist any changes the user makes during runtime
Case "uiOutlineR"
If Application.Evaluate("'" & Wks.name _
& "'!" & "uiSet") = 0 Then _
Wks.Outline.ShowLevels RowLevels:=vSetting: _
Wks.Names("uiSet").RefersTo = "=1"
Case "uiOutlineC"
If Application.Evaluate("'" & Wks.name _
& "'!" & "uiSet") = 0 Then _
Wks.Outline.ShowLevels ColumnLevels:=vSetting: _
Wks.Names("uiSet").RefersTo = "=1"
End Select 'Case vSettings(i)
End If 'Not IsEmpty(vSetting)
Next
End Sub 'Setup_WksUI()

**Note that the last 2 Select Case conditions don't work if both rows
AND columns have Outline levels. Since I most always do one OR the
other, I didn't account for both. In that scenario, "uiOutlineC" will
never happen without modifying this procedure.

HTH
 
Garry,

I appreciate the tremendous amount of code in your response.

After going over it with my limited coding skills, I haven't been able
to extrapolate the parts that would allow me to modify a defined name's
Refers to: field.



- Ronald K.
 
Garry,

I appreciate the tremendous amount of code in your response.

After going over it with my limited coding skills, I haven't been able to
extrapolate the parts that would allow me to modify a defined name's Refers
to: field.



- Ronald K.
Click to expand...

Ronald,
In the section following this comment...

'Persist any changes the user makes during runtime

...you'll find code examples to both read/write a defined name's
RefersTo. Reading a value uses Evaluate(); writing the value uses the
'=' operator same as any other value.
 
GS laid this down on his screen :
Ronald,
In the section following this comment...

'Persist any changes the user makes during runtime

..you'll find code examples to both read/write a defined name's RefersTo.
Reading a value uses Evaluate(); writing the value uses the '=' operator same
as any other value.
Click to expand...

Just to be clear.., the defined names in my example have local scope
and so belong to the worksheet names collection...

ActiveSheet.Names("<name>").RefersTo = "<value>"



If your defined name has global scope then...

ActiveWorkbook.Names("<name>").RefersTo = "<value>"
 
Garry,

When I enter the following, Error 2029 occurs in the Immediate Window
.... why ?

Dim a As Variant

a =
Application.Evaluate(ActiveWorkbook.Names("Fill_Formula").RefersTo)
Debug.Print a



- Ronald K.
 
Garry,

When I enter the following, Error 2029 occurs in the Immediate Window ...
why ?

Dim a As Variant

a =
Application.Evaluate(ActiveWorkbook.Names("Fill_Formula").RefersTo)
Debug.Print a



- Ronald K.
Click to expand...

What is error 2029?

Why not just enter...

?Application.Evaluate(ActiveWorkbook.Names("Fill_Formula"))

...without .RefersTo. What made you think you needed to include
..RefersTo to read the valu? My code sample doesn't show that! You only
need to use .RefersTo to write the value...
 
Garry,

Finally got it working.

Sub Macro_Change_Function()
'
' Macro_Change_Function Macro
'

'

x = GetRefersTo("Fill_Formula")
a = Sheets("Settings").Range("Current_Function")
b = Sheets("Settings").Range("Selected_Function")
y = Application.Substitute(x, a, b)
ActiveWorkbook.Names("Fill_Formula").RefersTo = y
End Sub


Thanks for all the help.



- Ronald K.
 
Garry,

Finally got it working.

Sub Macro_Change_Function()
'
' Macro_Change_Function Macro
'

'

x = GetRefersTo("Fill_Formula")
a = Sheets("Settings").Range("Current_Function")
b = Sheets("Settings").Range("Selected_Function")
y = Application.Substitute(x, a, b)
ActiveWorkbook.Names("Fill_Formula").RefersTo = y
End Sub


Thanks for all the help.



- Ronald K.
Click to expand...

Glad to be of any help!
What's important is...
Are you having fun?

Best wishes...
 
Garry,

For the past 90 days, I've been having boat loads of "fun". 8b

But you made the difficult possible.



- Ronald K.
 
Garry,

For the past 90 days, I've been having boat loads of "fun". 8b

But you made the difficult possible.



- Ronald K.
Click to expand...

"Hurdles are only difficult until you know how to get over them! After
that, they are no longer hurdles!"

Thanks for the feedback...
 
Back
Top