Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting named range in VBA- how to set as formula/reference instead of text string?
I want to toggle between two ranges (i.e. reset the same named range to either option, depending on user input). The named ranges are used by graphs, and show either (option 1) a baseline data point and 12 months of current data, or (option 2) 24 months of data. I tried doing this change on the worksheet and ran into big problems, and decided I should also look at setting these in VBA and see if it would be easier. The problem is that when I go back into the named range, it shows the correct "formula" but it is returned as a string instead of a reference, e.g. ="OH2!$C$49,OH2!$C$20:$C$31" instead of =OH2!$C$49,OH2!$C$20:$C$31 Any/all help greatly appreciated, Keith R XL97 Here's what I've got so far: ---------------------------------------------------------------------------- ----------------------------- "Active" is a named range =VLOOKUP(Info!$B$2,Info!$B$4:$C$12,2), and works properly- it returns a sheet name, for example, OH2, GMC2, etc. ---------------------------------------------------------------------------- ----------------------------- Public Function ChangeGraph(GType As Range, ActiveSite As Range) 'function can't be put in put in the same cell as Gtype, which is fine If GType.Value = 1 Then ActiveWorkbook.Names.Add Name:="Graph01_A", _ RefersTo:=Evaluate(Names("active").Value) & "!$C$49," & _ Evaluate(Names("active").Value) & "!$C$20:$C$31" ActiveWorkbook.Names.Add Name:="GraphsXAxis", _ RefersTo:=Evaluate(Names("active").Value) & "!$A$7," & _ Evaluate(Names("active").Value) & "!$A$20:$A$31" ElseIf GType.Value = 2 Then ActiveWorkbook.Names.Add Name:="Graph01_A", _ RefersTo:=Evaluate(Names("active").Value) & "!$C$8:$C$31" ActiveWorkbook.Names.Add Name:="GraphsXAxis", _ RefersTo:=Evaluate(Names("active").Value) & "!$A$8:$A$31" Else MsgBox "Only values of 1 or 2 can be accepted in ChangeGraph Function", , "Error: Value Out Of Range" End If 'I don't set Gtype back to a value so it returns zero- which is 'fine because I just use it to trigger the named range change 'while avoiding the volatile issue End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting named range in VBA- how to set as formula/reference instead of text string?
Keith,
Check this code. Note the 2 = signs... ActiveWorkbook.Names.Add Name:="myrange", RefersTo:= _ "=OH2!$C$49,OH2!$C$20:$C$31" In cases like this - I like to build the string from pieces of code and than substitute the string in the code (easier to trouble shoot) ActiveWorkbook.Names.Add Name:="myrange", RefersTo:= mystring if you decide to do this, make sure your string starts with an = sign. steve "Keith R" wrote in message news:01c3551c$1041d160$476a1bac@PC12001... I want to toggle between two ranges (i.e. reset the same named range to either option, depending on user input). The named ranges are used by graphs, and show either (option 1) a baseline data point and 12 months of current data, or (option 2) 24 months of data. I tried doing this change on the worksheet and ran into big problems, and decided I should also look at setting these in VBA and see if it would be easier. The problem is that when I go back into the named range, it shows the correct "formula" but it is returned as a string instead of a reference, e.g. ="OH2!$C$49,OH2!$C$20:$C$31" instead of =OH2!$C$49,OH2!$C$20:$C$31 Any/all help greatly appreciated, Keith R XL97 Here's what I've got so far: -------------------------------------------------------------------------- -- ----------------------------- "Active" is a named range =VLOOKUP(Info!$B$2,Info!$B$4:$C$12,2), and works properly- it returns a sheet name, for example, OH2, GMC2, etc. -------------------------------------------------------------------------- -- ----------------------------- Public Function ChangeGraph(GType As Range, ActiveSite As Range) 'function can't be put in put in the same cell as Gtype, which is fine If GType.Value = 1 Then ActiveWorkbook.Names.Add Name:="Graph01_A", _ RefersTo:=Evaluate(Names("active").Value) & "!$C$49," & _ Evaluate(Names("active").Value) & "!$C$20:$C$31" ActiveWorkbook.Names.Add Name:="GraphsXAxis", _ RefersTo:=Evaluate(Names("active").Value) & "!$A$7," & _ Evaluate(Names("active").Value) & "!$A$20:$A$31" ElseIf GType.Value = 2 Then ActiveWorkbook.Names.Add Name:="Graph01_A", _ RefersTo:=Evaluate(Names("active").Value) & "!$C$8:$C$31" ActiveWorkbook.Names.Add Name:="GraphsXAxis", _ RefersTo:=Evaluate(Names("active").Value) & "!$A$8:$A$31" Else MsgBox "Only values of 1 or 2 can be accepted in ChangeGraph Function", , "Error: Value Out Of Range" End If 'I don't set Gtype back to a value so it returns zero- which is 'fine because I just use it to trigger the named range change 'while avoiding the volatile issue End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining values and text to make a reference for "named range" | Excel Worksheet Functions | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
HOW TO EXTRACT CELL REFERENCE AS TEXT FROM NAMED RANGE | Excel Worksheet Functions | |||
Using a formula to create named range reference | Excel Worksheet Functions | |||
Setting character attributes in a text string | Excel Worksheet Functions |