MORE listbox code

Jennifer · 05-24-2006, 03:10 PM

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

Tom Ogilvy · 05-24-2006, 03:35 PM

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

Jennifer · 05-24-2006, 04:00 PM

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

Tom Ogilvy · 05-24-2006, 04:15 PM

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

Jennifer · 05-24-2006, 04:55 PM

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

Tom Ogilvy · 05-25-2006, 10:50 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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