Hi All, i will try to explain my issue as objectively i can and show where i have reached till now.
- I have certain database in sheet1 with column as name, place, city, code and Hobby ( Hobby column is my problem focus read below)
- In sheet 2 cell (1,1) i have dropdown list of names coming from Sheet 1 "name column" and related output coming from sheet1 ( based on dropdown i choose in cell(1,1))
- Problem : - I want to create a user form which when activated automatically picks name filed from sheet2 cell (1,1) and one filed (Hobby) and give me option to change it when click save.
- Enactment.- User choose a name in sheet 2 in cell 1,1 - list of details come in sheet. Now he click update (which fires userform) - this userform already have the name he has choosen and show hobby field. User update hobby filed and click save. Whithout going to sheet 1 the hobby filed is updated. Now he pick another name in sheet2 cell 1,1 and does the same.
- Basic problem i am looking is how to link userform filed to one particular cell in another sheet. Below is the simple code i have written, but it need the name filed to be filled manually ....
Code
Private Sub CommandButton1_Click() 'this is to show field button based on name
Dim Name As String
Name = Trim(TextBox1.Text) ' something has to change here right now i am putting it manually ...
lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Sheet1").Cells(i, 1).Value = Name Then
TextBox2.Text = Worksheets("Sheet1").Cells(i, 6).Value
End If
Next
End Sub
Private Sub CommandButton2_Click() 'this is to update
Dim Name As String
Name = Trim(TextBox1.Text)
lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Sheet1").Cells(i, 1).Value = Name Then
Worksheets("Sheet1").Cells(i, 6).Value = TextBox2.Text
End If
Next
End Sub
Private Sub CommandButton3_Click()
Unload UserForm1
End Sub