Solved

Update sheet from userform listbox

Posted on 2013-12-01
3
341 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 100 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 400 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now