Change named range value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

To anyone that can help....

I have a couple named ranges to which I like to assign different values
consecutively in a vba sub command.

Defined Named Ranges:
Name Range
-------------- -------------
TestRange1 =TestWorksheet1!$C$5
TestRange2 =TestWorksheet1!$D$5

Basically, in the sub I am able to set the value of TestRange1 or TestRange2
but not both.

Private Sub someSub() 'Called from CommandButton1_click()
Worksheets("TestWorksheet1").Range("TestRange1").value = "TEST Range 1"
Worksheets("TestWorksheet1").Range("TestRange2").value = "TEST Range 2"
End Sub

If i comment out the first line, then the second is executed. I tried to
capture a possible error, but the doesn't seem to be one.

I even tried to set the value through different means:

Private Sub someSub() 'Called from CommandButton1_click()
ActiveWorkbook.Names.Item("TestRange1").RefersToRange.value = "TEST1"
ActiveWorkbook.Names.Item("TestRange2").RefersToRange.value = "TEST2"
End Sub


Does anyone know why I can't assigne more than one value at a time?

TIA,

Joe
 
Hi, I think this meets your request . . .

Sub AddRanges
Worksheets("Sheet1").Names.Add Name:="TestRange1",
RefersTo:="=Sheet1!$A$1"
Worksheets("Sheet1").Names.Add Name:="TestRange2",
RefersTo:="=Sheet1!$A$2"
End Sub

John Mansfield
The Planning Deskbook
http://www.pdbook.com

-----Original Message-----
To anyone that can help....

I have a couple named ranges to which I like to assign different values
consecutively in a vba sub command.

Defined Named Ranges:
Name Range
-------------- -------------
TestRange1 =TestWorksheet1!$C$5
TestRange2 =TestWorksheet1!$D$5

Basically, in the sub I am able to set the value of TestRange1 or TestRange2
but not both.

Private Sub someSub() 'Called from CommandButton1_click()
Worksheets("TestWorksheet1").Range
Click to expand...
("TestRange1").value = "TEST Range 1"
Worksheets("TestWorksheet1").Range
Click to expand...
("TestRange2").value = "TEST Range 2"
End Sub

If i comment out the first line, then the second is executed. I tried to
capture a possible error, but the doesn't seem to be one.

I even tried to set the value through different means:

Private Sub someSub() 'Called from CommandButton1_click()
ActiveWorkbook.Names.Item
Click to expand...
("TestRange1").RefersToRange.value = "TEST1"
ActiveWorkbook.Names.Item
Click to expand...
("TestRange2").RefersToRange.value = "TEST2"
 
Thanks for the tip John, I had tried that too actually.

I just now found the real problem. There was a problem with a Conditional
Formatting event function I was calling. I guess changing the value of my
defined named range would trigger the conditional formatting to do a check
which would subsequently stop all processes when it encountered the problem.
 
Back
Top