What a group! Thanks for all your previous help!

1). Can I change/edit appearance of text in drop downs? Size in particular.
Color and bold would be nice as well. I have numerous drop downs in my
workbook. Unless the size of the sheet is 100% or larger, the text in the
menu drop down is very small. Our sheets are best sized at 60% to 75%.

2). I have a workbook with about 50 tabs. All indexed. And all the sheets
are populated with data from 1 of 4 master sheets. (In same workbook) Data
is input from another computer program running on a machine in the shop, into
1 of the 4 sheets.

If I insert a row on a master sheet, the rest of the workbook is "out of
sinc". Example: If I insert a row above row 16, on the master form sheet,
all is well. BUT . . . when I go to other sheets, the formulae in all the
rows below 15 are all "off". Row 15 is fine. Row 16 has become 17. 17
becomes 18. Etc. As in . . .

A15 of sheet 2 might be . . . ='2000-Master Sheet 1'!A15 And all other
rows for column A would be the appropriate formula. But then, from there
down . . .

A16 should reference '2000-Master Sheet 1'!A16 from sheet 1. But it now
reads =''2000-Master Sheet 1'!A17 There is no row 16. 17 reads 18, etc.
And it's that way on all of the sheets.

SO . . . I either need to know how to fix this, or . . .

If someone tries to insert a row, I need a warning prompt to come up. A
comment box. Bells and whistles, etc. Saying NOT to insert rows yadda yadda.

Can I associate a comment box (or whatever) with a regular Excel menu
function like "insert Row"? Ideally, I'd like to have "insert row" unabled,
with a prompt/warning letting operators know they can't insert rows. Or can
columns be "locked" so no one can insert a new row? (Ditto for inserting
columns!)

3). I'm trying to figure out how to "protect???" certain columns. I don't
want anyone fooling around with the columns that contain formulae. And there
are a ton of them.

Actually, there are only maybe 5 to 7 colums out of 50, on only 4 sheets out
of 50 that they should be allowed to use for manually inputting data or text.


I know this is a bit long. If you'd like to maybe answer just one or two
?s, and "pass it on", that'd be great!

Thanks again! Wayne

Since I got here early, I get to grab the easy one - #3.

The easiest way to protect a small group of cells on a worksheet is to begin
by unprotecting them all. Click the empty gray square to the left of the
column labels (A B C etc) and just above the first row number. That will
select all cells on the sheet. Then use Format | Cells and go to the
[Protection] tab and clear the check next to [Locked]

added hint - if you check the box marked [Hidden] then when you protect the
sheet later the formulas in those cells won't show up in the formula bar when
the cell is clicked on. This can help reduce screen clutter, especially when
the formulas are long.

To continue ... now go back to the sheet and just choose the cells that you
want to prevent changes to and with them selected, go back to Format | Cells
and check the [Locked] option. You can choose entire rows or columns for
locking if you want to.

Final step to close the lock, so to speak, is to choose the worksheet and use
Tools | Protection | Protect Sheet
If you choose to use a password, make sure you remember it. While passwords
in Excel are easily cracked, it is at least an annoyance to have to do so.

Now - #1 - to the best of my knowledge there is no way to color or resize
text in the dropdowns.

For #2 - are the cell references on the other sheets actually as you've
shown them here, OR do they contain $ symbols as ='SomeSheet'!$A$15

If that's the case, then go to the 'other' sheets and select whole columns
or rows as necessary and use Edit | Replace to change $ to nothing (enter $
for Find, leave replace empty) and choose Replace All.

The catch/caveat on that is you need to make certain that that does not
change any formulas on those sheets that are dependent on a value being in a
fixed location on the sheet. Example: On Sheet2 you get a value from Sheet1
and want to multiply it by the value that will always be in cell A1 on
Sheet2. The formula might look like this:
='Sheet2'!$B$95 * $A$1
If you remove the $ from the $A$1, then it is no longer an absolute
reference and if you add rows at the top of the sheet or insert new columns
at the left side and THEN go back and type that constant value back into A1,
the formulas won't work because it will have changed (assuming a row insert)
from
='Sheet2'!B95 * A1 (after the edit replace operation)
to
='Sheet2'!B95 * A2 (after the row insert).


