• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • Last Modified:

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$15000),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
0
Paul Clayton
Asked:
Paul Clayton
  • 3
1 Solution
 
Roy CoxGroup Finance ManagerCommented:
I've corrected most of the problems in the other posts.

Change this line

Set rng = ("Sheet1, Table1")

Open in new window


to

Set rng =sheet1,Range("A1").CurrentRegion

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
0
 
Roy CoxGroup Finance ManagerCommented:
There's been a change of plan for today, so I am working through your code and will post a revised workbook later.
0
 
Roy CoxGroup Finance ManagerCommented:
Ok

I've worked my way through the code and made amendments

I've created a procedure that will load the TextBoxes and is shared by several controls, this eliminates writing the code for each button.

When adding a new line I am assuming that the date will be the next day, so the code autofills the day and date boxes.

I'm not sure what the reports are. Are you looking to print a report between two dates?
00-Test_User_Form-18Nov--2-.xlsm
0
 
Paul ClaytonAuthor Commented:
Particular issues solved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now