How to Load Sheet Data into USERFORM
Watch This Video on youtube - https://youtu.be/SF3U7RP7a_g
Private Sub CommandButton1_Click()
Dim r As Long
On Error Resume Next
r = Application.WorksheetFunction.Match(Val(Me.TextBox1.Value), Sheets("Data").Range("A:A"), 0)
If Err > 0 Then
MsgBox "Data Not Found"
Me.TextBox2.Value = ""
Me.TextBox3.Value = 0
Me.TextBox4.Value = 0
Me.TextBox5.Value = 0
Me.TextBox6.Value = 0
Me.TextBox7.Value = 0
Me.TextBox8.Value = 0
Else
Me.TextBox2.Value = Sheets("Data").Cells(r, 2).Value
Me.TextBox3.Value = Sheets("Data").Cells(r, 3).Value
Me.TextBox4.Value = Sheets("Data").Cells(r, 4).Value
Me.TextBox5.Value = Sheets("Data").Cells(r, 5).Value
Me.TextBox6.Value = Sheets("Data").Cells(r, 6).Value
Me.TextBox7.Value = Sheets("Data").Cells(r, 7).Value
Me.TextBox8.Value = Sheets("Data").Cells(r, 8).Value
End If
End Sub
Private Sub CommandButton2_Click()
If Me.CommandButton2.Caption = "Edit" Then
Me.CommandButton2.Caption = "Change"
Me.TextBox2.Enabled = True
Me.TextBox3.Enabled = True
Me.TextBox4.Enabled = True
Me.TextBox5.Enabled = True
Me.TextBox6.Enabled = True
Me.TextBox7.Enabled = True
Me.TextBox8.Enabled = True
Else
Me.CommandButton2.Caption = "Edit"
Me.TextBox2.Enabled = False
Me.TextBox3.Enabled = False
Me.TextBox4.Enabled = False
Me.TextBox5.Enabled = False
Me.TextBox6.Enabled = False
Me.TextBox7.Enabled = False
Me.TextBox8.Enabled = False
End If
End Sub
Private Sub TextBox1_Change()
Dim r As Long
On Error Resume Next
r = Application.WorksheetFunction.Match(Val(Me.TextBox1.Value), Sheets("Data").Range("A:A"), 0)
If Err > 0 And Len(Me.TextBox1.Value) <> 0 Then
MsgBox "Data Not Found"
Me.TextBox2.Value = ""
Me.TextBox3.Value = 0
Me.TextBox4.Value = 0
Me.TextBox5.Value = 0
Me.TextBox6.Value = 0
Me.TextBox7.Value = 0
Me.TextBox8.Value = 0
Else
Me.TextBox2.Value = Sheets("Data").Cells(r, 2).Value
Me.TextBox3.Value = Sheets("Data").Cells(r, 3).Value
Me.TextBox4.Value = Sheets("Data").Cells(r, 4).Value
Me.TextBox5.Value = Sheets("Data").Cells(r, 5).Value
Me.TextBox6.Value = Sheets("Data").Cells(r, 6).Value
Me.TextBox7.Value = Sheets("Data").Cells(r, 7).Value
Me.TextBox8.Value = Sheets("Data").Cells(r, 8).Value
End If
End Sub
Comments
Post a Comment