Hi All,

I'm using the below code to fill a listbox from items in a range starting from
cell B1. (Saw it the other day on these forums, from a question by Al...)
-------------------------------------
Private Sub UserForm_Initialize()
Dim r, c As Range
With Sheets("Sheet1")
Set r = .Range(.Range("B1"), .Range("B" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With
End Sub
-------------------------------------
What i'm trying to do is depending on the item selected, modify the cell
next to it
from a text box on the same form.

So if item "CMS-D1" is selected from the list, ("CMS-D1" sits in cell B7), i
need
to change cell C7 with what is is textbox1.

Im having problems getting the cell reference depending on the item selected.

Any help would be appreciated.

Cheer.
Tony B.

As long as you are starting in B1

dim r as Range

set r = Range("B1")(userform1.Listbox1.ListIndex + 1,2)

to explain
Range("B1")(1,2)
gives you C1 and Range("B1")(1,1) is B1

Range("B1")(4,2)
is cell C4

since Listbox1.Listindex is zero based, we add 1 to it to get the selected
item.

--
Regards,
Tom Ogilvy




"Tony Barla" wrote in message
...
Hi All,

I'm using the below code to fill a listbox from items in a range starting
from
cell B1. (Saw it the other day on these forums, from a question by Al...)
-------------------------------------
Private Sub UserForm_Initialize()
Dim r, c As Range
With Sheets("Sheet1")
Set r = .Range(.Range("B1"), .Range("B" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With
End Sub
-------------------------------------
What i'm trying to do is depending on the item selected, modify the cell
next to it
from a text box on the same form.

So if item "CMS-D1" is selected from the list, ("CMS-D1" sits in cell B7),
i
need
to change cell C7 with what is is textbox1.

Im having problems getting the cell reference depending on the item
selected.

Any help would be appreciated.

Cheer.
Tony B.

What? You're not going to chastise the poster for using
a "cute" subject line?


-----Original Message-----
As long as you are starting in B1

dim r as Range

set r = Range("B1")(userform1.Listbox1.ListIndex + 1,2)

to explain
Range("B1")(1,2)
gives you C1 and Range("B1")(1,1) is B1

Range("B1")(4,2)
is cell C4

since Listbox1.Listindex is zero based, we add 1 to it to
get the selected
item.

--
Regards,
Tom Ogilvy




"Tony Barla" wrote
in message
...
Hi All,

I'm using the below code to fill a listbox from items
in a range starting
from
cell B1. (Saw it the other day on these forums, from a
question by Al...)
-------------------------------------
Private Sub UserForm_Initialize()
Dim r, c As Range
With Sheets("Sheet1")
Set r = .Range(.Range("B1"), .Range("B" &
Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With
End Sub
-------------------------------------
What i'm trying to do is depending on the item
selected, modify the cell
next to it
from a text box on the same form.

So if item "CMS-D1" is selected from the list, ("CMS-
D1" sits in cell B7),
i
need
to change cell C7 with what is is textbox1.

Im having problems getting the cell reference depending
on the item
selected.

Any help would be appreciated.

Cheer.
Tony B.



.

I didn't chastise anyone. I simply pointed out that being as clear and
straight forward as possible would improve the ability of people to provide
you with the solutions you requested. Apparently you found that offensive.

--
Regards,
Tom Ogilvy

"Patch" wrote in message
...
What? You're not going to chastise the poster for using
a "cute" subject line?


-----Original Message-----
As long as you are starting in B1

dim r as Range

set r = Range("B1")(userform1.Listbox1.ListIndex + 1,2)

to explain
Range("B1")(1,2)
gives you C1 and Range("B1")(1,1) is B1

Range("B1")(4,2)
is cell C4

since Listbox1.Listindex is zero based, we add 1 to it to
get the selected
item.

--
Regards,
Tom Ogilvy




"Tony Barla" wrote
in message
...
Hi All,

I'm using the below code to fill a listbox from items
in a range starting
from
cell B1. (Saw it the other day on these forums, from a
question by Al...)
-------------------------------------
Private Sub UserForm_Initialize()
Dim r, c As Range
With Sheets("Sheet1")
Set r = .Range(.Range("B1"), .Range("B" &
Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With
End Sub
-------------------------------------
What i'm trying to do is depending on the item
selected, modify the cell
next to it
from a text box on the same form.

So if item "CMS-D1" is selected from the list, ("CMS-
D1" sits in cell B7),
i
need
to change cell C7 with what is is textbox1.

Im having problems getting the cell reference depending
on the item
selected.

Any help would be appreciated.

Cheer.
Tony B.



.

Lol.
I know, took me a few minutes to find my post too.
Silly me. Was a bit worked up trying to figure out my problem.
First time trying to do userforms, and all places i've looked for help dont
have specifically what i wanted. Also trying to find what your after when you
dont know how to put it into writting doesn't help too.

But a credit to this newsgroup/forum. You all seem to know what it is people
are after.

Cheers.
Tony B.


"Patch" wrote:

What? You're not going to chastise the poster for using
a "cute" subject line?


-----Original Message-----
As long as you are starting in B1

dim r as Range

set r = Range("B1")(userform1.Listbox1.ListIndex + 1,2)

to explain
Range("B1")(1,2)
gives you C1 and Range("B1")(1,1) is B1

Range("B1")(4,2)
is cell C4

since Listbox1.Listindex is zero based, we add 1 to it to
get the selected
item.

--
Regards,
Tom Ogilvy




"Tony Barla" wrote
in message
...
Hi All,

I'm using the below code to fill a listbox from items
in a range starting
from
cell B1. (Saw it the other day on these forums, from a
question by Al...)
-------------------------------------
Private Sub UserForm_Initialize()
Dim r, c As Range
With Sheets("Sheet1")
Set r = .Range(.Range("B1"), .Range("B" &
Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With
End Sub
-------------------------------------
What i'm trying to do is depending on the item
selected, modify the cell
next to it
from a text box on the same form.

So if item "CMS-D1" is selected from the list, ("CMS-
D1" sits in cell B7),
i
need
to change cell C7 with what is is textbox1.

Im having problems getting the cell reference depending
on the item
selected.

Any help would be appreciated.

Cheer.
Tony B.



.

Thanks Tom,

That will help heaps. I was trying stuff like rowcount and rowsource etc.
Plus i wasn't referrencing the userform, just the listbox. hehe.
Greatly appreciated.
Tony B.

"Tom Ogilvy" wrote:

As long as you are starting in B1

dim r as Range

set r = Range("B1")(userform1.Listbox1.ListIndex + 1,2)

to explain
Range("B1")(1,2)
gives you C1 and Range("B1")(1,1) is B1

Range("B1")(4,2)
is cell C4

since Listbox1.Listindex is zero based, we add 1 to it to get the selected
item.

--
Regards,
Tom Ogilvy




"Tony Barla" wrote in message
...
Hi All,

I'm using the below code to fill a listbox from items in a range starting
from
cell B1. (Saw it the other day on these forums, from a question by Al...)
-------------------------------------
Private Sub UserForm_Initialize()
Dim r, c As Range
With Sheets("Sheet1")
Set r = .Range(.Range("B1"), .Range("B" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With
End Sub
-------------------------------------
What i'm trying to do is depending on the item selected, modify the cell
next to it
from a text box on the same form.

So if item "CMS-D1" is selected from the list, ("CMS-D1" sits in cell B7),
i
need
to change cell C7 with what is is textbox1.

Im having problems getting the cell reference depending on the item
selected.

Any help would be appreciated.

Cheer.
Tony B.

Hi,

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Dim RNG_B As Range
Dim RNG_C As Range
Dim CL As Range

Private Sub UserForm_Initialize()

Set RNG_B = Range("Sheet1!B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each CL In RNG_B
ListBox1.AddItem CL
Next

End Sub

Private Sub ListBox1_Click()

Set RNG_C = RNG_B.Offset(0, 1)
With ListBox1
If .ListIndex -1 Then
RNG_C.ClearContents
RNG_C(.ListIndex + 1, 1).Value = .List(.ListIndex)
End If
End With
e:

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--