Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

kjpeissner · May 19th 2016

Hello,

I have spent almost a week trying to get my userform to work. My eyes are really bad, and a user form will help immensely.

The buttons enter, search, and exit work for me; however,Edit and Delete do not. I'd like to change my exit button to exit and save. The sheet's headers are ID, First Name, Last Name, Phone 1, Phone 2, Email, Address, City, State, Zip, and Notes.

Please can anyone assist me? Here is the code which I found here and there from other spreadsheet examples:

Command Button 1 is Enter, Command Button 2 is Search, Command Button 3 is Edit, Command Button 4 is delete, Command Button 5 is Exit

Code

Private Sub CommandButton1_Click()
Dim LastRow As Object




          Set LastRow = Sheet1.Range("a65536").End(xlUp)




          LastRow.Offset(1, 0).Value = TextBox1.Text
          LastRow.Offset(1, 1).Value = TextBox2.Text
          LastRow.Offset(1, 2).Value = TextBox3.Text
          LastRow.Offset(1, 3).Value = TextBox4.Text
          LastRow.Offset(1, 4).Value = TextBox5.Text
          LastRow.Offset(1, 5).Value = TextBox6.Text
          LastRow.Offset(1, 6).Value = TextBox7.Text
          LastRow.Offset(1, 7).Value = TextBox8.Text
          LastRow.Offset(1, 8).Value = TextBox9.Text
          LastRow.Offset(1, 9).Value = TextBox10.Text
          LastRow.Offset(1, 10).Value = TextBox11.Text




          MsgBox "One record written to Sheet1"




          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)




          If response = vbYes Then
              TextBox1.Text = ""
              TextBox2.Text = ""
              TextBox3.Text = ""
              TextBox4.Text = ""
              TextBox5.Text = ""
              TextBox6.Text = ""
              TextBox7.Text = ""
              TextBox8.Text = ""
              TextBox9.Text = ""
              TextBox10.Text = ""
              TextBox11.Text = ""
              




              TextBox1.SetFocus




          Else
              Unload Me
          End If




      End Sub
                








Private Sub CommandButton2_Click()
    
    Do
        DoEvents
        row_number = row_number + 1
        item_in_review = Sheet1.Range("A" & row_number)
        If item_in_review = TextBox1.Text Then
            TextBox2.Text = Sheet1.Range("B" & row_number)
            TextBox3.Text = Sheet1.Range("C" & row_number)
            TextBox4.Text = Sheet1.Range("D" & row_number)
            TextBox5.Text = Sheet1.Range("E" & row_number)
            TextBox6.Text = Sheet1.Range("F" & row_number)
            TextBox7.Text = Sheet1.Range("G" & row_number)
            TextBox8.Text = Sheet1.Range("H" & row_number)
            TextBox9.Text = Sheet1.Range("I" & row_number)
            TextBox10.Text = Sheet1.Range("J" & row_number)
            TextBox11.Text = Sheet1.Range("K" & row_number)
            
        End If
    Loop Until item_in_review = ""
        
End Sub












Private Sub CommandButton3_Click()
'After searching data and fill the textbox in userform
'i will edit the data for Sheet1
'Search  ID  and other info will fill the textbox
'and  same with other when i trying to search something and edit spicific
'and edit some text When click this the add , delete button willbe disable
'and when i click edit button the data when i edit will be save in database sheet1 it will replace the data




      End Sub








Private Sub CommandButton4_Click()
'this button after searching the data in searching in textbox when i click delete
'all data in sheet1 and the userform textbox will be deleted
End Sub




Private Sub CommandButton5_Click()
  
          End
      End Sub
















Private Sub UserForm_Click()




End Sub

Display More

skywriter · May 19th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

I can help you but you have to upload the workbook.
Dummy data is fine if you have sensitive info, but leave the sheets, sheet names, headers etc. intact.

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions to upload a workbook.

kjpeissner · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Hi Skywriter, I think I did it. You are so kind. I can't thank you enough. This will make data entry so much easier. I'll actually be able to see it without having to enlarge my spreadsheet.

