Hi guys and girls,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
<o
></o
>
I'm working on a new excel form and have the input ready, now the layout needs some more attention and i'm confused
. I tried to understand the posts on the forum and found some interesting info, but not quite exactly the information i need.<o
></o
>
<o
></o
>
I've got information in 8 coloms, A to H:<o
></o
>
<o
><TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=376 border=0 x:str><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" span=3 width=25><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=45 height=17>General</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=75>Cost category</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=72>Sub category</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=55>applicable</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=25>etc.</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=25>etc.</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=25>etc.</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=54>Total cost</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>a, b</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">a, b, c</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">a, b, c, d, e, f</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">yes/no</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>2</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>3</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>1</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num="10">€ 10,00 </TD></TR></TBODY></TABLE></o
>
<o
></o
>
The input in row two is dependant from drop down menu's. If for instance you select general a, cost category b and sub cost category d is applicable "yes", then some other drop down menu's should exclude certain drop down options that cannot be used anymore as a result of this choise. <o
></o
>
<o
></o
>
On the contrary: also after a specific selection of choises in the colums, it might mean that this cost category always comes together with another line with specific criteria, IE.: when in row 10 you make the choice for adding cost in category demolishing costs, excel should immediately insert a new line based on this criteria that selects the adherent cost, for instance the cost of cleaning up after demolishing which is this case is obligatory because it comes together with demolishing costs.<o
></o
>
<o
></o
>
In this form I think I'd prefer working with drop down menu's but I'm nog quite sure if they work for the kind of result I want to get. <o
></o
>
If you have better solutions, i would appreciate if you share them with me.<o
></o
>
<o
></o
>
On this forum I already found information about inserting a line after certain criteria, but I guess this code needs adjustments as well.<o
></o
>
<o
></o
>
Code:<o
></o
>
Sub insertLine() Application.ScreenUpdating = False Set rng = Columns(1) 'change to whatever column number the criteria is in Set c = rng.Find(what:=2009) If Not c Is Nothing Then a = c.Address Do c.Offset(1, 0).EntireRow.Insert Set c = rng.FindNext(c) Loop While c.Address <> a End IfEnd Sub<o
></o
>
<o
></o
>
Curious what you come up with!<o
></o
>
<o
></o
>
Thanks already
.
<o
></o
>
Twan<o
></o
>
<o
I'm working on a new excel form and have the input ready, now the layout needs some more attention and i'm confused
<o
I've got information in 8 coloms, A to H:<o
<o
<o
The input in row two is dependant from drop down menu's. If for instance you select general a, cost category b and sub cost category d is applicable "yes", then some other drop down menu's should exclude certain drop down options that cannot be used anymore as a result of this choise. <o
<o
On the contrary: also after a specific selection of choises in the colums, it might mean that this cost category always comes together with another line with specific criteria, IE.: when in row 10 you make the choice for adding cost in category demolishing costs, excel should immediately insert a new line based on this criteria that selects the adherent cost, for instance the cost of cleaning up after demolishing which is this case is obligatory because it comes together with demolishing costs.<o
<o
In this form I think I'd prefer working with drop down menu's but I'm nog quite sure if they work for the kind of result I want to get. <o
If you have better solutions, i would appreciate if you share them with me.<o
<o
On this forum I already found information about inserting a line after certain criteria, but I guess this code needs adjustments as well.<o
<o
Code:<o
Sub insertLine() Application.ScreenUpdating = False Set rng = Columns(1) 'change to whatever column number the criteria is in Set c = rng.Find(what:=2009) If Not c Is Nothing Then a = c.Address Do c.Offset(1, 0).EntireRow.Insert Set c = rng.FindNext(c) Loop While c.Address <> a End IfEnd Sub<o
<o
Curious what you come up with!<o
<o
Thanks already
<o
Twan<o
Last edited: