[SOLVED] Edit worksheet data thru userform - Page 2

LoveCandle · #16 07-04-2009, 04:31 PM

Quote:

Therefore, I think that there is a relationship between The example find code and amendment code .. but I don't know what is it exactly??!!!

Hi,

As I expected, There is a relationship between The searching Code and Amendment Code, which complete each other.

Searching Code's job is looking for the concerned record (selected by ComboBox) and stops on it by (Using Select property).

Then, Amendment code pastes the new changes to the recoded selected by Searching code.

Anyhow, I summarized the code to fit my need.

Code:

Private Sub ComboBox1_Change()
    
On Error GoTo 1

With MyRange.Find(ComboBox1)
   .Select
    TextBox1 = .Offset(0, 1)
End With

1 End Sub

Private Sub CommandButton1_Click()
  
With ActiveCell
    .Value = ComboBox1
    .Offset(0, 1) = TextBox1
End With
Unload Me

End Sub

Krishnakumar · #17 07-05-2009, 01:44 AM

Hi,

Replace all your code with the following

Code:

Public MyRange As Range
Public ws As Worksheet
Public Idx As Long
Private Sub ComboBox1_Change()
Idx = Me.ComboBox1.ListIndex + 1
If Idx = 0 Then
    Me.TextBox1.Value = ""
Else
    Me.TextBox1.Value = Application.Index(MyRange.Value, Idx, 2)
End If
End Sub

Private Sub CommandButton1_Click()
With MyRange.Cells(1, 1)
    If Idx = 0 Then
        Idx = Me.ComboBox1.ListCount
        .Offset(Idx).Value = Me.ComboBox1
        .Offset(Idx, 1).Value = Me.TextBox1
        Set MyRange = ws.Range("A2:B" & ws.Range("a" & Rows.Count).End(xlUp).Row)
        Me.ComboBox1.List = Application.Index(MyRange.Value, 0, 1)
    Else
        .Offset(Idx - 1).Value = Me.ComboBox1
        .Offset(Idx - 1, 1).Value = Me.TextBox1
    End If
End With
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Set ws = Sheets("Sheet1")
Set MyRange = ws.Range("A2:B" & ws.Range("a" & Rows.Count).End(xlUp).Row)
Me.ComboBox1.List = Application.Index(MyRange.Value, 0, 1)
End Sub

LoveCandle · #18 07-05-2009, 02:23 AM

Hi,

Thank you Krishnakumar for your reply,

Your codes are excellent, but I am so sorry to tell you that the problem is still existing

What is new in your codes is the following,

If ComboBox1 value was changed, the new values are not recorded in the first row of the list like previous examples, but they are added to the worksheet list as a new record.

Anyhow, I think the best and the most satisfactory solution for this case is creating a new Textbox and use the first one for employee name and the other one for badge number and ComboBox1 value will stay as it is without changing.

Thank you all,