skywriter · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

There's no data and there are two sheets.

So explain to me in detail which buttons you want code for and exactly how you want to use these buttons, meaning what you will type etc., your comments are a little hard to follow.

Please don't be generic, be specific, not I want to search the sheets.
My next question would be search the sheets for what and your reply might be what is in the textbox and my next question would be which text box etc.

Why do I ask these things because you are asking me to help you do certain things and I don't know exactly what you expect, I may make it search a certain way and you may say well that's not what I wanted, so please spell it out.

Explain why there are two sheets and how that works into what you want.
Also naming text boxes 1, 2 etc. is a terrible idea, it makes it very difficult, you have to remember what box was which, not a good idea.

I'm renaming them to what the label says.

kjpeissner · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

I removed the actual data and left the headers in those two sheets. I'll send you the revised worksheet tomorrow with some data added. Was there any VBA? I figured I could move the sheet tabs and make HUMS the first sheet when I needed to use the user form and vice versa. I need data to populate under the headers in the sheet with the user form and be able to search by ID, and hopefully be able to delete data with the userform, edit data with the user form and exit and save using the data form. Does any of that make sense?

I have a macro in which you can run too which should pull up my Userform. I'm just now heading home from work. I've put in almost two hours of extra tonight at the office here and I'm going cross eyed. Thank you for absolutely everything.

kjpeissner · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Of course you saw the VBA. Gees my brain is dead. I'm sorry I'm such an air head Bruce. I'll get to it first thing in the morning. It's almost 8:00 pm here adn I have to get ready for tomorrow. I'm so sorry to hold you up. Sincerely, kelly

Quote from kjpeissner;771074

I removed the actual data and left the headers in those two sheets. I'll send you the revised worksheet tomorrow with some data added. Was there any VBA? I figured I could move the sheet tabs and make HUMS the first sheet when I needed to use the user form and vice versa. I need data to populate under the headers in the sheet with the user form and be able to search by ID, and hopefully be able to delete data with the userform, edit data with the user form and exit and save using the data form. Does any of that make sense?

I have a macro in which you can run too which should pull up my Userform. I'm just now heading home from work. I've put in almost two hours of extra tonight at the office here and I'm going cross eyed. Thank you for absolutely everything.

kjpeissner · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

I got interrupted when I was initially going to upload the revised Excel spreadsheett, so I've tried again. I think it is uploaded now. I've uploaded the revised spreadsheet. I added information the ECE page of what I've been trying to do. Please let me know if I need to be clearer. Also, I intend to use the userform for both sheets. When I need to work on HUMS with the Userform, I'll pull the tab over and make it sheet one, and then do the same with ECE when I need to work on it.

Thank you kindly, kelly

skywriter · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

I haven't been on the board since my last post and I haven't read your responses, but I'll have a look shortly.

skywriter · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Quote

Of course you saw the VBA. Gees my brain is dead. I'm sorry I'm such an air head Bruce. I'll get to it first thing in the morning. It's almost 8:00 pm here adn I have to get ready for tomorrow. I'm so sorry to hold you up. Sincerely, kelly

Don't worry, I'm here to help you.

kjpeissner · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Thank you for your patience and understanding.

skywriter · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Right now the code I'm writing is only written for sheet1("ECE 201603").

What would you think about a combo box that had a list of all the sheet names or selected sheet names that you could pick from to run the code against.

Regarding an edit button I'm thinking you just use search instead and the code for the enter button will always check if the ID exists and if it does it will just ask you if you want to replace the existing record.

If you are replacing you say yes, if by chance you are trying to enter what you think is a new record but it already exists you get warned, giving you a chance to change the ID?

Delete will also warn you before doing the deed, giving you a chance to change your mind.

By exit/save do you mean close the form and save the workbook but leave the workbook open or close the form and save and close the workbook?

If it's the latter then we should also have a close form button in case you just want the form to go away and keep working.

kjpeissner · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

