Editing data through VBA userform

[mmaeng13]

I am working on a spread to track sales information. I have a userform that adds new data, but I want to make it easier to find and edit entries in the future as changes happen a lot. My data starts in A-G cell#5, with headers in row 4, with names Order Number; Date; Amount; GP%; Quote/Win/Loss; Customer; Comment. I have a userform with TextBox1-7 and three buttons, Update(cmdUpdate); Clear(cmdClr); Cancel(cmdExit). My current code is below. I would appreciate some help with getting it working right. I get errors but can't figure out what is wrong.

Option Explicit



Private Sub UserForm_Initialize()
    TextBox1.SetFocus
End Sub

Private Sub cmdClr_Click()
Dim ctl As Object
    For Each ctl In Me.Controls
       If TypeName(ctl) = "TextBox" Then ctl.Value = Null
    Next ctl
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        Findit
    End If
End Sub

Private Sub Findit()
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer

Set sh = Sheet1
Search = TextBox1.Text
Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)

    If fnd Is Nothing Then
        MsgBox "No Quote Found", , "Error"
        TextBox1.Text = ""
        frmUserForm2.Hide
    Else
        For i = 2 To 7
            frmUserForm2.Controls("UserFor2" & i).Text = sh.Cells(fnd.Row, i).Value
        Next i
    End If
End Sub

Private Sub cmdUpdate_Click()
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer
Dim ctl As Object

Set sh = Sheet2
Search = TextBox1.Text
Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)

    For i = 2 To 7
         sh.Cells(fnd.Row, i).Value = frmUserForm2.Controls("UserForm2" & i).Text
    Next i
    For Each ctl In Me.Controls
       If TypeName(ctl) = "TextBox" Then ctl.Value = Null
    Next ctl
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

 

[NdNoviceHlp]

You can trial this with Userform1 with controls as U outlined and with sheet1 data starting in row 5. HTH. Dave

Option Explicit
Private Sub UserForm_Initialize()
    UserForm1.TextBox1.SetFocus
End Sub

Private Sub cmdClr_Click()
Dim ctl As Object
For Each ctl In Me.Controls
   If TypeName(ctl) = "TextBox" Then ctl.Value = Null
Next ctl
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                                       ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
    Call Findit
End If
End Sub

Private Sub Findit()
Dim fnd As Range, Search As String
Dim sh As Worksheet, i As Integer
Set sh = ThisWorkbook.Sheets("sheet1")
Search = UserForm1.TextBox1.Text
Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)
If fnd Is Nothing Then
    MsgBox "No Quote Found", , "Error"
    UserForm1.TextBox1.Text = ""
    UserForm1.Hide
Else
For i = 2 To 7
UserForm1.Controls("textbox" & i).Text = sh.Cells(fnd.Row, i).Value
Next i
End If
End Sub

Private Sub cmdUpdate_Click()
Dim fnd As Range, Search As String, sh As Worksheet
Dim i As Integer, ctl As Object

Set sh = ThisWorkbook.Sheets("sheet1") 'Sheet2
Search = UserForm1.TextBox1.Text
Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)
For i = 2 To 7
sh.Cells(fnd.Row, i).Value = UserForm1.Controls("textbox" & i).Text
Next i
For Each ctl In Me.Controls
   If TypeName(ctl) = "TextBox" Then ctl.Value = Null
Next ctl
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

 

[dmt32]

Hi,
make a backup of your workbook and replace all your forms codes with following

Option Explicit

Dim fnd             As Range

Enum XLRecordActionType
    xlGetRecord
    xlUpdateRecord
    xlClearForm
End Enum

Private Sub UserForm_Initialize()
    Me.TextBox1.SetFocus
    Me.cmdUpdate.Enabled = False
End Sub

Private Sub cmdClr_Click()
    GetUpdateRecord xlClearForm
End Sub

Private Sub cmdUpdate_Click()
    GetUpdateRecord xlUpdateRecord
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then Findit
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = fnd Is Nothing
End Sub

Private Sub Findit()
   
    Dim i           As Integer
    Dim Search      As String
    Dim sh          As Worksheet
   
    Set sh = Sheet1
    Search = Me.TextBox1.Text
    If Len(Search) = 0 Then Exit Sub
   
    Set fnd = sh.Columns("A:A").Find(Search, , , xlWhole)
   
    If Not fnd Is Nothing Then
        GetUpdateRecord xlGetRecord
    Else
        MsgBox Search & Chr(10) & "No Quote Found", 48, "Not Found"
    End If
   
End Sub

Sub GetUpdateRecord(ByVal Action As XLRecordActionType)
    Dim i       As Integer
   
    For i = 1 To 7
        With Me.Controls("TextBox" & i)
        If Action = xlGetRecord And i > 1 Then .Text = fnd.Offset(, i - 1).Text
        If Action = xlUpdateRecord Then fnd.Offset(, i - 1).Value = .Text
        If Action <> xlGetRecord Then .Text = ""
        If i = 1 Then .SetFocus
        End With
    Next i
    Me.cmdUpdate.Enabled = CBool(Action = xlGetRecord)
    If Action <> xlGetRecord Then Set fnd = Nothing
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Note the variable declarations at the TOP of Codes. These MUST be place at very TOP of your forms code page OUTSIDE any procedure.

Not fully tested but hopefully will do what you want

Dave

 

[mmaeng13]

Hi,
make a backup of your workbook and replace all your forms codes with following



Dave

So far it works perfectly, I have to test a little more. Thank you!

 

[mmaeng13]

You can trial this with Userform1 with controls as U outlined and with sheet1 data starting in row 5. HTH. Dave

It didn't work but dmt32's worked. Thank you though.

 

[dmt32]

So far it works perfectly, I have to test a little more. Thank you!

welcome - glad we were able to help

Many thanks for feedback

Dave

 

[mmaeng13]

welcome - glad we were able to help

Many thanks for feedback

Dave

I have found only 1 glitch, my Exit button doesn't work. I have tried changing the command to UserForm2.Hide and that didn't work, as well as adding enable code to the form initialize section. It won't let me click the button at all.

 

[dmt32]

I have found only 1 glitch, my Exit button doesn't work. I have tried changing the command to UserForm2.Hide and that didn't work, as well as adding enable code to the form initialize section. It won't let me click the button at all.

Hi,
sorry my fault did not fully think it through

Don't have lot time at moment, for quickness, delete this procedure

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = fnd Is Nothing
End Sub

Hopefully, should resolve - I will see if can re-think it later

Dave

 

[mmaeng13]

Hi,
sorry my fault did not fully think it through

Don't have lot time at moment, for quickness, delete this procedure

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = fnd Is Nothing
End Sub

Hopefully, should resolve - I will see if can re-think it later

Dave

All good now. I really appreciate the help. I can normally manipulate code I find to do what I need but I couldn't figure this one out.

 

[dmt32]

All good now. I really appreciate the help. I can normally manipulate code I find to do what I need but I couldn't figure this one out.

It was just a line to keep the focus on textbox1 if record not found but I added it without testing. Issue can be resolved if needed but if happy with way it is working, just leave it.

Dave