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.

Share