If value=1 then certain price to sheet2 cell

[Ventoux]

Hi everyone, so here's my project that I need help with:

I want to write a macro, which helps me generating a bill.

I have 15 different main products which can be bought: Prog11 to Prog51 (only 1 possible at a time!). Each time there may or may not be additional accessoires purchased with them: OilFull, OilHalf, Cool, WindowFull, WindowHalf.

So starting with product "Prog11" I want it to prompt different values on "sheet2" to be filled. Dat1 and Kenn1 will always be manually filled our by me, but I am not sure how to associate different attributes of "Prog11", like it's price with the respective cell in "Sheet2", in this case previously declared as "Prc2"

Question for a start:

See the IF function below, this is how I want to write it --> If Prog11 takes value=1, then copy dat1 and kenn1 to sheet2 (already works) and price of prog11 (lets say 11,00 €) into "Prc2"

Please Login or Register  to view this content.

[tehneXus]

Hi,

1. Declaring variables like this:

Please Login or Register  to view this content.

makes only Kenn3 a Range, all other variables in this line are Variant

2. For copy a value to a range use

Please Login or Register  to view this content.

This is much faster, note that no formatting is copied

3. Filling range Prc2: Where is the price (lets say 11,00 €) stored? i.e. in which variable/cell?

4. Overall I think you are using a lot of variables which makes the code hard to read

5. A sample sheet for a better understanding would be helpful

[Ventoux]

Hi,

1. Declaring variables like this:

Please Login or Register  to view this content.

makes only Kenn3 a Range, all other variables in this line are Variant

Didn't know that, I found this shortcut on the internet and assumed it would work.

3. Filling range Prc2: Where is the price (lets say 11,00 €) stored? i.e. in which variable/cell?

It isn't stored anywhere just yet. I assumed, that I could just write the prices into each command in the vba editor when needed.

But it's probably better to open a 4th sheet and type in all the prices and later just link them, right?!

5. A sample sheet for a better understanding would be helpful

You're probably right, I just uploaded the file on here.



Thank you for replying anyway, I will compile another sheet tomorrow with all the necessary values like prices and stuff, so it will be easier to code.

[tehneXus]

Wie wärs damit/How about that.

You will enter the price into an inputbox and can decide how many bills will be created

Please Login or Register  to view this content.

[Ventoux]

Thanks a lot for your effort!!!

Since I am a beginner at VBA however, I have a hard time understanding what to do with your code
Also just for clearfication, my accessory products (Öl, Kühlwasser, Scheibenwasser) have to be displayed on a seperate bill, thats why I created a sheet3.
the amount purchased can go from 0.5 liters to 4 liters max, whole liters and half liters have different prices though, thats why I made seperate cells for 0,5 and 1.

I think I will try to build on my initial approach to this, but if you are willing to help and explain your approach, that would be really nice! (Although I feel bad that you put so much effort into it, thanks again)

[tehneXus]

You can make one workbook "A" with test data before the macro and workbook "B" after the macro, right now I didn't get the details of what you want to do

[Ventoux]

OK, so I added Sheet5 called Example Input Data.

input.xlsm


I typed them into sheet1 as I would like to do when the macro works. (although I would do so only in row 4, i only typed the other ones beneath it, so you would understand how i want the file to work)

I then simulated the macro and filled out sheet2 and 3 as i would want them in the future.

I hope this is clear and not too complicated, again thanks a lot for your help!!


(Just to make it clear, sheet 5 would not exist in future.. I get this data hand written on a paper, and would type them into sheet 1 and hopefully receive a bill as in sheet2 and 3)

[tehneXus]

Soo,

Sheet2 and Sheet3 unfortunately had no records which could show the design of the bill.

However, I tried to fill Sheet1 with the info from Sheet5 and created prices (table below) using formulas for those entries according to the PLU and the price list. I assume there can be multiple cars from 1 company receiving one bill for all of their cars. Please confirm that you'd like to enter the data like this, then we will write code to produce the bills. Would you like to insert a column "Steuerklasse"? How is "Sonder" inserted into the bill? Would you like to automatize entering the company details (name, adress,..) to the bill? How is "Rechnungsnummer" assigned?

Apart from that I've included a Userform for data entry, maybe you prefer (at least I do) entering data like this. Currently its designed for 1 record but can be changed to enter more and produce bills without entering data into cells directly, which is less error-prone. The form is opened with the button on sheet1

input.xlsm

[Ventoux]

I can't thank you enough!!!

Let me upload my editing of sheet 2 and 3 again, I must have uploaded the wrong version in the previous posting.

-->neu.xlsm

The userform you created looks great! Wow, good job! Didn't even know that was possible with VBA.

Btw, the inclusion of "Steuerklasse" is not necessary, since it is "4" for any product.

Thanks again ... I initialy wanted to create this macro, to take some work off of my father, but I think that this (especially the user form) will really surprise him

