0

userform1. code works perfect when sheet "All p.o. info" is active, but issues when sheet is not active. textbox value not showing or showing wrong information when sheet with range containing the value is inactive. Any help will be greatly appreciated. 

`Private Sub CommandButton4_Click()
Dim id As String
Dim PO As String
Dim finalrow As Integer
Dim i As Integer

id = TextBox16.Value
finalrow = Sheets("ALL P.O. INFO").Range("D6000").End(xlUp).ROW

For i = 2 To finalrow
    If Sheets("ALL P.O. INFO").Cells(i, 4) = id Then
        Me.TextBox23 = Sheets("ALL P.O. INFO").Cells(i, 7).Value
        Me.TextBox19 = Sheets("ALL P.O. INFO").Cells(i, 4).Value
        Me.TextBox20 = Sheets("ALL P.O. INFO").Cells(i, 1).Value
        Me.TextBox21 = Sheets("ALL P.O. INFO").Cells(i, 6).Value
        Me.TextBox22 = Sheets("ALL P.O. INFO").Cells(i, 14).Value

    End If

PO = TextBox18.Value
finalrow = Sheets("ALL P.O. INFO").Range("A6000").End(xlUp).ROW
        If Cells(i, 1) = PO Then
            Me.TextBox23 = Sheets("ALL P.O. INFO").Cells(i, 7).Value
            Me.TextBox19 = Sheets("ALL P.O. INFO").Cells(i, 4).Value
            Me.TextBox20 = Sheets("ALL P.O. INFO").Cells(i, 1).Value
            Me.TextBox21 = Sheets("ALL P.O. INFO").Cells(i, 6).Value
            Me.TextBox22 = Sheets("ALL P.O. INFO").Cells(i, 14).Value
        End If

Next i`
Byron
  • 61
  • 1
  • 11
  • 1
    In your second IF-STATEMENT, `Cells(i,1)` isn't fully qualified; which means that the code is using an implicit `ActiveSheet`. – Mistella Apr 17 '19 at 14:45
  • 1
    See here - https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 – Tim Williams Apr 17 '19 at 14:47
  • @TimWilliams wow, how the heck was that closed as "opinion-based"?! voted to reopen... – Mathieu Guindon Apr 17 '19 at 14:51
  • @MathieuGuindon - I had no idea it was closed. The reason for closing seems odd though - there's only one way to answer (and thanks for the edit) – Tim Williams Apr 17 '19 at 14:58
  • @TimWilliams indeed... and that's a great answer you got there! Would [this](https://i.stack.imgur.com/EFtjA.png) be too much of an edit though? I don't want to abuse my edit privs or deface your post in any way - not going to hit that button if you would prefer to keep it as it is. – Mathieu Guindon Apr 17 '19 at 15:08
  • ok thanks that was it, simple error, simple fix. – Byron Apr 17 '19 at 15:20
  • @MathieuGuindon - that edit is OK with me – Tim Williams Apr 17 '19 at 16:41

0 Answers0