Posts by kjpeissner

kjpeissner · Oct 19th 2016

Re: How to link checkbox on userform to cell in VBA

I keep copies of everything. I just looked at the spreadsheet. I was originally on the right track. That makes me feel a bit better. Last night I found free VBA lessons. I want to learn this, so I can improve my other worksheets and streamline my processes.

Thank you again!!!

kjpeissner · Oct 18th 2016

Re: Userform Getting Check Boxes to save

I so agree. Skywriter's code is wonderful!

I'm sorry. I can be very confusing. At the moment the Manage attachments is not working for me. Therefore, I've posted the Excel Sheet to my Google Drive. https://drive.google.com/file/…qaHRXMkU/view?usp=sharing

You will see the checkboxes on the "Userform" and on the Excel sheet the columns that coincide. On the Userform, I need to be able to tick off more than one box, untick boxes, and save to the Excel Sheet under the appropriate column using "X".

Thank you kindly, kelly

Quote from Ger Plante;779484

Kinda hard to follow what you are doing. The code that Skywriter produced in the other thread was very good, but I dont understand in what way you are trying to customize it. Can you share/add an example workbook showing what you need?

kjpeissner · Oct 18th 2016

Referencing http://www.ozgrid.com/forum/showthread.php?t=199865&page=2

I've been using the above Userform code for eons now; however I have found I need to add check boxes to include Listserv, Cert, AAS, NDS. I've spent the day on it, and I have searched the forum.
I know how to add checkboxes, but I can't get them to save or show up in the spreadsheet for example when ticking the checkbox it leaves an X on the spreadsheet. I need to be able to un-check them as well.

I was trying to get the code to reference the row, but it still did not work for me, so I deleted it. I managed to corrupt the copy I had.

I'd really appreciate it if you would help me.

Thank you,

Kelly

kjpeissner · Aug 2nd 2016

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

I am so sorry to hear about your girlfriend. I hope all is going well for you both. Take care. kj

Quote from skywriter;774870

You might get a better response if you create a new thread and put a link to this thread.

My girlfriend had surgery yesterday and I'm doing my best to take care of her right now, so my time is limited.

Good Luck :cool:

kjpeissner · Aug 2nd 2016

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

Hi,

I've been thinking the same thing or rather a new record button would be best for me. Skywriter, the form you created has been wonderful. Thank you! Kelly Jo
"Kearney_Paul
[INDENT]Dear Skywriter,

I know this post was covered a few months back and I think you are incredibly helpful! Is there a way to possibly add a "next record" and "previous record" to the UserForm you built at all? I've tried reading down through your code and made an attempt myself but it is way more advanced then I am capable of in vba at the moment unfortunately.

If you could help in any way I'd very much appreciate it.

Kind regards,

Paul, Ireland"[/INDENT]

kjpeissner · May 23rd 2016

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

I got it. I went into the project itself and right clicked on notes. Now it works. Thank you so much. Have a blessed day!

kjpeissner · May 23rd 2016

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

I got the control+enter to work by changing Multiline to true. Right click is still not working. Please, how do I fix it?

Thank you, kelly

kjpeissner · May 23rd 2016

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

Thank you for your response. Right click in the text box is not working, and neither is the control + enter

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.

kjpeissner · May 21st 2016

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

Thank you! :thanx:

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

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.

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!

kjpeissner · May 20th 2016

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

Thank you for your patience and understanding.

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

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 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

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.

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