+ Reply to Thread
Results 1 to 2 of 2

Using Form Listbox to update Cells in Sheet

  1. #1
    Registered User
    Join Date
    10-09-2017
    Location
    Stavanger, Norway
    MS-Off Ver
    O365
    Posts
    78

    Using Form Listbox to update Cells in Sheet

    I am using a Form and Listbox to update a small range of cells (13 rows and 2 Columns) in a Sheet. Not a skilled VBA programmer I have used Google to find an appropriate code. And it seems to work, but is based on the cells being at the top of the sheet "A:A". I can't seem to change the code to refer to a given range in another sheet (say EXTRAS!B80:C92)... I expect this is a simple solution but my own skills are limited... can anyone help me with this.
    Here is the code I have used. I have two textbox entries to update the selected row in the list box (double click) and a Commandbutton to save the changes and copy to the range.

    'Open Form and fill listbox
    Private Sub UserForm_Initialize()


    Dim i As Long
    For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
    Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
    Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 2).Value
    Next i

    End Sub

    'Select Row in Listbox
    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm1.TextBox1.Text = Me.ListBox1.Column(0)
    UserForm1.TextBox2.Text = Me.ListBox1.Column(1)
    End Sub
    'Save edited data
    Private Sub CommandButton1_Click()
    UserForm1.ListBox1.Column(0) = Me.TextBox1.Text
    UserForm1.ListBox1.Column(1) = Me.TextBox2.Text

    Dim x
    x = Me.ListBox1.List
    ThisWorkbook.Worksheets("Sheet1").Range("A2").Resize(UBound(x) + 1, 2).Value = x

    End Sub
    Last edited by SimonCampbell; 05-27-2022 at 08:27 AM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Using Form Listbox to update Cells in Sheet

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

+ 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. VBA to update the existing table and copy new ones form one sheet to another sheet
    By kvelayud in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-24-2019, 05:11 PM
  2. [SOLVED] Update sheet data when editing ListBox items
    By onmyway in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2015, 02:16 PM
  3. [SOLVED] Update (strikethrough) Listbox item selected within sheet
    By ShaunRoos in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2014, 02:45 PM
  4. [SOLVED] form that copy the listbox from other sheet
    By darkhangelsk in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-23-2012, 04:44 AM
  5. Listbox Update on user form
    By Irish_Griffin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-15-2009, 02:41 PM
  6. [SOLVED] get a list of sheet names onto a listbox on a form
    By Grd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2005, 05:05 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