Form with adding

[Spiccoli]

Hello

I want to create a form for people who use a cabin

The columns titles are: name, number of nights, age group, $ per night, then total $'s

The $'s per night depends on the age group. It's $10 for ages 0-12, $20 for 13-18, and $30 for 19+. I'm doing a drop down menu for the age groups. Based on their choice, I want the $'s per night to come up automatically in the next column

My questions are
1) Is it best to be doing this in Excel, or should I use Word
2) Assuming Excel, how do I set up the $'s per night based on the drop down menu selection
3) I'd like to have the totals at the bottom of my form, so how do I account for the variety in rows that may be needed by various users without having a whole bunch of blank rows at the bottom
4) I know I can lock the spreadsheet and then allow the user to only change appropriate cells, but is there a way to have the user just tab through the changeable cells

I think those are all my questions. Thanks very much

Jeff :

 

[Nick Hodge]

Jeff

1) Excel's fine
2) Set up the dropdown list using Data>Validation>List and type the three
type in the box, separated by commas. Then use a series of IF functions to
look at the data
3) Total at the bottom are tricky if the list could be one or 100 items
long. You can either have the gap, consider putting the total at the top,
or have some code and a button to total
4) You simply go to select the cells you want to restrict users to. Go to
format>cells>protection and uncheck 'locked'. Now go too
tools>protection>protect sheet...and all the cells barring those you have
unlocked will be protected. The 'unlocked' ones will be selectable by
pressing the tab key.

(I can email you a sample workbook if you like)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Hello,

I want to create a form for people who use a cabin.

The columns titles are: name, number of nights, age group, $ per night, then total $'s.

The $'s per night depends on the age group. It's $10 for ages 0-12, $20

for 13-18, and $30 for 19+. I'm doing a drop down menu for the age groups.
Based on their choice, I want the $'s per night to come up automatically in
the next column.

My questions are:
1) Is it best to be doing this in Excel, or should I use Word?
2) Assuming Excel, how do I set up the $'s per night based on the drop down menu selection?
3) I'd like to have the totals at the bottom of my form, so how do I

account for the variety in rows that may be needed by various users without
having a whole bunch of blank rows at the bottom?

4) I know I can lock the spreadsheet and then allow the user to only

change appropriate cells, but is there a way to have the user just tab
through the changeable cells?

 

[Spiccoli]

Nick

AWESOME

Thanks SO much, Nick. It works very well exactly as you wrote it

I have two more additional questions..
1) Do you know if I can import this into a Word document and run it in Word?
2) I'm interested in learning more about your answer to #3, "have some code and a button to total." Do you know how I might be able to do that

Thanks again for your help

Jef


----- Nick Hodge wrote: ----

Jef

1) Excel's fin
2) Set up the dropdown list using Data>Validation>List and type the thre
type in the box, separated by commas. Then use a series of IF functions t
look at the dat
3) Total at the bottom are tricky if the list could be one or 100 item
long. You can either have the gap, consider putting the total at the top
or have some code and a button to tota
4) You simply go to select the cells you want to restrict users to. Go t
format>cells>protection and uncheck 'locked'. Now go to
tools>protection>protect sheet...and all the cells barring those you hav
unlocked will be protected. The 'unlocked' ones will be selectable b
pressing the tab key

(I can email you a sample workbook if you like

--
HT
Nick Hodg
Microsoft MVP - Exce
Southampton, Englan
(e-mail address removed)

Hello

for 13-18, and $30 for 19+. I'm doing a drop down menu for the age groups
Based on their choice, I want the $'s per night to come up automatically i
the next column

1) Is it best to be doing this in Excel, or should I use Word
2) Assuming Excel, how do I set up the $'s per night based on the dro down menu selection
3) I'd like to have the totals at the bottom of my form, so how do

account for the variety in rows that may be needed by various users withou
having a whole bunch of blank rows at the bottom

4) I know I can lock the spreadsheet and then allow the user to onl

change appropriate cells, but is there a way to have the user just ta
through the changeable cells

 

[Nick Hodge]

Jeff

1) You could embed this into word and it would work, but they would need to
know to double-click the excel portion to activate it.

2) If you want to go this route, it would need some code writing, which
would not be difficult, but we would need more detail. e.g are all the
columns necessary, are columns filled in, what is the range of guest
numbers, 1 -20, etc. It may be a little more trouble than it's worth if all
you are trying to do is put the total under the last entry if there is only
ever say up to 20 people as this would still print on a single sheet.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Nick,

AWESOME!

Thanks SO much, Nick. It works very well exactly as you wrote it.

