Referencing a Defined Name

[Patrick Simonds]

Below is a snippet of code I use to place data from a userform onto a
worksheet and to return a value to the userform.

I am recreating my worksheet and am using Defined Names for many of the
cells. I want TextBox1.Text to be placed in Start_1, TextBox2.Text to be
placed in End_1.

Start_1 is defined as a range AV3 through AV36. Not sure if that is
important or not. But of course if I am on row 12 I want the
data in TextBox1 (TextBox1.Text) to be placed in the start_1 on row 12.

So can I reference the defined name and achieve the same result.



TextBox1.Text = rng(1, 47).Text
TextBox2.Text = rng(1, 48).Text
TextBox3.Text = rng(1, 50).Value
TextBox3.Text = Format(TextBox3.Text, "0.0")
TextBox4.Text = rng(1, 53).Text

 

[Richard Buttrey]

Below is a snippet of code I use to place data from a userform onto a
worksheet and to return a value to the userform.

I am recreating my worksheet and am using Defined Names for many of the
cells. I want TextBox1.Text to be placed in Start_1, TextBox2.Text to be
placed in End_1.

Start_1 is defined as a range AV3 through AV36. Not sure if that is
important or not. But of course if I am on row 12 I want the
data in TextBox1 (TextBox1.Text) to be placed in the start_1 on row 12.

So can I reference the defined name and achieve the same result.



TextBox1.Text = rng(1, 47).Text
TextBox2.Text = rng(1, 48).Text
TextBox3.Text = rng(1, 50).Value
TextBox3.Text = Format(TextBox3.Text, "0.0")
TextBox4.Text = rng(1, 53).Text

Conceptually: you could read the active cell row and column number and
use this in a range.cells(row,col) piece of code attached to the click
or change event of the text box.

Rgds






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Patrick Simonds]

By using the Defined Name I would not have to go in and edit all the
references when it became necessary to add a column.