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.
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 writeinstead of
Please Login or Register to view this content.it's nothing but normal behaviour of your code.Please Login or Register to view this content.
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.
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.
Study this to get the logic.
Please Login or Register to view this content.
Ah! Ok thanks, I'll give that a whirl.
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".
Last edited by Mark Dynes; 03-16-2017 at 01:59 PM.
OK Got it at last needed to put it like this works a treat.Thanks for the help!!!Please Login or Register to view this content.
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,
needs to be this.Please Login or Register to view this content.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Glad to help and thanks for rep+.
Hi Norrie, thanks I noticed those dots were missing when I addedin front ofPlease Login or Register to view this content.Does that mean the code would also work withoutPlease Login or Register to view this content.???Please Login or Register to view this content.
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.
Mark, re read the example code in Post#4.
This time I usedbut that can also bePlease Login or Register to view this content.Please Login or Register to view this content.
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!
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks