Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
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 Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
on 9/20/2011, nntp.aioe.org supposed :
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. 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
GS laid this down on his screen :
on 9/20/2011, nntp.aioe.org supposed : 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. 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. 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 Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
Garry,
When I enter the following, Error 2029 occurs in the Immediate Window .... why ? Dim a As Variant a = Application.Evaluate(ActiveWorkbook.Names("Fill_Fo rmula").RefersTo) Debug.Print a - Ronald K. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
on 9/22/2011, kittronald supposed :
Garry, When I enter the following, Error 2029 occurs in the Immediate Window ... why ? Dim a As Variant a = Application.Evaluate(ActiveWorkbook.Names("Fill_Fo rmula").RefersTo) Debug.Print a - Ronald K. What is error 2029? Why not just enter... ?Application.Evaluate(ActiveWorkbook.Names("Fill_F ormula")) ...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 Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
on 10/4/2011, kittronald supposed :
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. Glad to be of any help! What's important is... Are you having fun? Best wishes... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
Garry,
For the past 90 days, I've been having boat loads of "fun". 8b But you made the difficult possible. - Ronald K. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a Defined Name's Contents
on 10/5/2011, kittronald supposed :
Garry, For the past 90 days, I've been having boat loads of "fun". 8b But you made the difficult possible. - Ronald K. "Hurdles are only difficult until you know how to get over them! After that, they are no longer hurdles!" Thanks for the feedback... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Print Name's list and Names Range? | Excel Worksheet Functions | |||
How to create table of cell names with the name's cell address | Excel Discussion (Misc queries) | |||
displaying contents of a user defined directory | Excel Programming | |||
Selecting a defined Name via a cell's contents | Excel Programming |