"Wayne Knazek" wrote:

What a group! Thanks for all your previous help!

1). Can I change/edit appearance of text in drop downs? Size in particular.
Color and bold would be nice as well. I have numerous drop downs in my
workbook. Unless the size of the sheet is 100% or larger, the text in the
menu drop down is very small. Our sheets are best sized at 60% to 75%.

2). I have a workbook with about 50 tabs. All indexed. And all the sheets
are populated with data from 1 of 4 master sheets. (In same workbook) Data
is input from another computer program running on a machine in the shop, into
1 of the 4 sheets.

If I insert a row on a master sheet, the rest of the workbook is "out of
sinc". Example: If I insert a row above row 16, on the master form sheet,
all is well. BUT . . . when I go to other sheets, the formulae in all the
rows below 15 are all "off". Row 15 is fine. Row 16 has become 17. 17
becomes 18. Etc. As in . . .

A15 of sheet 2 might be . . . ='2000-Master Sheet 1'!A15 And all other
rows for column A would be the appropriate formula. But then, from there
down . . .

A16 should reference '2000-Master Sheet 1'!A16 from sheet 1. But it now
reads =''2000-Master Sheet 1'!A17 There is no row 16. 17 reads 18, etc.
And it's that way on all of the sheets.

SO . . . I either need to know how to fix this, or . . .

If someone tries to insert a row, I need a warning prompt to come up. A
comment box. Bells and whistles, etc. Saying NOT to insert rows yadda yadda.

Can I associate a comment box (or whatever) with a regular Excel menu
function like "insert Row"? Ideally, I'd like to have "insert row" unabled,
with a prompt/warning letting operators know they can't insert rows. Or can
columns be "locked" so no one can insert a new row? (Ditto for inserting
columns!)

3). I'm trying to figure out how to "protect???" certain columns. I don't
want anyone fooling around with the columns that contain formulae. And there
are a ton of them.

Actually, there are only maybe 5 to 7 colums out of 50, on only 4 sheets out
of 50 that they should be allowed to use for manually inputting data or text.


I know this is a bit long. If you'd like to maybe answer just one or two
?s, and "pass it on", that'd be great!

Thanks again! Wayne

I'm reminded by Debra Dalgleish
"For a combo box from the Forms toolbar, the font size can't be changed

If you use a combo box from the Control Toolbox, you can change the font.
Right-click on the combo box and choose Properties.
Click in the font cell, and click the ... button.
Choose a font and size."

"JLatham" wrote:

Since I got here early, I get to grab the easy one - #3.

The easiest way to protect a small group of cells on a worksheet is to begin
by unprotecting them all. Click the empty gray square to the left of the
column labels (A B C etc) and just above the first row number. That will
select all cells on the sheet. Then use Format | Cells and go to the
[Protection] tab and clear the check next to [Locked]

added hint - if you check the box marked [Hidden] then when you protect the
sheet later the formulas in those cells won't show up in the formula bar when
the cell is clicked on. This can help reduce screen clutter, especially when
the formulas are long.

To continue ... now go back to the sheet and just choose the cells that you
want to prevent changes to and with them selected, go back to Format | Cells
and check the [Locked] option. You can choose entire rows or columns for
locking if you want to.

Final step to close the lock, so to speak, is to choose the worksheet and use
Tools | Protection | Protect Sheet
If you choose to use a password, make sure you remember it. While passwords
in Excel are easily cracked, it is at least an annoyance to have to do so.

Now - #1 - to the best of my knowledge there is no way to color or resize
text in the dropdowns.

For #2 - are the cell references on the other sheets actually as you've
shown them here, OR do they contain $ symbols as ='SomeSheet'!$A$15

