MORE listbox code
 

In a listbox that allows multi selections and has multi columns when the user
makes his selections how do you place text such as "PAID" in column F for the
corresponding rows selected on the database worksheet. Note: this worksheet
is the same info filling the list box. Thank you Thank you!
--
Though daily learning, I LOVE EXCEL!
Jennifer

MORE listbox code
 

Assume you ListFillRange holds a string like

Data!B9:E35
(Includes the sheet name)


Set sh = Worksheets("Data")
set rng = Range(Listbox1.ListFillRange).Columns(1).Cells

for i = 0 to listbox1.Listcount - 1
if listbox1.Selected(i) then
rw = rng(i+1).row
sh.cells(rw,"F").Value = "Paid"
end if
Next

--
regards,
Tom Ogilvy




"Jennifer" wrote:

In a listbox that allows multi selections and has multi columns when the user
makes his selections how do you place text such as "PAID" in column F for the
corresponding rows selected on the database worksheet. Note: this worksheet
is the same info filling the list box. Thank you Thank you!
--
Though daily learning, I LOVE EXCEL!
Jennifer

MORE listbox code
 

You lost me Tom on the ListFillRange
this is the code so far that i have adapted from yours . . .
ha I changed 1 thing Ha Ha!
I'm guessing that the ListFillRange has something to do w/the named range
("Database") on the worksheet ("ProduceData")

So right now I'm getting an error saying that the listfillrange is not
defined?

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = Range(lstData.ListFillRange).Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Tom Ogilvy" wrote:

Assume you ListFillRange holds a string like

Data!B9:E35
(Includes the sheet name)


Set sh = Worksheets("Data")
set rng = Range(Listbox1.ListFillRange).Columns(1).Cells

for i = 0 to listbox1.Listcount - 1
if listbox1.Selected(i) then
rw = rng(i+1).row
sh.cells(rw,"F").Value = "Paid"
end if
Next

--
regards,
Tom Ogilvy




"Jennifer" wrote:

In a listbox that allows multi selections and has multi columns when the user
makes his selections how do you place text such as "PAID" in column F for the
corresponding rows selected on the database worksheet. Note: this worksheet
is the same info filling the list box. Thank you Thank you!
--
Though daily learning, I LOVE EXCEL!
Jennifer

MORE listbox code
 

assuming a workbook level Name

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = sh.Range("Database").Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next

assume a sheet level name

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = sh.Range("ProduceData!Database").Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next

--
Regards,
Tom Ogilvy


"Jennifer" wrote:

You lost me Tom on the ListFillRange
this is the code so far that i have adapted from yours . . .
ha I changed 1 thing Ha Ha!
I'm guessing that the ListFillRange has something to do w/the named range
("Database") on the worksheet ("ProduceData")

So right now I'm getting an error saying that the listfillrange is not
defined?

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = Range(lstData.ListFillRange).Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Tom Ogilvy" wrote:

Assume you ListFillRange holds a string like

Data!B9:E35
(Includes the sheet name)


Set sh = Worksheets("Data")
set rng = Range(Listbox1.ListFillRange).Columns(1).Cells

for i = 0 to listbox1.Listcount - 1
if listbox1.Selected(i) then
rw = rng(i+1).row
sh.cells(rw,"F").Value = "Paid"
end if
Next

--
regards,
Tom Ogilvy




"Jennifer" wrote:

In a listbox that allows multi selections and has multi columns when the user
makes his selections how do you place text such as "PAID" in column F for the
corresponding rows selected on the database worksheet. Note: this worksheet
is the same info filling the list box. Thank you Thank you!
--
Though daily learning, I LOVE EXCEL!
Jennifer

MORE listbox code
 

Getting closer but we are still having troubles, it is placing a singe "Paid"
in column "AG" but there doesn't seem to be a real reason for the placement.
Hmmm!

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Dim rng As Range
Set rng = sh.Range("Database").Columns(1).Cells
Dim i As Long
Dim rw As Long

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next


