Changed variable value

[Karen53]

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 procedure:

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

 

[Chip Pearson]

I would recommend that you set up a Watch on that variable. In VBA, go to
the Debug menu and choose Add Watch. In that dialog, enter ChoiceIndex as
the Expression, select All Procedures in the Procedure drop down, select All
Modules in the Module drop down, and choose Break When Value Changes in the
Watch Type option box. This will cause VBA to pause on the line(s) of code
that change the value of ChoiceIndex.

Also, you should put "Option Explicit" as the very first line in the module
(above and outside of any procedures) to ensure that the variable is
declared and that you don't have a misspelled variable name.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[JLGWhiz]

From the code you posted, the only area that I can see where the ListIndex
would be affected is at the end of each of your three options where you work
with the row source for the list box. If one of those should shorten or
lengthen the rowsource, it could affect that particular ListIndex if the
change was on the front end of the rowsource. I don't know if any of the
subroutines that are called would affect the rowsource or not, but that seems
to be the only way the variable value would change. The ListIndex is
probably the same. Just the value being changed.

 

[Karen53]

HI Chip,

Thanks for your help. I did as you suggested but did not get any results.

My watch says 'Expression not defined in context'. ChoiceIndex is created
in this procedure. The value never changed in the watch window, yet the
value prints in my immediate window.

I think I have two problems and I'm wondering if they are related. For some
reason, I lose Application.EnableUpdates at a time when I shouldn't. So far
I haven't been able to find that either. Could these be related and do you
have any suggestions to help me isolate this issue? The EnableEvents appears
to be somewhat intermediate.
--
Thanks for your help.
Karen53

I would recommend that you set up a Watch on that variable. In VBA, go to
the Debug menu and choose Add Watch. In that dialog, enter ChoiceIndex as
the Expression, select All Procedures in the Procedure drop down, select All
Modules in the Module drop down, and choose Break When Value Changes in the
Watch Type option box. This will cause VBA to pause on the line(s) of code
that change the value of ChoiceIndex.

Also, you should put "Option Explicit" as the very first line in the module
(above and outside of any procedures) to ensure that the variable is
declared and that you don't have a misspelled variable name.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Karen53]

Chip,

I removed the Application.EnableEvents = True statement I had added in
another procedure to solve that problem now my variable ChoiceIndex is fine.
But, the other procedure is not updating the values with the replace.


Sub UpdatePool()

'save the changes to the pool list
'command button from frmPoolList, btn = cmdUpdate

Dim Choice As Long
Dim NewPool As String
Dim OldPool As String
Dim PoolRange As String 'indicates CAM or Tax Pool
Dim IsAssigned As Boolean
Dim NameExists As Boolean
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

Application.ScreenUpdating = False

Choice = frmPoolList.lboPoolList.ListIndex + 1 'plus 1 because the
listbox begins with 0
NewPool = frmPoolList.txtNewPoolType

'determine which Pool List to save to and save
If frmPoolList.lboPoolList.RowSource = Worksheets(Replace(Tablespg.Name,
"", "''")).Range _

("CAMPoolTypes").Address(external:=True) Then
OldPool = Range("CAMPoolTypes").Item(Choice, 1).Value
PoolRange = "CAM"

'check if old pool is assigned to Line Items
If OldPool <> "" Then
If NewPool = "" Then
Call IsPoolAssigned(PoolRange, OldPool, IsAssigned)
If IsAssigned = True Then
GoTo Finished
End If
End If
End If

'check for duplicate pool name
If NewPool <> "" Then
Call PoolNameExists(PoolRange, NewPool, NameExists)
If NameExists = True Then
GoTo Finished
End If
End If

'save the new pool
Range("CAMPoolTypes").Item(Choice, 1).Value = NewPool

If OldPool <> "" Then

'Update Tables page Exterior Range "X" with new value
Tablespg.Range("X4:X304").Replace what:=OldPool,
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Tables Page exterior NewPool " & NewPool & "
OldPool " & OldPool

'Update Tables Page Interior Range "AD" with new value
Tablespg.Range("AD4:AD304").Replace what:=OldPool,
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Tables Page interior NewPool " & NewPool & "
OldPool " & OldPool

'update the line items page
PoolRange = "CAMLineItemsExterior"
Call LineItems.CAMLineItemExteriorLocations(ItemFRow, ItemCol,
AmountCol, PoolCol, PoolRange, _
PoolTypeCol,
LineItemFCell, LineItemLCol)
Call LineItems.UpdateLineItemsPage(ItemFRow, ItemCol, AmountCol,
PoolCol, PoolRange, _
PoolTypeCol, LineItemFCell, IntLineItemFCell,
LineItemLCol, IntLineItemFRow)
'reset the rowsource for edits
With frmPoolList.lboPoolList
.RowSource = ""
.RowSource = Worksheets(Replace(Tablespg.Name, "",
"''")).Range _
("CAMPoolTypes").Address(external:=True)
End With

End If
End If


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

("TaxPoolTypes").Address(external:=True) Then
OldPool = Range("TaxPoolTypes").Item(Choice, 1).Value
PoolRange = "Tax"

'check if old pool is assigned to Line Items
If OldPool <> "" Then
If NewPool = "" Then
Call IsPoolAssigned(PoolRange, OldPool, IsAssigned)
If IsAssigned = True Then
GoTo Finished
End If
End If
End If

'check for duplicate pool name
If NewPool <> "" Then
Call PoolNameExists(PoolRange, NewPool, NameExists)
If NameExists = True Then
GoTo Finished
End If
End If

'save the new pool
Range("TaxPoolTypes").Item(Choice, 1).Value = NewPool

If OldPool <> "" Then

'Update Tables page range "AI" with new value
Tablespg.Range("AI4:AI154").Replace what:=OldPool,
Replacement:=NewPool, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Debug.Print "Tables Page tax NewPool " & NewPool & " OldPool " &
OldPool

'update the line items page
PoolRange = "TaxLineItems"
Call LineItems.CAMLineItemExteriorLocations(ItemFRow, ItemCol,
AmountCol, PoolCol, PoolRange, _
PoolTypeCol,
LineItemFCell, LineItemLCol)
Call LineItems.UpdateLineItemsPage(ItemFRow, ItemCol, AmountCol,
PoolCol, PoolRange, _
PoolTypeCol, LineItemFCell, IntLineItemFCell,
LineItemLCol, IntLineItemFRow)
'reset the rowsource for edits
With frmPoolList.lboPoolList
.RowSource = ""
.RowSource = Worksheets(Replace(Tablespg.Name, "",
"''")).Range _
("TaxPoolTypes").Address(external:=True)
End With

End If
End If

Finished:

'Reset
NewPool = ""
OldPool = ""
'clear the textbox
frmPoolList.txtNewPoolType.Value = ""
frmPoolList.txtLineItemAmount.Value = ""
'set focus to list box
frmPoolList.lboPoolList.SetFocus

Application.ScreenUpdating = True
'Application.EnableEvents = True

End Sub






--
Thanks for your help.
Karen53

I would recommend that you set up a Watch on that variable. In VBA, go to
the Debug menu and choose Add Watch. In that dialog, enter ChoiceIndex as
the Expression, select All Procedures in the Procedure drop down, select All
Modules in the Module drop down, and choose Break When Value Changes in the
Watch Type option box. This will cause VBA to pause on the line(s) of code
that change the value of ChoiceIndex.

Also, you should put "Option Explicit" as the very first line in the module
(above and outside of any procedures) to ensure that the variable is
declared and that you don't have a misspelled variable name.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Karen53]

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

 

[Karen53]

Hi All,

I got it to work. Thanks for the help!