What you are proposing is awesome! Thank you! And close the form and save the workbook but leave the workbook open is what I's like to be able to do. Wow!

skywriter · May 20th 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

If you happen to see this message.

Regarding your ID numbers, are they pure numbers 123 etc. and if so do you want then to get entered into the cell as a number because I will need to change some code to get that for you?

The output of the text boxes are text and I need to change that.

Probably the same question for zip code.

The changes are very minor so don't worry about adding to my workload it's no big deal.

If they are a mixture of numbers and letters or just text then it won't matter.

kjpeissner · May 21st 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Yes the ID numbers and zip are numbers only.

I was concerned about your workload. Even so you are taking personal time to do this and that is valuable too. I'll make sure to pay it forward to someone that needs assistance or help in something in the future. Again, thank you.

skywriter · May 21st 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Here's all the code for anyone that doesn't want to open the file.

Code

Option Explicit
Private ws As Worksheet
Private rngFound As Range


Private Sub ChooseWorksheet_Change()
    Set ws = Worksheets(ChooseWorksheet.Value)
End Sub


Private Sub ChooseWorksheet_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If ChooseWorksheet.ListIndex = -1 Then
        MsgBox "You will need to choose a worksheet.", vbInformation
        Cancel = True
    End If
End Sub


Private Sub ClearFormButton_Click()
    ClearForm
End Sub


Private Sub DeleteButton_Click()
Dim response As Long


If Me.ID = "" Then
    MsgBox "You have not selected an ID.", vbInformation
    Me.ID.SetFocus
    Exit Sub
End If
FindRecord (Val(Me.ID))
    If Not rngFound Is Nothing Then
        PopulateForm
        response = MsgBox("Are you sure you want to delete ID# " & Me.ID & " on " & ws.Name & "!", vbYesNo + vbCritical)
        If response = vbYes Then rngFound.EntireRow.Delete
    Else
        MsgBox "There is no record with ID# " & Me.ID & " on " & ws.Name & "!", vbInformation
        Me.ID.SetFocus
    End If
    ClearForm
End Sub


Private Sub EnterButton_Click()
Dim lr As Long
Dim replace As Long
Dim response As Long


If Me.ID.Value = "" Then
    MsgBox "You have not entered an ID."
    Me.ID.SetFocus
    Exit Sub
End If


FindRecord (Val(Me.ID))
    If Not rngFound Is Nothing Then
       replace = MsgBox("This record exists on worksheet" & ws.Name & " row #" & rngFound.Row & "." & vbNewLine _
       & "Replace?", vbYesNo)
       If replace = vbYes Then
            lr = rngFound.Row
       Else
            ClearForm
            Me.ID.SetFocus
            Exit Sub
        End If
    Else
        lr = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
            
    With ws
           .Cells(lr, 1).Value = Val(Me.ID)
          .Cells(lr, 2).Value = Me.FirstName
          .Cells(lr, 3).Value = Me.LastName
          .Cells(lr, 4).Value = Me.Phone1
          .Cells(lr, 5).Value = Me.Phone2
          .Cells(lr, 6).Value = Me.Email
          .Cells(lr, 7).Value = Me.Address
          .Cells(lr, 8).Value = Me.City
          .Cells(lr, 9).Value = Me.State
          .Cells(lr, 10).Value = Val(Me.Zip)
          .Cells(lr, 11).Value = Me.Notes1
    End With
    
            If replace = vbYes Then
                MsgBox "The existing record on " & ws.Name & " row# " & rngFound.Row & " was overwitten"
            Else
                MsgBox "The record was written to " & ws.Name & " row# " & lr
            End If
            
          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)


          If response = vbYes Then
              ClearForm
              Me.ID.SetFocus
          Else
              Unload Me
          End If
End Sub


Private Sub ExitButton_Click()
    Unload Me
    ThisWorkbook.Save
End Sub


Private Sub SearchButton_Click()


If Me.ID.Value = "" Then MsgBox "You have not entered an ID.": Exit Sub