--
Though daily learning, I LOVE EXCEL!
Jennifer


"Tom Ogilvy" wrote:

assuming a workbook level Name

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = sh.Range("Database").Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next

assume a sheet level name

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = sh.Range("ProduceData!Database").Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next

--
Regards,
Tom Ogilvy


"Jennifer" wrote:

You lost me Tom on the ListFillRange
this is the code so far that i have adapted from yours . . .
ha I changed 1 thing Ha Ha!
I'm guessing that the ListFillRange has something to do w/the named range
("Database") on the worksheet ("ProduceData")

So right now I'm getting an error saying that the listfillrange is not
defined?

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = Range(lstData.ListFillRange).Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Tom Ogilvy" wrote:

Assume you ListFillRange holds a string like

Data!B9:E35
(Includes the sheet name)


Set sh = Worksheets("Data")
set rng = Range(Listbox1.ListFillRange).Columns(1).Cells

for i = 0 to listbox1.Listcount - 1
if listbox1.Selected(i) then
rw = rng(i+1).row
sh.cells(rw,"F").Value = "Paid"
end if
Next

--
regards,
Tom Ogilvy




"Jennifer" wrote:

In a listbox that allows multi selections and has multi columns when the user
makes his selections how do you place text such as "PAID" in column F for the
corresponding rows selected on the database worksheet. Note: this worksheet
is the same info filling the list box. Thank you Thank you!
--
Though daily learning, I LOVE EXCEL!
Jennifer

MORE listbox code
 

If the entries in your listbox correspond to the rows of the range Database,
then it should do what you want.

If that is not case, then you would have to search the range database to
find the matching row and place it there.

--
Regards,
Tom Ogilvy

"Jennifer" wrote in message
...
Getting closer but we are still having troubles, it is placing a singe
"Paid"
in column "AG" but there doesn't seem to be a real reason for the
placement.
Hmmm!

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Dim rng As Range
Set rng = sh.Range("Database").Columns(1).Cells
Dim i As Long
Dim rw As Long

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next


--
Though daily learning, I LOVE EXCEL!
Jennifer


"Tom Ogilvy" wrote:

assuming a workbook level Name

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = sh.Range("Database").Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next

assume a sheet level name

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = sh.Range("ProduceData!Database").Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next

--
Regards,
Tom Ogilvy


"Jennifer" wrote:

You lost me Tom on the ListFillRange
this is the code so far that i have adapted from yours . . .
ha I changed 1 thing Ha Ha!
I'm guessing that the ListFillRange has something to do w/the named
range
("Database") on the worksheet ("ProduceData")

So right now I'm getting an error saying that the listfillrange is not
defined?

Dim sh As Worksheet
Set sh = Worksheets("ProduceData")
Set rng = Range(lstData.ListFillRange).Columns(1).Cells

For i = 0 To lstData.ListCount - 1
If lstData.Selected(i) Then
rw = rng(i + 1).Row
sh.Cells(rw, "ag").Value = "Paid"
End If
Next
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Tom Ogilvy" wrote:

Assume you ListFillRange holds a string like

Data!B9:E35
(Includes the sheet name)


Set sh = Worksheets("Data")
set rng = Range(Listbox1.ListFillRange).Columns(1).Cells

for i = 0 to listbox1.Listcount - 1
if listbox1.Selected(i) then
rw = rng(i+1).row
sh.cells(rw,"F").Value = "Paid"
end if
Next

--
regards,
Tom Ogilvy




"Jennifer" wrote:

In a listbox that allows multi selections and has multi columns
when the user
makes his selections how do you place text such as "PAID" in
column F for the
corresponding rows selected on the database worksheet. Note: this
worksheet
is the same info filling the list box. Thank you Thank you!
--
Though daily learning, I LOVE EXCEL!
Jennifer