[tehneXus]

Check if this works for you: input.xlsm

[Ventoux]

Hi,

unfortunately I'm having trouble opening the form. When starting the excel file, I am getting an error right away, saying something about having to update the code in this project for use on 64bit systems. Then it's highlighting the line

Please Login or Register  to view this content.

without me even opening VBA window.

Here is a screen shot: errormessage.JPG

I am gonna check this file on an older 32bit system later, but it has excel 97 on or something, so it will probable not work either

edit: ok the older system gives me following warning: "Fehler beim Kompilieren: Projekt oder Bibliothek nicht gefunden".

Also upon starting the file, both systems give me the error message, that excel "could not load some objects because they are not available on this machine". I had the same error message on your previous version of this macro, but thought it was due to the fact, that it wasn't completed yet.

[Ventoux]

So, do you think there is a way to fix this? I could also do with a way simpler version than the last one.

[tehneXus]

Hi,

sorry for answering so late.

As I currently cannot upload attachments I will instruct you how to change the code, mostly delete code:
1. Remove the module "mdlGetPrinter"
2. Remove the following functions from "mdlFormHelp": CapsLock, NumLock, KeyState
3. Remove the declaration "GetKeyState" from "mdlFormHelp"
4. From "txtKenn_KeyDown" and "txtSonderPreis_KeyDown" in "frmBill" and from "txtBox_KeyDown" in "clsTxtAmount" remove the following code:

Please Login or Register  to view this content.

Then goto Debug -> Compile VBAProject to check the code, if there will be an error message let me know, if nothing will happen everything is ok and the code should work on your 64bit machine.

[Ventoux]

Hi, thank you for your post.

I followed all your remarks and get a "compile error" saying "Can't find project or library".

It's highlighting the line

Please Login or Register  to view this content.

in module "mdlFormHelp".

When closing the error message a window called "References - VBAProject" opens with some listed files and applications and libraries with checked boxes beside them (unfortunately I can't upload the screenshot).

So I assume, that what I have to do is klick the box next to the respective application/library, that the macro is using, but is not active on my system.

[Ventoux]

OK, I fixed the missing library error on my system. Now I'm getting a new compile error saying "Method or data member not found" and referncing to code

Please Login or Register  to view this content.

and highlighting .lv specifically.

Unfortunately I can't find anything regarding .lv in the help file.

Edit: in frmBILL's sub Private Sub UserForm_Initialize()

[tehneXus]

The ListView which is used on that form is not available on 64bit versions of Excel. I will try something with the list box, will let you know on friday

[tehneXus]

check if this works: input_new.xlsm

[Ventoux]

Hi, unfortunately my system is not able to handle the .dtDate command, I am getting the library not found errors again.

But I dont have any missing references in the reference window now

Maybe it would work, if you showed me a list of your active libraries in VBA and I just activate the same for my system?

[tehneXus]

The point is: a lot of stuff is not working with the 64bit version of excel... I'd recommend to use 32bit if you are not working with files larger than 2GB.

Back to the problem: try # input_new.xlsm #

[Ventoux]

OK, the good thing is I am not getting the compile errors anymore, unfortunately though, I am getting different ones.

When filling out the form and pressing "Posten übernehmen", everything is transfered into the window just fine! But once i press "Rechnung erstellen", I get three different errors (see these screenshots, please: error1.JPG error2.JPG error3.JPG).
Then a window pops up where I can choose the customer, window.JPG but there is no actual data in the drop down menu. And when trying to fill in the data manually into the window, I get the errors from above, evertime I type in a new letter.

However, when typing a few letters (and closing the errors each time) and pressing "Kunde verwenden", it actually transforms all the data into the bills exactly as I want them to! So good job there and thank you!

So maybe we could just leave the "Kunden" feature out and I type the customer manually into the bill each time (which would be no problem at all)?

[tehneXus]

Looks like the ADODB does not work.. try: # input_new.xlsm #

[Ventoux]

It works!!! You did an excellent job there, thank you so much!
I really appreciate all the effort you put into this.
I wish I was this talented

[Ventoux]

Hey, little problem here: After I put in 35 positions, it didn't convert the next position into the window, instead it asked me whether I wanted to replace a position in the window.

Is there a limit of how many positions I can transfer into the bill at once? The Wagenpflege bill is 6 lines into page two, exactly as I want it to, but why wasn't I able to put in more positions?

thanks.

[tehneXus]

Hi,

there is no limit but you cannot insert a duplicate into the list. An entry is identified by "Date" and "Kennzeichen", if you want to disable this just edit/remove the function "RecordExistsInLB" in "mdlFormHelp"

[Ventoux]

Sorry, my bad!

I accidentally typed in the same licence plate two times in a row, which caused the programme to ask me whether I wanted to replace the entry before that.

So everything works just fine