FindRecord (Val(Me.ID))
        If Not rngFound Is Nothing Then
            PopulateForm
        Else
            MsgBox "There are not matches for " & Me.ID & "!", vbInformation
        End If
End Sub


Sub ClearForm()
    Me.ID.Value = ""
    Me.FirstName.Value = ""
    Me.LastName.Value = ""
    Me.Phone1.Value = ""
    Me.Phone2.Value = ""
    Me.Email.Value = ""
    Me.Address.Value = ""
    Me.City.Value = ""
    Me.State.Value = ""
    Me.Zip.Value = ""
    Me.Notes1.Value = ""
    Me.ID.SetFocus
End Sub


Private Sub UserForm_Initialize()
Dim w As Worksheet
    For Each w In Worksheets
        ChooseWorksheet.AddItem w.Name
    Next w
    ChooseWorksheet.SetFocus
End Sub
Function FindRecord(ID)
    With ws.Columns(1)
        Set rngFound = .Find(What:=ID, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _
                                SearchDirection:=xlNext)
    End With
End Function
Sub PopulateForm()
            Me.FirstName.Value = rngFound(1, 2).Value
            Me.LastName.Value = rngFound(1, 3).Value
            Me.Phone1.Value = rngFound(1, 4).Value
            Me.Phone2.Value = rngFound(1, 5).Value
            Me.Email.Value = rngFound(1, 6).Value
            Me.Address.Value = rngFound(1, 7).Value
            Me.City.Value = rngFound(1, 8).Value
            Me.State.Value = rngFound(1, 9).Value
            Me.Zip.Value = rngFound(1, 10).Value
            Me.Notes1.Value = rngFound(1, 11).Value
End Sub

Display More

Remember if you want to use this form in a workbook other than the one I uploaded, right click on the form name, choose to export the file and then import that exported file into another workbook.

Let me know if you have any bugs or you want to do something else with the form.

There isn't a whole lot of data validation code in this form, I'll leave that up to you.

Control + q is a shortcut to run the macro that initializes the form.

kjpeissner · May 21st 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

This is absolutely perfect! You are the best. I can't thank you enough!! I'm going to use it in my budget ledger too. You have no idea what a improvement you have made in my life. I'm going to send you a PM

Quote from skywriter;771156

Here's all the code for anyone that doesn't want to open the file.

Code

Option Explicit
Private ws As Worksheet
Private rngFound As Range


Private Sub ChooseWorksheet_Change()
    Set ws = Worksheets(ChooseWorksheet.Value)
End Sub


Private Sub ChooseWorksheet_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If ChooseWorksheet.ListIndex = -1 Then
        MsgBox "You will need to choose a worksheet.", vbInformation
        Cancel = True
    End If
End Sub


Private Sub ClearFormButton_Click()
    ClearForm
End Sub


Private Sub DeleteButton_Click()
Dim response As Long


If Me.ID = "" Then
    MsgBox "You have not selected an ID.", vbInformation
    Me.ID.SetFocus
    Exit Sub
End If
FindRecord (Val(Me.ID))
    If Not rngFound Is Nothing Then
        PopulateForm
        response = MsgBox("Are you sure you want to delete ID# " & Me.ID & " on " & ws.Name & "!", vbYesNo + vbCritical)
        If response = vbYes Then rngFound.EntireRow.Delete
    Else
        MsgBox "There is no record with ID# " & Me.ID & " on " & ws.Name & "!", vbInformation
        Me.ID.SetFocus
    End If
    ClearForm
End Sub


Private Sub EnterButton_Click()
Dim lr As Long
Dim replace As Long
Dim response As Long


If Me.ID.Value = "" Then
    MsgBox "You have not entered an ID."
    Me.ID.SetFocus
    Exit Sub
End If


