Hi All,

I got it to work. Thanks for the help!


--
Thanks for your help.
Karen53


"Karen53" wrote:

Hi

I've found part of the problem and fixed it. I still have a problem
remaining.

I have a pool list which is a drop down list. A choice from this list is
saved associated with a line item.

I can change the names of the various pools, I can add, I can delete with no
problem. If I change the name of a pool, I use Replace to change that pool
to the new pool on the line Items list. Replace works just fine....UNTIL I
edit the line items. Once I edit the line items, Replace stops working. If
I manually replace the values, replace starts working again, until I again
edit the line items. Then it stops working again.

I've already posted the other procedures here. I've posted the others that
may apply below. Since everything feeds off the pool list, there can't be
any additional spaces, etc. Can anyone tell me why replace stops working?


Sub SaveLineItems(ItemCol, AmountCol, PoolCol, ItemRange, PoolTypeCol,
LineItemFCell, _
ntLineItemFCell, LineItemLCol,
IntLineItemFRow)

'upload the chosen item to frmPoolList for editing

Debug.Print "Starting SaveLineItems"

Dim Choice As Long
Dim ItemAmount As String
Dim LineItem As String
Dim ItemPool As String
Dim ItemPoolIndex As Long

Choice = frmPoolList.lboPoolList.ListIndex + 4 'plus 4 because the
listbox begins with 0
ItemPoolIndex = frmPoolList.lboLineItemPool.ListIndex + 4 'items begin
in row 4, list box is 0
Debug.Print "SaveLI ItemCol " & ItemCol & " Choice " & Choice


LineItem = frmPoolList.txtNewPoolType.Value
ItemAmount = frmPoolList.txtLineItemAmount.Value

Call SaveItems(ItemCol, AmountCol, PoolCol, Choice, LineItem,
ItemAmount, _
ItemPoolIndex, PoolTypeCol)

End Sub


Sub SaveItems(ItemCol, AmountCol, PoolCol, Choice, LineItem, ItemAmount, _
ItemPoolIndex, PoolTypeCol)

'Save the data

Debug.Print "Starting SaveItems"

Dim ws As Worksheet

Set ws = Tablespg

With ws
'save line item
Debug.Print "ItemCol " & ItemCol & " Choice " & Choice
With .Range(ItemCol & Choice)
.HorizontalAlignment = xlLeft
.Value = LineItem
End With
'save amount
If ItemAmount = "" Then
.Range(AmountCol & Choice).Value = ItemAmount
Else
With .Range(AmountCol & Choice)
.Value = Val(ItemAmount)
.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)"
End With
End If
'save the associated pool
Debug.Print "SaveItems Pool " & .Range(PoolTypeCol &
ItemPoolIndex).Value
.Range(PoolCol & Choice).Value = .Range(PoolTypeCol &
ItemPoolIndex).Value
With .Range(PoolCol & Choice)
.HorizontalAlignment = xlCenter
.WrapText = True
End With

'fit the row
Call AddSheets.UnProtectSht(Replace(Tablespg.Name, "'", "''"))
.Rows(Choice & ":" & Choice).EntireRow.AutoFit
Call AddSheets.ProtectSht(Replace(Tablespg.Name, "'", "''"))

End With

End Sub


---
Thanks for your help.
Karen53


"Karen53" wrote:

Hi,

I have been unable to locate what is happening and am hoping someone can
provide some insight.

I have a variable, 'ChoiceIndex', where I save what index the user chose in
a listbox so I can reset it back to their choice when resetting the listbox.
My debug.prints all indicate the correct information, Column 'U' and row
'72', up until the reset then all of a sudden ChoiceIndex is '68' instead of
'72'.

I performed a Find for 'ChoiceIndex' on the entire project and this variable
simply does not exist anywhere else for it to change. It only exists in this
procedure.

The error message is #380.
Could not set the ListIndex property. Invalid property value.

Does anyone have any ideas as to what is happening?

Here's the procedu

Sub UpdateLineItem()

'save the changes to the Line Item lists
'command button from frmPoolList, btn = cmdUpdate

Debug.Print "Starting UpdateLineItem " & Application.ScreenUpdating

Dim ItemFRow As Long 'first row of Line Items - Tablespg
Dim ItemCol As String 'column of the Line Item - Tablespg Line
Items ranges
Dim AmountCol As String 'column of the Line Item amount -
Tablespg Line Items ranges
Dim ItemRange As String 'range to hold the specific name of
range being used
Dim PoolCol As String 'column for pool assigned to Line Item -
Tablespg Line Items ranges
Dim PoolTypeCol As String 'PoolType column in PoolTypes ranges
Dim LineItemFCell As String 'first cell of the Exterior Line Items -
LineItemspg
Dim IntLineItemFCell As String 'first cell of the Interior Line Items -
LineItemspg
Dim IntLineItemFRow As Long 'first row of the Interior Line Items -
LineItemspg
Dim LineItemLCol As String 'Last column of Line Items - LineItemspg
Dim Good As Boolean 'Validate Data
Dim ChoiceIndex As Long 'save the user's choice

