+ Reply to Thread
Results 1 to 17 of 17

How to select and edit value in listbox using textbox???

  1. #1
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Question How to select and edit value in listbox using textbox???

    Hi, hoping someone will be able to help me out. I'm extremely novice at userform, so please bare with me.

    I have created codes where I can select on the value in listbox and this values appears on its corresponding textbox. However what I would now like to do is if I need to edit these values in the listbox/data, I would like to be able to click on this in the listbox and then make the necessary changes in the textbox and keeping this update on the same row.

    Any help or suggestion you can provide will be greatly appreciated!!
    Attached Files Attached Files
    Last edited by auswtz; 11-25-2015 at 12:11 AM.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: How to select and edit value in listbox using textbox???

    What is wrong with the proposed solution My Aswer Is This has provided from your cross-post here?
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: How to select and edit value in listbox using textbox???

    To make sure I understand, you want to click the Update button and have all the information in all the textboxes update accordingly in Sheet1?
    If you are happy with my response please click the * in the lower left of my post.

  4. #4
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: How to select and edit value in listbox using textbox???

    Hi, yes sorry I just saw your thread in the other site. I wasn't sure how I could show the codes I had so got on here as it might be easier to attach what I'm working on. I know now to use the #(thanks)

    The codes I currently have is from all the other sites I have read and slightly changed to make it work. So far Im able to select an issue in the list and have the textbox filled with its corresponding data.

    What I want to do now as you previously mentioned is if needed to update the data in Sheet 1 - I would essentially like to click the issue on the list and then be able to edit the issue using the textbox and then have it updated.

    So far the first step is solved - i can click on the issue on the list and it appears on the corresponding text box. I just need to be able to update this on its same row if required.???

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: How to select and edit value in listbox using textbox???

    Hi auswtz,

    Ideally you'd have a primary key for each record so that amending a record would be fairly straight forward.

    That said have a look at the following which returns the applicable row number for the record selected (based on Issue and subject (from email) in Col. A so hopefully that's unique) into a text box called txtRowNumber so that editing is possible (you can hide that control if you wish).

    Regards,

    Robert
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: How to select and edit value in listbox using textbox???

    Hi Robert,

    This is perfect! thank you so much for all your help.

    I will have a look at your codes and see if I can decipher it Thanks again for your patience and your help.

    Cheers

  7. #7
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: How to select and edit value in listbox using textbox???

    You're welcome

    If you can mark the thread as solved it would be appreciated

    Regards,

    Robert

  8. #8
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: How to select and edit value in listbox using textbox???

    Just a couple more questions?

    Are you able to run me through the following codes? Currently your wonderful codes allow me to update column A. How can I also do the same for all the other columns to allow the user to edit the other columns if required?

    Private Sub ListofData_Click()

    Dim rngMyData As Range

    Set rngMyData = Sheets("Sheet1").Columns("A")


    On Error Resume Next
    txtRowNumber = Application.WorksheetFunction.Match(txtIssue, rngMyData, 0)
    On Error Resume Next

    If Val(txtRowNumber) > 1 Then 'Exclude the ability to change the header row.
    cmdUpdate.Enabled = True 'OK to enable the button as an applicable row number has been returned
    End If

  9. #9
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: How to select and edit value in listbox using textbox???

    Currently your code allow me to update column A.
    No, it finds the row number to be modified via the issue selected on the form compared to the same in column A. If any field on the form is then modified it will be changed upon clicking the Update button on your form so it does allow "the user to edit the other columns if required".

    HTH

    Robert

    Please wrap your code with the appropriate tags. Thanks.

  10. #10
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: How to select and edit value in listbox using textbox???

    Hi Robert,
    Really sorry about this - but the code we have only allows to update column a (issue). I tried updating the other fields and this wouldn't update???
    I haven't made any changes to the codes you have written for me however it is only updating the Issue (column A) once I press the update button but not the other columns??

    Private Sub ListofData_Click()

    Dim rngMyData As Range

    Set rngMyData = Sheets("Sheet1").Columns("A")

    On Error Resume Next
    txtRowNumber = Application.WorksheetFunction.Match(txtIssue.Value, rngMyData, 0)
    On Error Resume Next

    If Val(txtRowNumber) > 1 Then 'Exclude the ability to change the header row.
    cmdUpdate.Enabled = True 'OK to enable the button as an applicable row number has been returned
    End If

    End Sub
    Private Sub cmdUpdate_Click()

    Dim lngMyRow As Long

    lngMyRow = Val(txtRowNumber)

    If lngMyRow = 0 Then
    MsgBox "Update is not available as a row number for the selected issue could not be found.", vbExclamation
    Exit Sub
    Else
    Cells(lngMyRow, "A").Value = txtIssue.Text
    Cells(lngMyRow, "C").Value = txtDateReceived.Text
    Cells(lngMyRow, "D").Value = txtAgency.Text
    Cells(lngMyRow, "E").Value = txtService.Text
    Cells(lngMyRow, "F").Value = txtSource.Text
    Cells(lngMyRow, "G").Value = txtIssueType.Text
    Cells(lngMyRow, "H").Value = txtIssueNonIssue.Text
    Cells(lngMyRow, "I").Value = txtOwnership.Text
    Cells(lngMyRow, "J").Value = txtTimeSpent.Text
    Cells(lngMyRow, "K").Value = txtDateCompleted.Text
    Cells(lngMyRow, "L").Value = txtActiveDuration.Text
    End If

    'Refresh the list
    Me.ListOfData.RowSource = "ListOfData"

    End Sub

  11. #11
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: How to select and edit value in listbox using textbox???

    There is a clash with the Private Sub ListOfData_Change() macro as when the record is attempted to be changed it fires this off again bringing back in the initial records.

    Why do have this procedure?

    You've also used the wrong tags to wrap your code

    Robert

  12. #12
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: How to select and edit value in listbox using textbox???

    Actually there are a number of design issues which I have addressed on attached. Post back re how it goes.

    Regards,

    Robert
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: How to select and edit value in listbox using textbox???

    Hi again, I saw your previous post with regards to Private Sub ListOfData_Change - this was initally written to so that when the user click on the listbox value it would appear on its corresponding text field.

    I deleted this since it was included in the codes you have written and it seems to now work with entering new data and editing existing data.

    I'll look at the codes you've provided on version 5. Thanks so much again for you help with this, definitely wouldn't be able to work out those codes without your help.

  14. #14
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: How to select and edit value in listbox using textbox???

    Its working perfect now!! thanks so much for your help Robert really appreciate it!!

  15. #15
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: How to select and edit value in listbox using textbox???

    You're welcome. Thanks for marking the thread as solved.

    Robert

  16. #16
    Registered User
    Join Date
    05-15-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    1

    Re: How to select and edit value in listbox using textbox???

    Hi, sorry to re-open this case. I think this is my first time posting a question

    I'm currently having an issue on this following:
    Set rngMyData = Worksheets("Contact_List").Columns("C") 'My actual data starts at column C and row 6 (exclude table header)

    On Error Resume Next
    txtRowNumber = Application.WorksheetFunction.Match(IDBox.Value, rngMyData, 0)
    On Error Resume Next

    If Val(txtRowNumber) > 1 Then 'Exclude the ability to change the header row.
    UpdateBtn.Enabled = True
    DeleteBtn.Enabled = True
    End If

    Currently, the update and delete button doesn't appear when an item in listbox is selected so I suspect the issue is on rngMyData. How do I resolve?

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to select and edit value in listbox using textbox???

    hi, welcome to the forum

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] if anything in listbox select first item, if listbox empty do nothing (listbox in userform
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2015, 12:49 PM
  2. [SOLVED] Single Select Multi Columns Listbox to Populate value in Textbox
    By puuts in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2014, 01:00 PM
  3. Select & edit items in a listbox in VBA
    By poppy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-04-2014, 04:47 PM
  4. [SOLVED] excel vba filter values to listbox from combobox and save edit values in textbox
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2013, 08:42 AM
  5. Select listbox item then appear textbox
    By sohaila in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2013, 04:41 AM
  6. select multiple column listbox to edit userform
    By tbar05 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2012, 12:13 AM
  7. UserForms, Force user to select from each Listbox, textbox
    By LauraW in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-29-2012, 04:17 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1