Macro alternation

[joostlaane]

hello everybody,


I have a excel file that i copied from the internet, i have adapted it to my needs, but i need some help with the macro "nieuwe factuur". if i click on it twice all the data goes away, i would like that the info that is now there stays there after clicking.


also in the column "beschrijving" is there a possibility that the size of the cell alters because there is more text there?


thanks in advance


below you can find the doc:

https://docs.google.com/file/d/0BzFJe5u0_x_Fck1vYTVHQlpsMUk/edit


https://docs.google.com/spreadsheet/ccc?key=0AjFJe5u0_x_FdHMyaTI5bGVjb29kcnhuWElKWFloeHc#gid=0

 

[SirJB7]

Hi, joostlaane!


Let's see where do we get with my zero-nederlands skills.


a) The two links are for the same file? Verkoopfactuur5.xls? Second links seems to be the same file without macros, is it? I'll go on with the first one.


b) I couldn't find a macro "niewe factuur" but a "NieuweFactuurStarten" associated to button "Nieuwe". When you click on button, it acts upon variable ReageerOpTweedeKlik contents:

- if 0, clears range "Accept" (green zone B55:O70), clears A1 cell (linked to client dropdown list in row 9, so clears client data). calls two procedures "LeesFactuurnummer" and "NoteerFactuurnummer", and sets variable to 1

- if 1, clears ranges "Factuurregels" (C19:M35) and "Verzendkosten" (N37) and sets variable to 2

- if 2, does nothing

So I don't know what should be done, it works as described. It's up to you to maintain or alter its behaviour... at your own risk as I guess there's no support for this.


c) About "beschrijving" description column, only row 21 has set "Adjust Text" property (or something like that, in spanish "Ajustar texto"). That has been manually set because there's no reference within the code. If you select the same item in row 22 (blank) row height remains standard. I don't know how will this affect when printing.


I got this far and hope it serves you as a guide for customizing it.


Regards!

 

[joostlaane]

Hi SirJB7,


a) indeed they are both the same file, one in google docs and one in MS Excel


b)the macro "NieuweFactuurStarten" is associated with the button nieuwe, but if you click on it once the contact details go back to 0 (which is a good thing). What i am trying to alter is that if you click on it twice, that the formulas like =VLOOKUP(C20;Sheet2!A:L;3) remains in that cell. But the description should go away, until you choose a new item from the dropdownlist and then it should appear again.


c) Is it possible that the height of the cells alters if the size of the text changes as well? e.g. the product B10 is a very short despcription but the product FAA60 is way longer. is there a macro that changes automaticly the size of the cell?


I know there is no support for this kind of programs, especially not in Dutch, but Thank you for taking your time and helping me out!


warm regards

 

[kalpesh39]

sir how to post & were to post my question i am not getting it on foruams home plz help i have a trouble to prepare a workbook

 

[SirJB7]

Hi, joostlane!


a) Ok, gotcha


b) In that macro, replace this line:

Range("Factuurregels") = ""

by these lines:

Range("Factuurregels").Columns(1) = ""

Range("Factuurregels").Columns(11) = ""


c) Adding this code to worksheet VBA for Change event didn't work (but it should)... so, try manually.

-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Row >= 19 And .Row <= 35 And .Column = 3 Then
Application.EnableEvents = False
ActiveSheet.Unprotect
.EntireRow.AutoFit
ActiveSheet.Protect
Application.EnableEvents = True
End If
End With
End Sub

[/pre]
-----


d) Also:

i. reset row 21 height to normal (copy format from row 20)

ii. copy D22 formula to D19:D35 (for avoiding N/A when blank -first two items-)


You owe me a Dutch course... at least... but in spanish, english, or french, or portuguese, ...


Regards!

 

[SirJB7]

@kalpesh39

Hi!

Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!

 

[tieunguyet]

Today the company to build a website for a job so important that can not simply say yes or no to it. Website has become the media and the leading business tool for every business, it brings an undeniable advantage. Here are the reasons to start building a website: Website

* Establish business presence on the internet, the opportunity to interact with customers anywhere and at any time. ホームページ製作

* Introduction of products and services in a lively and interactive. ホームページ制作

* Create opportunities to sell goods in a professional manner without costly. ホームページ作成

* The opportunity to serve customers better, achieve greater satisfaction from customers. ホームページ作成ソフト

* Create a professional image in public, effective tool to implement marketing and PR campaigns. ホームページセミナー

* The website is simply not a cause of failure of the business. CMS

Your website is not only eye-catching, fast loading, meet customer needs, user-friendly but also friendly to Google, Yahoo, Bing (the popular search engines today) to website is always a high position on search engines this. Web promotion would be simpler if your website is programmed with the search engine-friendly from the beginning. With the development of talented engineers, professionals, OSS will design you a website with everything you need to exploit the strengths of the internet, making the reader interested in learning more about:

* The mission of your company. ビジネスブログ

* Services of your company.

* Help you identify the object.

* Create the form and feel according to your specific needs.

* Making site is always lively and eye-catching access.