Application.ScreenUpdating = False

ChoiceIndex = frmPoolList.lboPoolList.ListIndex

Call ValidateItemData(Good)

If Good = False Then
GoTo NoGood
Else
'determine which Item List to save
If frmPoolList.lboPoolList.RowSource =
Worksheets(Replace(Tablespg.Name, "", "''")).Range _

("CAMLineItemsExterior").Address(external:=True ) Then
Call CAMLineItemExteriorLocations(ItemFRow, ItemCol, AmountCol,
PoolCol, ItemRange, _
PoolTypeCol,
LineItemFCell, LineItemLCol)
Debug.Print "Update Ext LI ItemCol " & ItemCol


Debug.Print "UpdateLineItem Ext call SaveLineItems " &
Application.ScreenUpdating
Call SaveLineItems(ItemCol, AmountCol, PoolCol, ItemRange,
PoolTypeCol, LineItemFCell, _
IntLineItemFCell,
LineItemLCol, IntLineItemFRow)

Debug.Print "UpdateLineItem Ext call UpdateLIneItemsPage " &
Application.ScreenUpdating
Call UpdateLineItemsPage(ItemFRow, ItemCol, AmountCol, PoolCol,
ItemRange, PoolTypeCol, _
LineItemFCell, IntLineItemFCell,
LineItemLCol, IntLineItemFRow)
'reset the rowsource for updates
With frmPoolList.lboPoolList
.RowSource = ""
.RowSource = Worksheets(Replace(Tablespg.Name, "",
"''")).Range _
(ItemRange).Address(external:=True)
End With

ElseIf frmPoolList.lboPoolList.RowSource =
Worksheets(Replace(Tablespg.Name, "", "''")).Range _

("CAMLineItemsInterior").Address(external:=True ) Then
Call CAMLIneItemInteriorLocations(ItemFRow, ItemCol, AmountCol,
PoolCol, ItemRange, _
PoolTypeCol, IntLineItemFCell, LineItemLCol,
IntLineItemFRow, LineItemFCell)
Debug.Print "Update Int LI ItemCol " & ItemCol


Debug.Print "UpdateLineItem Int call SaveLineItems " &
Application.ScreenUpdating
Call SaveLineItems(ItemCol, AmountCol, PoolCol, ItemRange,
PoolTypeCol, LineItemFCell, _
IntLineItemFCell,
LineItemLCol, IntLineItemFRow)

Debug.Print "UpdateLineItem Int call UpdateLIneItemsPage " &
Application.ScreenUpdating
Call UpdateLineItemsPage(ItemFRow, ItemCol, AmountCol, PoolCol,
ItemRange, PoolTypeCol, _
LineItemFCell, IntLineItemFCell,
LineItemLCol, IntLineItemFRow)
'reset the rowsource for updates
With frmPoolList.lboPoolList
.RowSource = ""
.RowSource = Worksheets(Replace(Tablespg.Name, "",
"''")).Range _
(ItemRange).Address(external:=True)
End With
End If

If frmPoolList.lboPoolList.RowSource =
Worksheets(Replace(Tablespg.Name, "", "''")).Range _

("TaxLineItems").Address(external:=True) Then
Call TaxLineItemLocations(ItemFRow, ItemCol, AmountCol, PoolCol,
ItemRange, _
PoolTypeCol, LineItemFCell,
LineItemLCol)

Debug.Print "UpdateLineItem Tax call SaveLineItems " &
Application.ScreenUpdating
Call SaveLineItems(ItemCol, AmountCol, PoolCol, ItemRange,
PoolTypeCol, LineItemFCell, _
IntLineItemFCell,
LineItemLCol, IntLineItemFRow)
Debug.Print "UpdateLineItem Tax call UpdateLIneItemsPage " &
Application.ScreenUpdating
Call UpdateLineItemsPage(ItemFRow, ItemCol, AmountCol, PoolCol,
ItemRange, PoolTypeCol, _
LineItemFCell, IntLineItemFCell,
LineItemLCol, IntLineItemFRow)
'reset the rowsource for updates
With frmPoolList.lboPoolList
.RowSource = ""
.RowSource = Worksheets(Replace(Tablespg.Name, "",
"''")).Range _
(ItemRange).Address(external:=True)
End With
End If
End If

NoGood:

'Reset
'clear the textbox
frmPoolList.txtNewPoolType.Value = ""
frmPoolList.txtLineItemAmount.Value = ""

'clear the Pool list box
frmPoolList.lboLineItemPool.ListIndex = -1

'reset the PoolList list box to user's choice
Debug.Print "ChoiceIndex = " & ChoiceIndex
frmPoolList.lboPoolList.ListIndex = ChoiceIndex
(((This is where the value changes & error
occurs)))

Application.ScreenUpdating = True

Debug.Print "UpdateLineItem Close " & Application.ScreenUpdating

End Sub

--
Thanks for your help.
Karen53