+ Reply to Thread
Results 1 to 20 of 20

User form not adding data to sheet

  1. #1
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    User form not adding data to sheet

    Hi,

    Can any one have a look at the following and see if I am missing anything obvious

    When i run the user form I get no errors but no text is entered to relevant cells

    Please Login or Register  to view this content.
    Also could the same
    Please Login or Register  to view this content.
    be used for a combo box?

    Many Thanks

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: User form not adding data to sheet

    have you tried adding to the uf qualifier?
    Please Login or Register  to view this content.
    the code works fine for me:
    Attached Images Attached Images

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: User form not adding data to sheet

    Are you sure your column A is empty below the last entered data?

    Because otherwise you are entering data somewhere down your worksheet.

    Also Range("A65536") is oldskool, use Range("A" & wks.Rows.Count) instead.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  4. #4
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    Re: User form not adding data to sheet

    Just tried that and still no joy

  5. #5
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    Re: User form not adding data to sheet

    I have just checked and Column A is empty and I have amended the code but still not working

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: User form not adding data to sheet

    If you're not getting an error, then the data is going somewhere. Are you sure you're looking at the correct sheet? Sheet1 is a codename and is not necessarily the sheet with Sheet1 as its tab caption.
    Rory

  7. #7
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    Re: User form not adding data to sheet

    Thank you for your help all solved

  8. #8
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: User form not adding data to sheet

    Quote Originally Posted by rorya View Post
    If you're not getting an error, then the data is going somewhere. Are you sure you're looking at the correct sheet? Sheet1 is a codename and is not necessarily the sheet with Sheet1 as its tab caption.
    I ran it with SHEET1 and I had another word as the caption. apparently that matters not.

  9. #9
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    Re: User form not adding data to sheet

    Me again!

    I need to stop users entering duplicate information

    How can I change the code so that the User Form checks if the Value already exists in each Column and if it does display a msg box and not allow them to enter a duplicate?
    here is my existing code:

    Please Login or Register  to view this content.
    Many Thanks

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: User form not adding data to sheet

    Do you mean it should check all the columns and not allow the entry at all if any of them are duplicated, or check each column and only disallow each column if it's a duplicate?

  11. #11
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    Re: User form not adding data to sheet

    It should check all columns please and not allow entry if any are duplicated

    Thanks

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: User form not adding data to sheet

    Just FYI the code you posted would never run, as you have put several subroutines inside another one.

    Try this:

    Please Login or Register  to view this content.
    You might want to add a message in an Else clause for each If just to let the user know why the data isn't being added!

  13. #13
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    Re: User form not adding data to sheet

    Could you please give an example of the position of the else clause?

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: User form not adding data to sheet

    You would have one before each End If line, so that you can specify which value is a duplicate.

  15. #15
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    Re: User form not adding data to sheet

    Hi,

    Just tried that but it only returns the last msg box regardless of the duplicate

    Please Login or Register  to view this content.
    Any ideas?

  16. #16
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: User form not adding data to sheet

    How about this.

    Please Login or Register  to view this content.

  17. #17
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: User form not adding data to sheet

    How about uploading your workbook ??????????
    Sixteen posts and barely one step forward.
    torachan.

  18. #18
    Registered User
    Join Date
    12-06-2020
    Location
    Forl', Italy
    MS-Off Ver
    Office 2019
    Posts
    20

    Re: User form not adding data to sheet

    Try this:

    Private Sub cmdAddTooling_Click()
    Dim AddNew As Range

    Set AddNew = [Sheet1!A1].End(xlDown)
    AddNew(2, 1) = "txtPartNumber"
    AddNew(2, 2) = "txtTool"
    AddNew(2, 3) = "txtDrawing"
    AddNew(2, 4) = "txtNumber"

    End Sub

  19. #19
    Registered User
    Join Date
    07-10-2018
    Location
    Uk
    MS-Off Ver
    MS365 Version 2102
    Posts
    86

    Re: User form not adding data to sheet

    I have got this working now however I didn't explain what I needed to achieve very well:

    If a duplicate is found in the columns then I would like an error message as well as not being able to enter any non duplicated information into the row

    Also I would like the default value in the TCNNumber input box to default to +1 of the the highest number in that column

    Thanks for all your help

  20. #20
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: User form not adding data to sheet

    Did you try the code provided in Post#16?

+ 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. popped a warning message to before adding the data into excel sheet from user form
    By limhuihui in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2018, 10:19 PM
  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] Restricting the user form to adding to excel sheet
    By shiftyspina in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-24-2013, 07:41 AM
  5. [SOLVED] User Form not adding data
    By JO505 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-07-2013, 01:21 PM
  6. 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
  7. Adding data to sheet with user form
    By Jay in forum Excel General
    Replies: 1
    Last Post: 06-08-2005, 11:05 AM

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