Solved

How to Auto-fill data in Database; VBA Code Mixed Combining Two User Forms

Posted on 2016-11-20
4
69 Views
Last Modified: 2016-11-22
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
Comment
Question by:Paul Clayton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41894593
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
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41894606
There's been a change of plan for today, so I am working through your code and will post a revised workbook later.
0
 
LVL 20

Accepted Solution

by:
Roy Cox earned 500 total points
ID: 41894696
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
 

Author Comment

by:Paul Clayton
ID: 41895294
Particular issues solved.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question