FindRecord (Val(Me.ID))
    If Not rngFound Is Nothing Then
       replace = MsgBox("This record exists on worksheet" & ws.Name & " row #" & rngFound.Row & "." & vbNewLine _
       & "Replace?", vbYesNo)
       If replace = vbYes Then
            lr = rngFound.Row
       Else
            ClearForm
            Me.ID.SetFocus
            Exit Sub
        End If
    Else
        lr = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
            
    With ws
           .Cells(lr, 1).Value = Val(Me.ID)
          .Cells(lr, 2).Value = Me.FirstName
          .Cells(lr, 3).Value = Me.LastName
          .Cells(lr, 4).Value = Me.Phone1
          .Cells(lr, 5).Value = Me.Phone2
          .Cells(lr, 6).Value = Me.Email
          .Cells(lr, 7).Value = Me.Address
          .Cells(lr, 8).Value = Me.City
          .Cells(lr, 9).Value = Me.State
          .Cells(lr, 10).Value = Val(Me.Zip)
          .Cells(lr, 11).Value = Me.Notes1
    End With
    
            If replace = vbYes Then
                MsgBox "The existing record on " & ws.Name & " row# " & rngFound.Row & " was overwitten"
            Else
                MsgBox "The record was written to " & ws.Name & " row# " & lr
            End If
            
          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)


          If response = vbYes Then
              ClearForm
              Me.ID.SetFocus
          Else
              Unload Me
          End If
End Sub


Private Sub ExitButton_Click()
    Unload Me
    ThisWorkbook.Save
End Sub


Private Sub SearchButton_Click()


If Me.ID.Value = "" Then MsgBox "You have not entered an ID.": Exit Sub


FindRecord (Val(Me.ID))
        If Not rngFound Is Nothing Then
            PopulateForm
        Else
            MsgBox "There are not matches for " & Me.ID & "!", vbInformation
        End If
End Sub


Sub ClearForm()
    Me.ID.Value = ""
    Me.FirstName.Value = ""
    Me.LastName.Value = ""
    Me.Phone1.Value = ""
    Me.Phone2.Value = ""
    Me.Email.Value = ""
    Me.Address.Value = ""
    Me.City.Value = ""
    Me.State.Value = ""
    Me.Zip.Value = ""
    Me.Notes1.Value = ""
    Me.ID.SetFocus
End Sub


Private Sub UserForm_Initialize()
Dim w As Worksheet
    For Each w In Worksheets
        ChooseWorksheet.AddItem w.Name
    Next w
    ChooseWorksheet.SetFocus
End Sub
Function FindRecord(ID)
    With ws.Columns(1)
        Set rngFound = .Find(What:=ID, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _
                                SearchDirection:=xlNext)
    End With
End Function
Sub PopulateForm()
            Me.FirstName.Value = rngFound(1, 2).Value
            Me.LastName.Value = rngFound(1, 3).Value
            Me.Phone1.Value = rngFound(1, 4).Value
            Me.Phone2.Value = rngFound(1, 5).Value
            Me.Email.Value = rngFound(1, 6).Value
            Me.Address.Value = rngFound(1, 7).Value
            Me.City.Value = rngFound(1, 8).Value
            Me.State.Value = rngFound(1, 9).Value
            Me.Zip.Value = rngFound(1, 10).Value
            Me.Notes1.Value = rngFound(1, 11).Value
End Sub

Display More

Remember if you want to use this form in a workbook other than the one I uploaded, right click on the form name, choose to export the file and then import that exported file into another workbook.

Let me know if you have any bugs or you want to do something else with the form.

There isn't a whole lot of data validation code in this form, I'll leave that up to you.

Control + q is a shortcut to run the macro that initializes the form.

Display More

skywriter · May 21st 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

The OP has offered to make a donation, that offer is very much appreciated.

I don't have a charity with links, so if someone on this board wants to chime in with a suggestion please feel free.

To the OP it was completely my pleasure, if you have issues or want to add some more functionality, I'm here.

cytop · May 21st 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Consider the forum charity

kjpeissner · May 21st 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Thank you! :thanx:

kjpeissner · May 23rd 2016

Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Hi, I found when entering notes, the data does not wrap. Instead it goes on and on. If I hit enter to get the information to another line, it tries to exit. Please, is that anything you can fix at your convenience? Thank you.

Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

Participate now!

Share

Users Viewing This Thread