Editing data through VBA userform

mmaeng13

New Member
Joined
Aug 29, 2017
Messages
25
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,758
You can trial this with Userform1 with controls as U outlined and with sheet1 data starting in row 5. HTH. Dave
Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,477
Office Version
  1. 2019
Platform
  1. Windows
Hi,
make a backup of your workbook and replace all your forms codes with following

VBA Code:
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
 
Solution

mmaeng13

New Member
Joined
Aug 29, 2017
Messages
25

ADVERTISEMENT

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.
 

mmaeng13

New Member
Joined
Aug 29, 2017
Messages
25

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,477
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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

New Member
Joined
Aug 29, 2017
Messages
25
Hi,
sorry my fault did not fully think it through

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

VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,477
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,104
Messages
5,622,746
Members
415,925
Latest member
Ryle23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top