If that's the case, then go to the 'other' sheets and select whole columns
or rows as necessary and use Edit | Replace to change $ to nothing (enter $
for Find, leave replace empty) and choose Replace All.

The catch/caveat on that is you need to make certain that that does not
change any formulas on those sheets that are dependent on a value being in a
fixed location on the sheet. Example: On Sheet2 you get a value from Sheet1
and want to multiply it by the value that will always be in cell A1 on
Sheet2. The formula might look like this:
='Sheet2'!$B$95 * $A$1
If you remove the $ from the $A$1, then it is no longer an absolute
reference and if you add rows at the top of the sheet or insert new columns
at the left side and THEN go back and type that constant value back into A1,
the formulas won't work because it will have changed (assuming a row insert)
from
='Sheet2'!B95 * A1 (after the edit replace operation)
to
='Sheet2'!B95 * A2 (after the row insert).


"Wayne Knazek" wrote:

What a group! Thanks for all your previous help!

1). Can I change/edit appearance of text in drop downs? Size in particular.
Color and bold would be nice as well. I have numerous drop downs in my
workbook. Unless the size of the sheet is 100% or larger, the text in the
menu drop down is very small. Our sheets are best sized at 60% to 75%.

2). I have a workbook with about 50 tabs. All indexed. And all the sheets
are populated with data from 1 of 4 master sheets. (In same workbook) Data
is input from another computer program running on a machine in the shop, into
1 of the 4 sheets.

If I insert a row on a master sheet, the rest of the workbook is "out of
sinc". Example: If I insert a row above row 16, on the master form sheet,
all is well. BUT . . . when I go to other sheets, the formulae in all the
rows below 15 are all "off". Row 15 is fine. Row 16 has become 17. 17
becomes 18. Etc. As in . . .

A15 of sheet 2 might be . . . ='2000-Master Sheet 1'!A15 And all other
rows for column A would be the appropriate formula. But then, from there
down . . .

A16 should reference '2000-Master Sheet 1'!A16 from sheet 1. But it now
reads =''2000-Master Sheet 1'!A17 There is no row 16. 17 reads 18, etc.
And it's that way on all of the sheets.

SO . . . I either need to know how to fix this, or . . .

If someone tries to insert a row, I need a warning prompt to come up. A
comment box. Bells and whistles, etc. Saying NOT to insert rows yadda yadda.

Can I associate a comment box (or whatever) with a regular Excel menu
function like "insert Row"? Ideally, I'd like to have "insert row" unabled,
with a prompt/warning letting operators know they can't insert rows. Or can
columns be "locked" so no one can insert a new row? (Ditto for inserting
columns!)

3). I'm trying to figure out how to "protect???" certain columns. I don't
want anyone fooling around with the columns that contain formulae. And there
are a ton of them.

Actually, there are only maybe 5 to 7 colums out of 50, on only 4 sheets out
of 50 that they should be allowed to use for manually inputting data or text.


I know this is a bit long. If you'd like to maybe answer just one or two
?s, and "pass it on", that'd be great!

Thanks again! Wayne

L & Debra . . .

Thanks for the tips and how to's. I'll work on the protect thang. I'll
save a copy and work on it. Need to be sure that if I protect a column, the
functions will still work. I just don't want a person editing the workbook
in any way, except as designed. They're allowed (and need to) enter some
data. But that's all.

Thanks again. Have a great 4th!


"JLatham" wrote:

I'm reminded by Debra Dalgleish
"For a combo box from the Forms toolbar, the font size can't be changed

If you use a combo box from the Control Toolbox, you can change the font.
Right-click on the combo box and choose Properties.
Click in the font cell, and click the ... button.
Choose a font and size."

"JLatham" wrote:

Since I got here early, I get to grab the easy one - #3.

The easiest way to protect a small group of cells on a worksheet is to begin
by unprotecting them all. Click the empty gray square to the left of the
column labels (A B C etc) and just above the first row number. That will
select all cells on the sheet. Then use Format | Cells and go to the
[Protection] tab and clear the check next to [Locked]