I have two more additional questions...
1) Do you know if I can import this into a Word document and run it in Word?
2) I'm interested in learning more about your answer to #3, "have some

code and a button to total." Do you know how I might be able to do that?

 

[Spiccoli]

Hi Nick

1) I'll give the embedding a try and see what happens.

2) I'd love it if you could explain to me how to do this. You're probably right, it may be more trouble than it's worth in this case, but I can see how I might use this in other situations as well. So if you could explain it, I'd much appreciate it

Basically, I want to know how I can set up a button that causes each row, between say 10 and 30, to be hidden if the nth column's cell in that row is blank

Thanks! :

Jef



----- Nick Hodge wrote: ----

Jef

1) You could embed this into word and it would work, but they would need t
know to double-click the excel portion to activate it

2) If you want to go this route, it would need some code writing, whic
would not be difficult, but we would need more detail. e.g are all th
columns necessary, are columns filled in, what is the range of gues
numbers, 1 -20, etc. It may be a little more trouble than it's worth if al
you are trying to do is put the total under the last entry if there is onl
ever say up to 20 people as this would still print on a single sheet

--
HT
Nick Hodg
Microsoft MVP - Exce
Southampton, Englan
(e-mail address removed)

Nick
1) Do you know if I can import this into a Word document and run it i Word
2) I'm interested in learning more about your answer to #3, "have som

code and a button to total." Do you know how I might be able to do that

Thanks again for your help
Jef
Jef
1) Excel's fin

2) Set up the dropdown list using Data>Validation>List and type th thre
type in the box, separated by commas. Then use a series of I functions t
look at the dat
3) Total at the bottom are tricky if the list could be one or 10 item
long. You can either have the gap, consider putting the total at th top
or have some code and a button to tota
4) You simply go to select the cells you want to restrict users to Go t
format>cells>protection and uncheck 'locked'. Now go to
tools>protection>protect sheet...and all the cells barring those yo hav
unlocked will be protected. The 'unlocked' ones will be selectabl b
pressing the tab key

(I can email you a sample workbook if you like
--

HT
Nick Hodg
Microsoft MVP - Exce
Southampton, Englan
(e-mail address removed)

 

[Nick Hodge]

Jeff

If you enter names in column A on a sheet called sheet1. This will hide any
rows between the last one and row 20

Sub HideBlankRows()
Dim llastrow As Long
llastrow = Worksheets("sheet1").Range("A65536").End(xlUp).Row + 1
Worksheets("Sheet1").Rows(llastrow & ":20").EntireRow.Hidden = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Hi Nick,

1) I'll give the embedding a try and see what happens.

2) I'd love it if you could explain to me how to do this. You're probably

right, it may be more trouble than it's worth in this case, but I can see
how I might use this in other situations as well. So if you could explain
it, I'd much appreciate it.

Basically, I want to know how I can set up a button that causes each row,

between say 10 and 30, to be hidden if the nth column's cell in that row is
blank.

 

[Spiccoli]

Nick,

This looks great! But where do I type it all in?

I'm guessing it's VB, but I've never written anything in VB before so I don't know how to get into it. Can you tell me? (Please forgive my ignorance.)

Also, how do I tie this to a button that the user can push when s/he is ready to print?

Best Regards,

Jeff


----- Nick Hodge wrote: -----

Jeff

If you enter names in column A on a sheet called sheet1. This will hide any
rows between the last one and row 20

Sub HideBlankRows()
Dim llastrow As Long
llastrow = Worksheets("sheet1").Range("A65536").End(xlUp).Row + 1
Worksheets("Sheet1").Rows(llastrow & ":20").EntireRow.Hidden = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Hi Nick,

right, it may be more trouble than it's worth in this case, but I can see
how I might use this in other situations as well. So if you could explain
it, I'd much appreciate it.between say 10 and 30, to be hidden if the nth column's cell in that row is
blank.

Thanks!
Jeff
Jeff
1) You could embed this into word and it would work, but they would

need to
know to double-click the excel portion to activate it.

2) If you want to go this route, it would need some code writing,

which
would not be difficult, but we would need more detail. e.g are all the
columns necessary, are columns filled in, what is the range of guest
numbers, 1 -20, etc. It may be a little more trouble than it's worth if all
you are trying to do is put the total under the last entry if there is only
ever say up to 20 people as this would still print on a single sheet.

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Nick Hodge]

Jeff

I have this in a workbook if you want it email me. (And anyone else for that
matter)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Nick,

This looks great! But where do I type it all in?

I'm guessing it's VB, but I've never written anything in VB before so I

don't know how to get into it. Can you tell me? (Please forgive my
ignorance.)