Paul Clayton
asked on
How to Auto-fill data in Database; VBA Code Mixed Combining Two User Forms
I had this question after viewing VBA Code Mixed Combining Two User Forms.
Hi Roy,
I posted my queries in two posts, both of which you have kindly responded to:
1. In the query "How to Auto-fill data in Database" you ask how line Set rng = ("Sheet1, Table1") is working. Simple answer is I don't really know! It probably comes from copying and pasting related to the second query; I was not aware that I could use use the Table format to automatically extend the data set! I've since made the data range Table1 with the code =OFFSET(Sheet1!$A$1:$H$11, 1,0,COUNTA (Sheet1!$A $2:$H$1500 0),8) so hope that could take care of the auto updating but haven't been able to test it.
2. "VBA Code Mixed Combining Two User Forms" is where I have cobbled together from different sources (copying and pasting various bits of code), but I have been struggling with this for more than a few days now. I don't think you have assisted me directly however it is highly likely I have picked up something for your efforts elsewhere!
I really just want to get this project out of my hair so bearing my mind I am really a novice, feel free to doctor/streamline my file wherever you think its wrong, however your comments where such changes are made would be appreciated so that I can try to learn from my mistakes.
I've just seen your most recent response and will take a look at this now. If you can consider the above comments when you have more time I would appreciate any updates that may be forthcoming.
Regards,
Paul
Hi Roy,
I posted my queries in two posts, both of which you have kindly responded to:
1. In the query "How to Auto-fill data in Database" you ask how line Set rng = ("Sheet1, Table1") is working. Simple answer is I don't really know! It probably comes from copying and pasting related to the second query; I was not aware that I could use use the Table format to automatically extend the data set! I've since made the data range Table1 with the code =OFFSET(Sheet1!$A$1:$H$11,
2. "VBA Code Mixed Combining Two User Forms" is where I have cobbled together from different sources (copying and pasting various bits of code), but I have been struggling with this for more than a few days now. I don't think you have assisted me directly however it is highly likely I have picked up something for your efforts elsewhere!
I really just want to get this project out of my hair so bearing my mind I am really a novice, feel free to doctor/streamline my file wherever you think its wrong, however your comments where such changes are made would be appreciated so that I can try to learn from my mistakes.
I've just seen your most recent response and will take a look at this now. If you can consider the above comments when you have more time I would appreciate any updates that may be forthcoming.
Regards,
Paul
There's been a change of plan for today, so I am working through your code and will post a revised workbook later.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Particular issues solved.
Change this line
Open in new window
to
Open in new window
This has been done
You seem to be mixing code from all sorts of sources. I have tidied up a lot of it, when I have time I will go through the complete code and tidy it up, but I think it is working now.
You may have trouble with the DatePickers, not every computer can use the DatePicker control. I usually use a non ActiveX version, samples on my web site
Alternate DatePickers for Excel
Take a look at my DatabaseForm which does everything that you are trying
An Excel DatabaseForm to adapt for your own projects
I'll have a look at streamlining the code, hopefully I will be finished in time to look at it this evening. In the meantime be careful when mixing code from different sources.
00-Test_User_Form-18Nov--1-.xlsm