| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changed variable value
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changed variable value
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changed variable value
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" 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changed variable value
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 "Chip Pearson" wrote: 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changed variable value
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 "Chip Pearson" wrote: 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changed variable value
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changed variable value
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format changed when the details in cell changed | Excel Worksheet Functions | |||
| Problem with Public Variable value being changed. | Excel Programming | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Code - if T changed, change date in W to date it is changed | Excel Programming | |||
Linear Mode
