Results 1 to 6 of 6

VBA User Form is adding data to wrong row

Threaded View

  1. #1
    Registered User
    Join Date
    02-07-2023
    Location
    Wisconsin, USA
    MS-Off Ver
    Microsoft Office 2016
    Posts
    6

    VBA User Form is adding data to wrong row

    Hey there,

    I'm trying to create a macro for a user form so when the user fills in customer information and clicks submit, it will add a customer name and time stamp to a "customer list" table.
    There will eventually be more than the customer name and time, once I get the rest of the code working properly.

    It is also set to add a row to the table before adding the customer, so I never run out of rows. Not sure if that has anything to do with my issue.

    Every time I run it, it adds the customer to the bottom row (the new row that it added) and still leaves empty rows above it.

    I suppose it doesn't have to add a row if there is already an open row available, but the main issue is the customer should always be added to the first empty row, not the very last row.

    I prefer to not upload the entire spreadsheet because there is some confidential info on it, but here is what I can share...

    I attached a screenshot to that portion of the sheet. The code should've inserted Customer 2 into row 17 as it is the first empty row, but instead, it inserted them into row 20, the last empty row.


    Here is the code I have right now for when the user clicks submit on the user form:

    Private Sub Submit_Button_Click()
    
    'Unprotects sheet while code runs*************
        Dim pswStr As String
            
            pswStr = "1234"
            
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            ActiveSheet.Unprotect Password:=pswStr
                
                
        ' Add-row code starts here************
    
        Dim CurrentSheet As Worksheet
        Dim TableListObject As ListObject
        Dim TableObjectRow As ListRow
    
            'Adds a row
            Set CurrentSheet = Sheets("Daily Goal and Sale Tracker")
            Set TableListObject = CurrentSheet.ListObjects(1)
            Set TableObjectRow = TableListObject.ListRows.Add
    
    
            'User input form code starts here***************
            
            'Selects next open row (the added row)
        Dim NextRow As Long
    
            NextRow = Worksheets("Daily Goal and Sale Tracker").Range("B" & Rows.Count).End(xlUp).row
        
        With Worksheets("Daily Goal and Sale Tracker")
        
        'Adds time stamp
            .Range("A" & NextRow).Value = Now
        
        'Adds Customer Name
            .Range("B" & NextRow).Value = Me.Name_Text.Value
            
            
        
        End With
    
    'Resets form controls after submitting
    
    Me.Name_Text.Value = ""
    
    
    
    
    'Protects sheet after input code runs***************
    
            ActiveSheet.Protect Password:=pswStr, DrawingObjects:=False, _
                    Contents:=True, Scenarios:=False, _
                    AllowFormattingCells:=False, AllowFormattingColumns:=False, _
                    AllowFormattingRows:=False, AllowInsertingColumns:=False, _
                    AllowInsertingRows:=False, AllowInsertingHyperlinks:=False, _
                    AllowDeletingColumns:=False, AllowDeletingRows:=False, _
                    AllowSorting:=False, AllowFiltering:=True, _
                    AllowUsingPivotTables:=False
                    Application.EnableEvents = True
                    Application.ScreenUpdating = True
        
    'Shows confirmation MsgBox
    
    MsgBox "Customer Added Successfully!"
    
    
    End Sub
    I appreciate any info you can share!
    Attached Images Attached Images
    Last edited by cody1p3; 04-30-2023 at 09:36 AM. Reason: screenshot correction

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. User form not adding data to sheet
    By Ashleytaylor1702 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 12-10-2020, 06:15 AM
  2. [SOLVED] Adding data from user form to email
    By echoz in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-04-2017, 10:44 AM
  3. User Form Data Entry Adding a TimeStamp
    By caltman242 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2015, 09:15 AM
  4. [SOLVED] User Form not adding data
    By JO505 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-07-2013, 01:21 PM
  5. Adding "All Borders" with a User Form (Similar to Data Form)
    By kgreave1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2012, 01:57 PM
  6. User Form List Boxes and Adding and Deleting Data
    By learning_vba in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2010, 06:23 PM
  7. Adding data to sheet with user form
    By Jay in forum Excel General
    Replies: 1
    Last Post: 06-08-2005, 11:05 AM

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