+ Reply to Thread
Results 1 to 14 of 14

Get data from userform to input into specific worksheet with named range

  1. #1
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    88

    Get data from userform to input into specific worksheet with named range

    HI Guys,

    I'm really struggling to get the inputted data from the userform into a specific worksheet (sheet1) Currently it only enters onto the sheet (SalesInvoice) which has the macros on it!!! Very frustrating!!!! I seem to be going round in circles. Any help would be appreciated. Please see the attached copy.
    Attached Files Attached Files

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

    Re: Get data from userform to input into specific worksheet with named range

    Since you call the useform from sheet Invoice and you don't specify the worksheet Sheet1 in your code it's normal that all is put into sheet Invoice.
    You set ws to Sheet1 but then you don't do anything with that.
    If you write
    Please Login or Register  to view this content.
    instead of
    Please Login or Register  to view this content.
    it's nothing but normal behaviour of your code.
    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.

  3. #3
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    88

    Re: Get data from userform to input into specific worksheet with named range

    Thanks for the tips bakerman2, not sure if I'm missing something but I have put what you stated to write and don't have what you say 'instead of'. So I've changed what I've got to what you said I had adding the ws. But I still get an error message saying 'Select method of Range class failed'. Also it debugs to the third instance of the stated line of code. I would expect it to debug to the first instance of its use if there was an error of this sort. Any thoughts? Ive attached the amended vers.
    Attached Files Attached Files

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

    Re: Get data from userform to input into specific worksheet with named range

    Study this to get the logic.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    88

    Re: Get data from userform to input into specific worksheet with named range

    Ah! Ok thanks, I'll give that a whirl.

  6. #6
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    88

    Re: Get data from userform to input into specific worksheet with named range

    Hi bakerman2, Ok I've changed my formulas to the formula layout you suggested for each 'If' but still have the same problem. It inputs the data to the Sales Invoice sheet and not to sheet1. When I have sheet1 open and then run VBA userform from a different screen it inputs the data onto the next row (Great I thought) but when I do it again it just rewrites the data into the same row and not the next row. Ive attached to see if you agree. I feel the incorrect sheet use has something to do with how Sheet1 is Set or if I have to Dim something else. What do you think? As for the 'next row' feature....still perplexed as it seems to be right. EDITED Just solved the the rewrite. The range was looking at "A" should be "B".
    Attached Files Attached Files
    Last edited by Mark Dynes; 03-16-2017 at 01:59 PM.

  7. #7
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    88

    Re: Get data from userform to input into specific worksheet with named range

    OK Got it at last needed to put it like this works a treat.
    Please Login or Register  to view this content.
    Thanks for the help!!!

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Get data from userform to input into specific worksheet with named range

    Mark

    All you are really missing is a bunch of dots.

    Wherever you have Cells within a With...End With block you need .Cells to refer back to the sheet in the With part.

    For example, this,
    Please Login or Register  to view this content.
    needs to be this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

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

    Re: Get data from userform to input into specific worksheet with named range

    Glad to help and thanks for rep+.

  10. #10
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    88

    Re: Get data from userform to input into specific worksheet with named range

    Hi Norrie, thanks I noticed those dots were missing when I added
    Please Login or Register  to view this content.
    in front of
    Please Login or Register  to view this content.
    Does that mean the code would also work without
    Please Login or Register  to view this content.
    ???

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Get data from userform to input into specific worksheet with named range

    Not sure what you mean, where exactly are you using Sheets("Sheet1")?

    If you have something like this then the With/End With structure is kind of redundant.
    Please Login or Register  to view this content.

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

    Re: Get data from userform to input into specific worksheet with named range

    Mark, re read the example code in Post#4.

    This time I used
    Please Login or Register  to view this content.
    but that can also be
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    88

    Re: Get data from userform to input into specific worksheet with named range

    Hi bakerman2, That is what I thought but it wasn't working. Looking back it is clear it was probably not working as a result of the dots being missing from the code. I will try it out again next week for the purposes of self learning. Thanks for all your help and additional comments, really appreciated!

  14. #14
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    88

    Re: Get data from userform to input into specific worksheet with named range

    Yes that's exactly what Ive put and it is working. If the With/End function is redundant with that in place I'll test this out also next week. Thanks for the additional comments. Really helpful!

+ 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. Return specific value from a named range in a userform
    By shiftyspina in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-04-2015, 10:34 AM
  2. Return specific value from a named range in a userform
    By shiftyspina in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2015, 12:18 PM
  3. VB UserForm ComboBox List Based On Dynamic Named Range in Worksheet
    By huntethic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-14-2015, 02:45 PM
  4. [SOLVED] Input Userform Data into Specific Cells & Loop?
    By shiftyspina in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2013, 07:00 AM
  5. How to delete specific columns based on a named range of values from a worksheet
    By dronofrio in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2012, 10:56 AM
  6. [SOLVED] Copy Data loaded via Userform to a worksheet named in 1 field of the Userform
    By audax48 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-15-2012, 01:44 PM
  7. [SOLVED] create named range specific to worksheet
    By beliavsky@aol.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-28-2005, 12: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