Open userform upon entering data in cell

[Bandito1]

Hi all,

I got this excel sheet named "Data Gel" where i add data into.

in column A is gel code (for example 1050). in column B is batch number (for example 308102).
In column E and F i note a "x" for when i return the batch. E is production returns and F is other returns.

Now i would like that a useform shows when i type a "x" in column E or F.
When this userform opens i would like that in textbox1 it shows the corresponding gel code and in textbox2 the batchnumber of that batch.

Anyone knows how i do this?

 

[Yongle]

Try this:

at TOP of STANDARD module (above all subs)

Public GelCode As String, BatchNumber As String

in SHEET module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim c As Long: c = Target.Column
    If c = 5 Or c = 6 Then
        If LCase(Target) = "x" Then
            '[COLOR=#006400][I]get cell values[/I][/COLOR]
            GelCode = Cells(Target.Row, "A")
            BatchNumber = Cells(Target.Row, "B")
            
            UserForm1.Show
        End If
    End If
End Sub

in USERFORM module

Private Sub UserForm_Initialize()
[I][COLOR=#006400]'place values in textboxes[/COLOR][/I]
    TextBox1.Value = GelCode
    TextBox2.Value = BatchNumber

[I][COLOR=#006400]'clear variables[/COLOR][/I]
    GelCode = ""
    BatchNumber = ""
End Sub

 

[MickG]

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]If[/COLOR] Target.Column = 5 Or Target.Column = 6 [COLOR="Navy"]Then[/COLOR]
  [COLOR="Navy"]If[/COLOR] Target.Value = "x" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] UserForm1
     .TextBox1.Text = Cells(Target.Row, "A")
     .TextBox2.Text = Cells(Target.Row, "B")
     .Show
   [COLOR="Navy"]End[/COLOR] With
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Bandito1]

Thank you both for your replies, both work perfectly!

 

[MickG]

You're welcome