Hello,
I am currently running Excel 2003.
My problem is this.
I have a w/sheet that allows users to enter data into selected cells. In
other cells there are various LOOKUPS base on their input. I have created a
Button from the Forms toolbar and attached a macro to it that allows the user
to clear down the w/sheet each time prior to using the w/sheet,whilst
protecting the LOOKUP cells. In protecting the worksheet the button also
appears to be protected and not accessible to the user.
Can anyone suggest an answer to my problem
Thank you

In protecting the worksheet the button also appears to be protected and
not accessible to the user.

Very odd. I've used Forms buttons on protected sheets for many years. The
button is always clickable and its assigned macro runs.


--
Jim
"Finance Guru" wrote in message
...
| Hello,
| I am currently running Excel 2003.
| My problem is this.
| I have a w/sheet that allows users to enter data into selected cells. In
| other cells there are various LOOKUPS base on their input. I have created
a
| Button from the Forms toolbar and attached a macro to it that allows the
user
| to clear down the w/sheet each time prior to using the w/sheet,whilst
| protecting the LOOKUP cells. In protecting the worksheet the button also
| appears to be protected and not accessible to the user.
| Can anyone suggest an answer to my problem
| Thank you

Yes it is,howevwr this is a first attempt at rying this out. The button
appears to float within the w/sheet. I can,pass the mouse cursor behid the
button ( that's the best I can explain it to you.
Regards
FG

"Jim Rech" wrote:

In protecting the worksheet the button also appears to be protected and
not accessible to the user.

Very odd. I've used Forms buttons on protected sheets for many years. The
button is always clickable and its assigned macro runs.


--
Jim
"Finance Guru" wrote in message
...
| Hello,
| I am currently running Excel 2003.
| My problem is this.
| I have a w/sheet that allows users to enter data into selected cells. In
| other cells there are various LOOKUPS base on their input. I have created
a
| Button from the Forms toolbar and attached a macro to it that allows the
user
| to clear down the w/sheet each time prior to using the w/sheet,whilst
| protecting the LOOKUP cells. In protecting the worksheet the button also
| appears to be protected and not accessible to the user.
| Can anyone suggest an answer to my problem
| Thank you

Hi Jim,

Your reply set me thinking. So I did the whole exercise over again,and you
are right I can still access the button to clear down the w/sheet. How very
odd !!!

I definitely couldn't do so previously,so perhaps somewhere in all of this -
I goofed up.
Many thanks for your help on this. As always the brains out there,astound me.
If only I had enough drinks to invite you all round!
FG


"Jim Rech" wrote:

In protecting the worksheet the button also appears to be protected and
not accessible to the user.

Very odd. I've used Forms buttons on protected sheets for many years. The
button is always clickable and its assigned macro runs.


--
Jim
"Finance Guru" wrote in message
...
| Hello,
| I am currently running Excel 2003.
| My problem is this.
| I have a w/sheet that allows users to enter data into selected cells. In
| other cells there are various LOOKUPS base on their input. I have created
a
| Button from the Forms toolbar and attached a macro to it that allows the
user
| to clear down the w/sheet each time prior to using the w/sheet,whilst
| protecting the LOOKUP cells. In protecting the worksheet the button also
| appears to be protected and not accessible to the user.
| Can anyone suggest an answer to my problem
| Thank you

Hi Jim - one last question.

In compiling this w/sheet, I have grouped rows. eg Row 1 - 49 would be the
standard report. However users may require more rows of data they want to
input,so for this i have Grouped rows 51 - 60, and so on.

Having got the problem to work,Excel would allow access to expand the second
group ie 51-60 and so on.

Any thoughts on this ?
FG



"Jim Rech" wrote:

In protecting the worksheet the button also appears to be protected and
not accessible to the user.

Very odd. I've used Forms buttons on protected sheets for many years. The
button is always clickable and its assigned macro runs.


--
Jim
"Finance Guru" wrote in message
...
| Hello,
| I am currently running Excel 2003.
| My problem is this.
| I have a w/sheet that allows users to enter data into selected cells. In
| other cells there are various LOOKUPS base on their input. I have created
a
| Button from the Forms toolbar and attached a macro to it that allows the
user
| to clear down the w/sheet each time prior to using the w/sheet,whilst
| protecting the LOOKUP cells. In protecting the worksheet the button also
| appears to be protected and not accessible to the user.
| Can anyone suggest an answer to my problem
| Thank you

Having got the problem to work,Excel would allow access to expand the
second group ie 51-60 and so on.

I'm sorry but I don't follow. Can you restate the exact problem?


--
Jim
"Finance Guru" wrote in message
...
| Hi Jim - one last question.
|
| In compiling this w/sheet, I have grouped rows. eg Row 1 - 49 would be the
| standard report. However users may require more rows of data they want to
| input,so for this i have Grouped rows 51 - 60, and so on.
|
| Having got the problem to work,Excel would allow access to expand the
second
| group ie 51-60 and so on.
|
| Any thoughts on this ?
| FG
|
|
|
| "Jim Rech" wrote:
|
| In protecting the worksheet the button also appears to be protected
and
| not accessible to the user.
|
| Very odd. I've used Forms buttons on protected sheets for many years.
The
| button is always clickable and its assigned macro runs.
|
|
| --
| Jim
| "Finance Guru" wrote in message
| ...
| | Hello,
| | I am currently running Excel 2003.
| | My problem is this.
| | I have a w/sheet that allows users to enter data into selected cells.
In
| | other cells there are various LOOKUPS base on their input. I have
created
| a
| | Button from the Forms toolbar and attached a macro to it that allows
the
| user
| | to clear down the w/sheet each time prior to using the w/sheet,whilst
| | protecting the LOOKUP cells. In protecting the worksheet the button
also
| | appears to be protected and not accessible to the user.
| | Can anyone suggest an answer to my problem
| | Thank you
|
|
|

In the w/sheet there are rows 1 - 40 ungrouped,for normal data entry purposes
( in the hope that nobody will require more that 40 rows. Howeveer in the
event of more rows being required,then I have included rows 41-59 ( Grouped &
then collapsed ). Should the user need to use more than thhe 40 rows,all
they have to do is click on the expand +/- button on the left hand side of
the w/sheet,and they will have an 19 additional rows for entry.

However having protected the worksheet,the +/- ( expand / collapse ) button
becomes inaccessible to the user. They cannot expand rows 41-59.

The excel error message when you try to "attack" the +/- button, states that
you y have to unprotect the worksheet,however this is undesirable

Thanks for helping - it is appreciated
HTH
FG



"Jim Rech" wrote:

Having got the problem to work,Excel would allow access to expand the
second group ie 51-60 and so on.

I'm sorry but I don't follow. Can you restate the exact problem?


--
Jim
"Finance Guru" wrote in message
...
| Hi Jim - one last question.
|
| In compiling this w/sheet, I have grouped rows. eg Row 1 - 49 would be the
| standard report. However users may require more rows of data they want to
| input,so for this i have Grouped rows 51 - 60, and so on.
|
| Having got the problem to work,Excel would allow access to expand the
second
| group ie 51-60 and so on.
|
| Any thoughts on this ?
| FG
|
|
|
| "Jim Rech" wrote:
|
| In protecting the worksheet the button also appears to be protected
and
| not accessible to the user.
|
| Very odd. I've used Forms buttons on protected sheets for many years.
The
| button is always clickable and its assigned macro runs.
|
|
| --
| Jim
| "Finance Guru" wrote in message
| ...
| | Hello,
| | I am currently running Excel 2003.
| | My problem is this.
| | I have a w/sheet that allows users to enter data into selected cells.
In
| | other cells there are various LOOKUPS base on their input. I have
created
| a
| | Button from the Forms toolbar and attached a macro to it that allows
the
| user
| | to clear down the w/sheet each time prior to using the w/sheet,whilst
| | protecting the LOOKUP cells. In protecting the worksheet the button
also
| | appears to be protected and not accessible to the user.
| | Can anyone suggest an answer to my problem
| | Thank you
|
|
|

If you add this macro to a standard module in your workbook users should be
able to operate the outline:

Sub Auto_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect "password", True, True, True, True
End With
End Sub

Of course they have to enable macros. Enabling an outline is a setting that
is not saved with the workbook so that's why the macro enables it. Also,
the fourth True is for "UserInterFaceOnly", a necessary setting, which is
also not saved.

FYI, another approach is to skip the outline and save the wrokbook with the
rows hidden in the normal way (Format, Row, Hide). Add a "Show more rows"
button with a macro attached. That macro would have to unprotect the sheet,
unhide rows and then reprotect. You could even ask the user how many
additional rows are needed (using the InputBox function).


--
Jim
"Finance Guru" wrote in message
...
| In the w/sheet there are rows 1 - 40 ungrouped,for normal data entry
purposes
| ( in the hope that nobody will require more that 40 rows. Howeveer in the
| event of more rows being required,then I have included rows 41-59 (
Grouped &
| then collapsed ). Should the user need to use more than thhe 40 rows,all
| they have to do is click on the expand +/- button on the left hand side of
| the w/sheet,and they will have an 19 additional rows for entry.
|
| However having protected the worksheet,the +/- ( expand / collapse )
button
| becomes inaccessible to the user. They cannot expand rows 41-59.
|
| The excel error message when you try to "attack" the +/- button, states
that
| you y have to unprotect the worksheet,however this is undesirable
|
| Thanks for helping - it is appreciated
| HTH
| FG
|
|
|
| "Jim Rech" wrote:
|
| Having got the problem to work,Excel would allow access to expand the
| second group ie 51-60 and so on.
|
| I'm sorry but I don't follow. Can you restate the exact problem?
|
|
| --
| Jim
| "Finance Guru" wrote in message
| ...
| | Hi Jim - one last question.
| |
| | In compiling this w/sheet, I have grouped rows. eg Row 1 - 49 would be
the
| | standard report. However users may require more rows of data they want
to
| | input,so for this i have Grouped rows 51 - 60, and so on.
| |
| | Having got the problem to work,Excel would allow access to expand the
| second
| | group ie 51-60 and so on.
| |
| | Any thoughts on this ?
| | FG
| |
| |
| |
| | "Jim Rech" wrote:
| |
| | In protecting the worksheet the button also appears to be
protected
| and
| | not accessible to the user.
| |
| | Very odd. I've used Forms buttons on protected sheets for many
years.
| The
| | button is always clickable and its assigned macro runs.
| |
| |
| | --
| | Jim
| | "Finance Guru" wrote in
message
| | ...
| | | Hello,
| | | I am currently running Excel 2003.
| | | My problem is this.
| | | I have a w/sheet that allows users to enter data into selected
cells.
| In
| | | other cells there are various LOOKUPS base on their input. I have
| created
| | a
| | | Button from the Forms toolbar and attached a macro to it that
allows
| the
| | user
| | | to clear down the w/sheet each time prior to using the
w/sheet,whilst
| | | protecting the LOOKUP cells. In protecting the worksheet the
button
| also
| | | appears to be protected and not accessible to the user.
| | | Can anyone suggest an answer to my problem
| | | Thank you
| |
| |
| |
|
|
|

Hello Jim,

Many thanks for your solution.
Greatly appreciated
FG

"Jim Rech" wrote:

If you add this macro to a standard module in your workbook users should be
able to operate the outline:

Sub Auto_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect "password", True, True, True, True
End With
End Sub

Of course they have to enable macros. Enabling an outline is a setting that
is not saved with the workbook so that's why the macro enables it. Also,
the fourth True is for "UserInterFaceOnly", a necessary setting, which is
also not saved.

FYI, another approach is to skip the outline and save the wrokbook with the
rows hidden in the normal way (Format, Row, Hide). Add a "Show more rows"
button with a macro attached. That macro would have to unprotect the sheet,
unhide rows and then reprotect. You could even ask the user how many
additional rows are needed (using the InputBox function).


--
Jim
"Finance Guru" wrote in message
...
| In the w/sheet there are rows 1 - 40 ungrouped,for normal data entry
purposes
| ( in the hope that nobody will require more that 40 rows. Howeveer in the
| event of more rows being required,then I have included rows 41-59 (
Grouped &
| then collapsed ). Should the user need to use more than thhe 40 rows,all
| they have to do is click on the expand +/- button on the left hand side of
| the w/sheet,and they will have an 19 additional rows for entry.
|
| However having protected the worksheet,the +/- ( expand / collapse )
button
| becomes inaccessible to the user. They cannot expand rows 41-59.
|
| The excel error message when you try to "attack" the +/- button, states
that
| you y have to unprotect the worksheet,however this is undesirable
|
| Thanks for helping - it is appreciated
| HTH
| FG
|
|
|
| "Jim Rech" wrote:
|
| Having got the problem to work,Excel would allow access to expand the
| second group ie 51-60 and so on.
|
| I'm sorry but I don't follow. Can you restate the exact problem?
|
|
| --
| Jim
| "Finance Guru" wrote in message
| ...
| | Hi Jim - one last question.
| |
| | In compiling this w/sheet, I have grouped rows. eg Row 1 - 49 would be
the
| | standard report. However users may require more rows of data they want
to
| | input,so for this i have Grouped rows 51 - 60, and so on.
| |
| | Having got the problem to work,Excel would allow access to expand the
| second
| | group ie 51-60 and so on.
| |
| | Any thoughts on this ?
| | FG
| |
| |
| |
| | "Jim Rech" wrote:
| |
| | In protecting the worksheet the button also appears to be
protected
| and
| | not accessible to the user.
| |
| | Very odd. I've used Forms buttons on protected sheets for many
years.
| The
| | button is always clickable and its assigned macro runs.
| |
| |
| | --
| | Jim
| | "Finance Guru" wrote in
message
| | ...
| | | Hello,
| | | I am currently running Excel 2003.
| | | My problem is this.
| | | I have a w/sheet that allows users to enter data into selected
cells.
| In
| | | other cells there are various LOOKUPS base on their input. I have
| created
| | a
| | | Button from the Forms toolbar and attached a macro to it that
allows
| the
| | user
| | | to clear down the w/sheet each time prior to using the
w/sheet,whilst
| | | protecting the LOOKUP cells. In protecting the worksheet the
button
| also
| | | appears to be protected and not accessible to the user.
| | | Can anyone suggest an answer to my problem
| | | Thank you
| |
| |
| |
|
|
|