don't get to do a second edit

[ndendrinos]

Sub Match_Row54()
    Dim cell As Range, Found As Range, Firstfound As String
    Sheets("Invoice").Select
    For Each cell In Range("D17:D35")
        If Not IsEmpty(cell) Then
        Set Found = Rows(54).Find(cell.Value, Cells(54, Columns.Count), xlValues, xlWhole, xlByColumns, xlNext, False)
            
            If Not Found Is Nothing Then
                Firstfound = Found.Address
                Do
                    If Found.Offset(3) < 0 Then
                        Found.Offset(1).Select
                        UserForm1.Show
              End If
                    Set Found = Rows(54).FindNext(After:=Found)
                    If Found Is Nothing Then Exit Do
                Loop Until Found.Address = Firstfound Or Found Is Nothing
            End If
        End If
            
    Next cell
    

End Sub

I use above to edit [B17:B35] thanks to a userform's textbox.
When I get the userform up I edit the first row and the userform disappears instead of staying on in case there is more than one row that needs editing. Is there something missing in the code? I really can't tell

Thank you

 

[Warship]

Difficult to analyze without your UserForm code.
The code you submitted seems to do as expected

 

[ndendrinos]

Thanks warship this is a difficult one.
here is the userform code (all of them)

Private Sub CommandButton1_Click()

Dim myC As Range
Set myC = Worksheets("Invoice").Range("D17:D35").Find(UserForm1.TextBox2.Text)
If myC Is Nothing Then
MsgBox UserForm1.TextBox2.Text & " was not found."
Else
myC(1, -1).Value = UserForm1.TextBox1.Value  'Pick up the cell to the left

End If
Unload UserForm1
Call deductfromstock
End Sub


Private Sub CommandButton2_Click()

End Sub

Private Sub TextBox1_Change()
If TextBox1.Value >= 0 Then _
    TextBox3.Text = "  Cannot fill entire quantity would you like to back order the difference ? "
If TextBox1.Value <= 0 Then _
 TextBox3.Text = "          There is no stock left for this product. Click the Cancel button "
 
End Sub

Private Sub TextBox2_Change()


End Sub

Private Sub UserForm_Initialize()

     Me.TextBox1.Text = CStr(ThisWorkbook.Sheets("Invoice").Range(ActiveCell.Address).Value)
     Me.TextBox2.Text = ActiveCell.Offset(-1, 0).Value
     With TextBox1
   .SelStart = 0
   .SelLength = Len(.Text)
   .Copy
 End With
 Me.TextBox1.Enabled = False
 Me.TextBox2.Enabled = False
 
 
End Sub

Thru the userform I get to see if there is enough stock for any of the requested "products" in column "D" and I can edit the adjacent cell in column "B"
Trouble is that I can only do this once and need to do it several times for as many "products" that I cannot fill

Difficult to explain

 

[ndendrinos]

as well this is where all the details entered end up before they get deducted from "Stock"

Sub deductfrominvrow56()
  Application.ScreenUpdating = False
    Dim TgtRw As Long
    Dim Dt
    Dim Inv
    Dim Inventorylist As Range
    Dim c As Long
     
    With Sheets("Invoice")
        Dt = .Range("H4").Value
        Inv = .Range("H2").Value
    End With
  
    'Sheets("Stock").Activate
    With Sheets("Invoice")
        TgtRw = 56
        Set Inventorylist = .Range("invrow54")
    End With
    
    Cells(TgtRw, 1) = Dt
    Cells(TgtRw, 2) = Inv
    
    For Each Cel In Range("Products")
        c = Application.Match(Cel, Inventorylist, 0)
        Cells(TgtRw, c) = -Cel.Offset(, -2)
    Next
   
   Call Match_Row54
    
 Sheets("Invoice").Activate
 Rows("56:56").Select
    Selection.ClearContents
 Application.ScreenUpdating = True
 
 End Sub

I just post this hoping that something obvious is there that I cannot see

 

[Warship]

Your code seems to do what it should as written.

The form updates and reappears with each successive CommandButton1 click
when Row57<0.

I'm not sure the reason for the form as both text boxes remain
disabled so no user input is possible so I'm not sure what you mean by edit.

Maybe the prob in deductfromstock? - Perhaps call this before the form unload
or in your Match_Row54 sub after UserForm1.Show?

 

[ndendrinos]

Thanks again Warship.
I've uploaded the file here:
http://www.box.net/shared/6fs7oloevl
maybe you can have a look when you have the time

 

[Norie]

Nick

Why are you using a userfom for this?

Couldn't you just set up some sort of validation that prevents over-ordering happening in the first place?

If you do want to use a userform why not display all the items from the order in a listbox, or some other control.

Or even just the 'problem' items.

As far as I can work out you are going to display a userform for each 'problem' item.

 

[ndendrinos]

Hello Norie thanks for stopping by.
I'm using the userform to get instantly the "problem" item and more importantly the stock left for that item.
I can then choose to edit the qty shipped and back order the rest (further code creates a "back order" invoice that sits as an new worksheet awaiting for stock to come in.
OR cancel the item all together.

To prevent the over-ordering was one of the ideas I used but abandoned because I would get a msgbox that would list all the "problem" items but without the option of editing the shipped qty directly.

I also tried a lisbox (maybe you remember that one ... it was split in 2 listboxes listing the AtoM and NtoZ products)

That last one was giving me the same problem as the present one thus my new attempt with a userform (that should only offer to decrease just the problem items)

Always a pleasure to hear from you

 

[ndendrinos]

Not sure if anyone has downloaded the sample file on Box.net but Norie just gave me and idea.
Part of the many codes I have in this WB is one that checks for duplicates and offers the choice of increasing the previous qty or delete the duplicate.
Maybe I can develop something along those lines meaning the code would check one by one the items on the invoice as they are entered and only fire up if the qty in stock is lesser than the one ordered

Here is the code for the dups:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("D17:D35")) Is Nothing Then
    If Target.Value = "" Then UserForm1.Show
    Range("A35").End(xlUp)(2, 1).Activate
    
    'looks for duplicate and alerts
    Application.EnableEvents = False
    Dim rngFindRange  As Range
    If Range("A" & Target.Row) <> "" And Range("D" & Target.Row) <> "" Then
    Set rngFindRange = Range("D1:D" & Target.Row - 1).Find(What:=Range("D" & Target.Row), LookIn:=xlValues)
        
        If Not rngFindRange Is Nothing Then
        
          If MsgBox("Product exists on a previous row ... Do you wish to add to it ?", vbYesNo) = vbNo Then
          Range("A" & Target.Row & ":F" & Target.Row).ClearContents
          ActiveCell.Offset(-1, 0).Select
     
                Else
                'this takes you to the qty of the duplicate
                ActiveCell.Offset(-1, 0).Select
                Selection.Copy
                '
                 rngFindRange.Offset(0, -3).Select
                 
                 Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False
        
        Range("A17").End(xlDown).Select
                  
  Union(ActiveCell, ActiveCell.Offset(0, 3).Resize(, 3)).ClearContents
                                 
                 End If
                End If
          End If
   End If
  
   Application.EnableEvents = True
End Sub

 

[Norie]

Nick

I downloaded the file and to be honest I couldn't quite follow what was going on.

I'm sure I've asked before, but what - in words - are you trying to do?

Are you trying to use Excel as some sort of database?