?
Solved

Update sheet from userform listbox

Posted on 2013-12-01
3
Medium Priority
?
523 Views
Last Modified: 2013-12-01
Hi, I'm struggling with a section of code dealing with a listbox on a userform.

the code is as follows;

'edit the viewing range
    Set findPnum = ws.Range("T:T").find(What:=I, LookAt:=xlWhole)
        If Not (findPnum Is Nothing) Then
        With findPnum
            .Offset(0, 2).Value = Me.txtGood.Value
            .Offset(0, 3).Value = Me.txtRejects.Value
            .Offset(0, 4).Value = Me.txtShipt.Value
        End With
        End If
       
'edit the updating range
    Set findPnum2 = ws.Range("AA:AA").find(What:=I, LookAt:=xlWhole)
        If Not (findPnum2 Is Nothing) Then
        With findPnum2
            .Offset(0, 1).Value = Me.txtGood.Value
            .Offset(0, 2).Value = Me.txtRejects.Value
            .Offset(0, 3).Value = -Me.txtShipt.Value
        End With
        End If

It runs ok (no errors) but it only updates the first item in each group.

That is to say this.........
If Not (findPnum Is Nothing) Then
        With findPnum
            .Offset(0, 2).Value = Me.txtGood.Value

works, but it doesn't' do the next two lines.

it also does this.....
        If Not (findPnum2 Is Nothing) Then
        With findPnum2
            .Offset(0, 1).Value = Me.txtGood.Value

but again not the next two lines.

Am I missing some step because of the With.....End With section?

Although I have also tried it by writing it out fully.

As usual, any help or advice is greatly appreciated.

I have attached the workbook for easier understanding. (the problem is with the "EditItem" procedure
Production.xlsm
0
Comment
Question by:Stephen Byrom
3 Comments
 
LVL 14

Assisted Solution

by:Faustulus
Faustulus earned 400 total points
ID: 39688544
You know, if something that you see can't be true consider the possibility that it isn't. In this case all three cells are properly written to. I suspect they are wiped clean in some code that is running later. Since you know your project, you will be able to find that code faster than I can. I tested the btnEdit_Click procedure by itself. So I can tell you that the cells are still intact at the end of that procedure.
Since I looked over your project in the above context allow me to draw your attention to this code which you use repeatedly,
ExitErrHandler:
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitErrHandler
End Sub
Consider what would happen differently if the line "Resume ExitErrHandler" wouldn't be there. The simple answer is, nothing. Therefore you can replace the above construct with the following simplified version.
   Exit Sub
ErrHandler:
    MsgBox Err.Description
End Sub
Now, if an error occurs execution is continued at the ErrHandler label and the MsgBox is displayed before the sub ends. If no error occurs execution stops at the Exit Sub command.

During development, your On Error Goto statements actually deprive you of valuable information. After completion of the project they can make believe that code that doesn't work actually does, although the user would probably find out anyway. The intended use is different. The Errhandler shouldn't just cry "Boss, there is a mistake somewhere", but actually handle the error. Then, with the Resume command you continue running the code after the casus belli has been removed. For example, if you want to access a worksheet and that sheet doesn't exist the Errhandler could create that sheet and then resume execution where that sheet is being accessed. This concept alone makes it impossible to place a single On Error instruction at the top of the code, like, one size fits all. Instead, you place the instruction just before the line of code that might cause the error you want to handle and revoke the instruction with On Error Goto 0 immediately afterward. You can have more than one error handling routines in one procedure, each one marked by a different label.
During development you don't really want any automatic jumping about when an unplanned error raises its ugly head because VB provides much better information about any error than your Errhandler.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1600 total points
ID: 39688561
Try this modification
Option Explicit
Public EnableEditlistTrapping As Boolean

Private Sub btnEdit_Click()
On Error GoTo ErrHandler
Dim SourceData As Range
Dim Val1 As String
Dim Val2 As String
Dim Val3 As String
Dim Val4 As String
Dim findPnum As Object
Dim findPnum2 As Object
Dim ws As Worksheet
Dim I As String

EnableEditlistTrapping = True
Set ws = ThisWorkbook.Sheets("Data")
I = Me.txtPnum

'edit the viewing range
    Set findPnum = ws.Range("T:T").find(What:=I, LookAt:=xlWhole)
        If Not (findPnum Is Nothing) Then
        EnableEditlistTrapping = False
        With findPnum
            .Offset(0, 2).Value = Me.txtGood.Value
            .Offset(0, 3).Value = Me.txtRejects.Value
            .Offset(0, 4).Value = Me.txtShipt.Value
        End With
        End If

'edit the updating range
    Set findPnum2 = ws.Range("AA:AA").find(What:=I, LookAt:=xlWhole)
        If Not (findPnum2 Is Nothing) Then
        With findPnum2
            .Offset(0, 1).Value = Me.txtGood.Value
            .Offset(0, 2).Value = Me.txtRejects.Value
            .Offset(0, 3).Value = -Me.txtShipt.Value
        End With
        EnableEditlistTrapping = True
        End If
        
'refresh the Range that the ListBox is bound to
    Set SourceData = Range(EditList.RowSource)
'get the part number of the selected item
    Val1 = EditList.Value
'refresh the good figure
    Val2 = SourceData.Offset(EditList.ListIndex, 2).Resize(1, 1).Value
'refresh the rejects figure
    Val3 = SourceData.Offset(EditList.ListIndex, 3).Resize(1, 1).Value
'refresh the number shipped
    Val4 = SourceData.Offset(EditList.ListIndex, 4).Resize(1, 1).Value

'update the text box values
    Me.txtPnum = Val1
    Me.txtGood = Val2
    Me.txtRejects = Val3
    Me.txtShipt = Val4
    
    Me.txtGood.SetFocus
ExitErrHandler:
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitErrHandler
End Sub

Private Sub EditList_Change()
On Error GoTo ErrHandler
Dim SourceData As Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String
If Not EnableEditlistTrapping Then Exit Sub
'get Range that the ListBox is bound to
    Set SourceData = Range(EditList.RowSource)
'get the part number of the selected item
    Val1 = EditList.Value
'get the good figure
    Val2 = SourceData.Offset(EditList.ListIndex, 2).Resize(1, 1).Value
'get the rejects figure
    Val3 = SourceData.Offset(EditList.ListIndex, 3).Resize(1, 1).Value
'get the number shipped
    Val4 = SourceData.Offset(EditList.ListIndex, 4).Resize(1, 1).Value

'set the text box values from the selected item
    Me.txtPnum = Val1
    Me.txtGood = Val2
    Me.txtRejects = Val3
    Me.txtShipt = Val4
    
ExitErrHandler:
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitErrHandler
End Sub

Private Sub UserForm_Activate()
EnableEditlistTrapping = True
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 39688578
Perfect!
Thanks so much ssaqibh.
The "EnableEditlistTrapping" did the trick.
Much appreciated.

Thanks also to Faustulus for shepherding me along in the right direction of error handling.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question