VBA Macro to record data in set cells

[sara1989]

Hi see attached

I have a Userform with Set Vehicle registrations,
I have to log all the fuel records for each vehicle each date and the miles and litres drawn,
Anyone know when i press ok cmd button it will record the information in the set cells >?

Thanks

[Tinbendr]

Ugh! Your code has no comments. (Nicely written though.)

1. Will all the vehicles always be on the Master?
2. How will you handle a new week?
3. Where is the URN number stored. There's no data on your sheet to compare.
4. Can you explain the flow of the userform? What will the OK button do? Add new records and/or Update others?

[sara1989]

1. Yes the vehicles will always Been in the master sheet
2. For a new week possibly a new sheet within the workbook?
3. The urn counts up every time a new record is logged.
4.the userforms is just to save the info for each truck daily fuel. Basically the driver will return to the office and hand in the paperwork. We will fill in the form and I need the info to save to the sheet. The sheet cannot be changed as this is the layout from my manager ?
I know how to write the lines but not sure how to write data to certain cells

[Tinbendr]

3. But where is stored on the Master?

[sara1989]

The urn can go anywhere on the master.

[Tinbendr]

And how is the URN calculated? Is it just a sequential number? Does it start over at the beginning of the year? Is there a limit to the length of the number?

[sara1989]

No limit starts from 1 and just counts up +1 to previous number each time a new record is created.

[Tinbendr]

URN seq# stored on Data sheet. Each time record added, number inclremented by 1.

URN is recorded under Reg#.

Fixed formula at end to reflect total that week.

Added Mileage traveled by subtracting largest and smallest number.

[sara1989]

Wow thanks for this but when i try a record 1 for another registration, it gives me a code in this line

dCol = WS.Range("C3:I3").Find(Format(Me.txtDate, "dd.mm"), , xlValues).Column

and says Run time error 91
Object variable or with block variable not set ?
Any ideas?

Thanks

[Tinbendr]

You didn't enter the date correctly. I noticed that there is no error checking for empty fileds. I thought we might get around to that later.

[sara1989]

ignore me i realised i needed to make sure the date entered was on my sheet ? this is perfect for what i need.

Is there any way i can stop it throwing up an error though if the date with the range on my sheet is not there?
Or a message box saying "This date is not within the current week ?" or somthing like that

Just because other people will be using and this will cause them major confusion if an error pops up

[Tinbendr]

This sems to work. The same is done for the Recrow on the off chance someone types in a Reg# in the combobox field.

BTW, I changed the place the combobox was getting the Reg#'s from. It was linked to a named range, but that range didn't have the space in the Reg# and it was causing problems with finding it on the Master. Now the items in the combobox come from the Master sheet. I also store the row it came from in column 2 (Which is not visible). That is where RecRow comes from, but you can type into a combobox field which would render my variable a 0, thus causing and error.

Please Login or Register  to view this content.

[sara1989]

Thanks Tinbendr

Next question, This works great and perfect for what i need, But as this is for a weekly fuel record. For each week i will have to create a new sheet ?

is there a way for it to look across more than 1 sheet so i can produce the weekly sheet ready ?

[sara1989]

Thanks Tinbendr

Next question, This works great and perfect for what i need, But as this is for a weekly fuel record. For each week i will have to create a new sheet ?

is there a way for it to look across more than 1 sheet so i can produce the weekly sheet ready ?

[Tinbendr]

is there a way for it to look across more than 1 sheet so i can produce the weekly sheet ready ?

HA! I knew this was coming.

How do you want to handle it? Do you want a new sheet for each week? I'm envisioning a button on the userform to "Start a new week'. A calendar will pop up for you to select the week. We'll rename the sheet with the starting date. (Or a range of dates E.g. Week 18.01-24.01)

Or if you can get the boss to move the Total column to the left, say, next to the registration number, then you can put as many days to the right. We'd just have to set the printout range to what's on the screen.

Or you could just put all the data on a database (flat) sheet. Then, produce a weekly 'report' that represents the current Master. I started this on the file I u/l. It has a AllRec sheet in it.

[sara1989]

well Tinbendr, anything to make my life easier

The 1st Suggestion sounds great ?
A calendar to chose the W/C, any help in implementing this at all ?

Would this create a new sheet and Continue the process ?

[Tinbendr]

Would this create a new sheet and Continue the process ?

Yes. Basically I would make a clean copy of the master. Then when a new week is started, I simply make a copy of that template sheet, rename it to the week name suggested earlier. I'll create a button on the ribbon to invoke the userform. The userform will write to the current sheet. Or maybe ask for the sheet to write to, in case there's a driver who 'can't find his receipt' for a week. Then you can just pick the week sheet to work on.

Sound good?

[sara1989]

Yes that sounds perfect and exactly what i need. Thank you for your help so far.

[Tinbendr]

OK, Give this a spin and see how it performs.

1. Added a Ribbon Tab. It's next to the home tab. This calls the userform.
2. I changed the date in C3 to an actual date. I created a formula to add one to calculate the other days of the week. I then use the format to display the date in the DD.MM format. This makes the integration of the popup calendar easier.
3. I had to delete all the named ranges that were on the sheet, because when you duplicate it, it sends Excel into a tail spin trying to handle all the now duplicate named ranges. (It wants you to rename them all.) I'm not sure how this will effect the operation. I don't think I use any named ranges in the VBA code, but it's late and I didn't check.
4. Added a command button and combobox to allow the user to select a week to work with. On userform start, all sheets with the DD.MM named format are loaded into it. The commandbutton calls a calendar to allow a date to be picked. ANY day selected (If not Sunday) will be calculated to the previous Sunday to get the start of the week.

[sara1989]

Wow Thanks Tinbender for this, I Have just read what you have writtern and downoaded it but when i click add fuel record i have an error imedialtly - RUN TIME ERROR 424 - Object require. When i press debug
This part of the code has the error

Please Login or Register  to view this content.

[Tinbendr]

How are you running it? With the Ribbon button(look next to Home Tab) . Or button on userform sheet?(Will eventually be removed.)

[sara1989]

i was trying to use the Button can this be removed by just deleting this or will this effect the way the userform works and opens ?

[sara1989]

Ok so im trying this out, 1 thing i have found is....
Bear in mind we have more than 1 user within this sheet and they are not very Excell trained

if a user is to open the sheet and stupidity selects say 19.01.2015 and created a sheet for this and logs the record,.
Then the second person logs on and wants to log a record for say the 20.01.2015 the day after. They may create another sheet which is Week commencing 20.01.2015 when they could have just logged the date on the 19.01.2015 the sheet the user created before?
If that makes sense so in effect there could be sheet for every day? which data would be everywhere

Is there a way to look up to see if a Sheet already contains the date and not create a sheet if this date is already on a sheet >?

Thanks for all your help currently

[Tinbendr]

You can. I just *assumed* that you would like the ribbon button. It can be a pain to deal with sometimes.

But we can always create a simple macro in one of the modules, that then, you can call with Alt-F8 or something.

[Tinbendr]

Is there a way to look up to see if a Sheet already contains the date

I thought I did. Have you tried it?

Look at the last sentence of 4, post#19.