added hint - if you check the box marked [Hidden] then when you protect the
sheet later the formulas in those cells won't show up in the formula bar when
the cell is clicked on. This can help reduce screen clutter, especially when
the formulas are long.

To continue ... now go back to the sheet and just choose the cells that you
want to prevent changes to and with them selected, go back to Format | Cells
and check the [Locked] option. You can choose entire rows or columns for
locking if you want to.

Final step to close the lock, so to speak, is to choose the worksheet and use
Tools | Protection | Protect Sheet
If you choose to use a password, make sure you remember it. While passwords
in Excel are easily cracked, it is at least an annoyance to have to do so.

Now - #1 - to the best of my knowledge there is no way to color or resize
text in the dropdowns.

For #2 - are the cell references on the other sheets actually as you've
shown them here, OR do they contain $ symbols as ='SomeSheet'!$A$15

If that's the case, then go to the 'other' sheets and select whole columns
or rows as necessary and use Edit | Replace to change $ to nothing (enter $
for Find, leave replace empty) and choose Replace All.

The catch/caveat on that is you need to make certain that that does not
change any formulas on those sheets that are dependent on a value being in a
fixed location on the sheet. Example: On Sheet2 you get a value from Sheet1
and want to multiply it by the value that will always be in cell A1 on
Sheet2. The formula might look like this:
='Sheet2'!$B$95 * $A$1
If you remove the $ from the $A$1, then it is no longer an absolute
reference and if you add rows at the top of the sheet or insert new columns
at the left side and THEN go back and type that constant value back into A1,
the formulas won't work because it will have changed (assuming a row insert)
from
='Sheet2'!B95 * A1 (after the edit replace operation)
to
='Sheet2'!B95 * A2 (after the row insert).


"Wayne Knazek" wrote:

What a group! Thanks for all your previous help!

1). Can I change/edit appearance of text in drop downs? Size in particular.
Color and bold would be nice as well. I have numerous drop downs in my
workbook. Unless the size of the sheet is 100% or larger, the text in the
menu drop down is very small. Our sheets are best sized at 60% to 75%.

2). I have a workbook with about 50 tabs. All indexed. And all the sheets
are populated with data from 1 of 4 master sheets. (In same workbook) Data
is input from another computer program running on a machine in the shop, into
1 of the 4 sheets.

If I insert a row on a master sheet, the rest of the workbook is "out of
sinc". Example: If I insert a row above row 16, on the master form sheet,
all is well. BUT . . . when I go to other sheets, the formulae in all the
rows below 15 are all "off". Row 15 is fine. Row 16 has become 17. 17
becomes 18. Etc. As in . . .

A15 of sheet 2 might be . . . ='2000-Master Sheet 1'!A15 And all other
rows for column A would be the appropriate formula. But then, from there
down . . .

A16 should reference '2000-Master Sheet 1'!A16 from sheet 1. But it now
reads =''2000-Master Sheet 1'!A17 There is no row 16. 17 reads 18, etc.
And it's that way on all of the sheets.

SO . . . I either need to know how to fix this, or . . .

If someone tries to insert a row, I need a warning prompt to come up. A
comment box. Bells and whistles, etc. Saying NOT to insert rows yadda yadda.

Can I associate a comment box (or whatever) with a regular Excel menu
function like "insert Row"? Ideally, I'd like to have "insert row" unabled,
with a prompt/warning letting operators know they can't insert rows. Or can
columns be "locked" so no one can insert a new row? (Ditto for inserting
columns!)

3). I'm trying to figure out how to "protect???" certain columns. I don't
want anyone fooling around with the columns that contain formulae. And there
are a ton of them.

Actually, there are only maybe 5 to 7 colums out of 50, on only 4 sheets out
of 50 that they should be allowed to use for manually inputting data or text.


I know this is a bit long. If you'd like to maybe answer just one or two
?s, and "pass it on", that'd be great!

Thanks again! Wayne