I am pretty much a beginner when it comes to VB. I need help with a project I am working on that has me ready to rip my hair out. Here is the what I need to do:
When I open a workbook (excel) I want it to display a form where I can enter in the date and various data.
I need that form to add that data that I entered to the worksheet in the coloumn that corrisponds with that particular information.
It also needs to copy that row's formulas and drop it down one row.
Last I need it to automaticaly update 3 diffrent charts that have more than one data line.
I am at a loss. PLEASE HELP!!!
Excel, Forms, and Charts
Large_Goose
Hi Sadavies,
When you output information to a sheet a row at a time you need to use a variable to hold the current row that your writing information to. So when you output 10 rows you know the next row you output to is row 11.
Dim nEndRowIndex As Double
nEndRowIndex = Worksheets("MySheet").Range("A2").End(xlDown).Row
When the user closes the workbook and then reopens it you need to work out what row to start outputing information to.
Outputting to row 1 will override what went before, so when the user opens the workbook you need to workout that the output row will be 11. Thats what the above code does, there are other ways to do it, but that code works out what the next blank row from cell A2 will be.
When you do this is up to you. You just need to do this before you write the new line. You can call the code anytime between the user opening the workbook to just before you write the information to the row.
It would be a good idea to call it on the Workbook_OnOpen event as thats a good place to but initalisation code, however that means you need to use a global variable.
You could also call it just before you write the first line of a newly opened workbook. For example if the output row is 0 then workout the row and output.
hope thats cleared it up. if not let me know.
Sebastian Roth
That would be great!! I can figure it out from there. Thank you so much, I look foward to your next post.
Tiger
stubs
I'm new to the forum and am so far very impressed with the wealth of knowledge out there. I downloaded your example and it does exactly what I was looking for, but I to am having similar problems getting the line to increment by 1 each time. I couldn't see where this additional peice of VBA code was suppost to sit. I've tried it in a few places but keep getting a compiler error.
Any help would be appreciated as this will solve 1 of my problems straight away.
Lance P. Smith
Excel has a built in Data Entry Form capability which is much easier to use. It keeps track of all of these details for you, such as length of the list, and inserting a record in the appropriate place.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Jesse Arnold
I meant to include this link:
http://contextures.com/xlDataEntry01.html
RickBoogher
Hi Jon, I did not know that.
Do you have information on how it works
JasonPolycom
Your asking a lot there. You really need examples, I'll see what I have at home, I'll post again later.
TylEray
Thank you very much for the help. I was finally able to get it to work.
Tiger
Vijay TV
Tiger, I had a look and the spreadsheets I have aren't really mine to give, they belong to my company. There also extremely specific and contain additional logic that hides how to do the simple things.
However I've created a real rough spreadsheet that demonstrates everything on your list apart from updating the charts. Which I couldn't for the life of me remember how to do. The example should get you started and demonstrate the things you need to do.
UserformExample.xls
jazman58
This did help a lot. But I'm still having trouble getting it to allow the new information to go into the next row.
If I enter information in and click add then copy. It puts the information into the first line. If I click add again it only changes the information in that first line instead of the line underneath it and continue going down as I add more information.
Thanks again for your help. This made a failing project get some progress.
Tiger
RichardWard
Good stuff.
You need a variable that holds the index of the current row (or even store the number of records) and increment this as the user adds new items. It's like a pointer, user adds a new record, output record at pointers position, move pointer to next row, user adds a new record, output record at pointer position, move pointer to next row.
When your user opens the workbook you need to set this pointer to the correct row. You can do this by looping down the list until you come across a blank value or you can use the following:
Dim nEndRowIndex As Double
nEndRowIndex = Worksheets("MySheet").Range("A2").End(xlDown).Row
Which finds the last cell populated in the A column from row 2 (only works if there are no blank cells inbetween the columns values).
Or you can store the value in a cell on a hidden worksheet.
rd2h
Hi sadavies,
Can you post the bit of your code thats causing your problem. That would help me see what's happening.
karnayanar
Hello Derek,
I have downloaded a copy of your UserFormExample.xls and understood how the macros worked within it. I then saw your post about adding the following code that allowed the data to automatically be entered 1 row lower than the last.
Dim nEndRowIndex As Double
nEndRowIndex = Worksheets("MySheet").Range("A2").End(xlDown).Row
The problem I have is knowing where this piece of code needs to be. Within the VBAProject there are the following options
Microsoft Excel Objects
Forms
I just don't know where to put this additional code to get the data to be entered 1 row down than the previous. Hope this makes sense.