Hello Uniri,
Visual basic code that should do as you asked is included at the end
of this answer. This code is written assuming:
- Worksheet Sheets2 through Sheet6 are protected
- the 8th worksheet (I don't know its name) is unchanged
- if the user must change B1 through B10 on each sheet, those cells
will be unlocked separately from the macros
- the macro should leave the user on Sheet1 when a change is made on that sheet
If one of these assumptions are incorrect, please make a clarification
request indicating the correct assumption so I can fix the macro.
To add the macros to your worksheet, you will need to do the following:
[0] Open the worksheet (or have it already open).
[1] Use the menu
Tools -> Macro -> Visual Basic Editor
to bring up visual basic. At this point, you should have a "projects
window" in the upper right.
[2] Right click on Sheet1 to bring up a menu that includes "View
Code", select that item to bring up a code window with Sheet1 as part
of its title. Copy and paste the Worksheet_Change macro into that
window.
[3] Use the menu
Insert -> Module
to bring up another code window. Copy & paste the Lockem and Unlockem
macros into that window.
You can make any changes you find necessary at this point [see below
for suggestions]. Use the menu "Quit and Return to Microsoft Excel" to
return to the spreadsheet.
At this point, the macros should work as expected.
If you need to make some changes, here are some ideas on how to implement them.
[1] Revise the number of sheets updated - change the numbers in the
FOR loops in Lockem and Unlockem. For example, if you need six instead
of five sheets changed, change the code to read
FOR I = 2 to 7
[2] Revise the formula - change the value within the string. Note the
use of both relative (C[-1]) and absolute (C1) cell references. For
example, there is something slightly inconsistent in your question
where you say
calculate a formula that uses the value in cell A1 sheet1
and then
Cell C1 on Sheet2 = (Cell A1 on Sheet1+1) * (Cell B1 on Sheet 2)
I assumed the latter was the correct statement and the code reflects
that. If that is wrong, change
ActiveCell.FormulaR1C1 = "=RC[-1]*'Sheet1+1'!R1C1"
to read
ActiveCell.FormulaR1C1 = "=RC[-1]*'Sheet1'!R1C1"
[3] If you add an additional "active" cell on Sheet1, add conditions
similar to those currently in Worksheet_Change to determine when to
act and the actions to perform.
Please make a clarification request if any part of the answer is
incorrect or unclear. I would be glad to correct the answer as needed.
Good luck with your work.
--Maniac
The subroutine that activates with each worksheet change in Sheet1.
This must be put directly into the sheet1 module as described above.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Only activate if change is of our special value
If (Intersect(Target, Range("A1")) Is Nothing) Or _
(Target.Cells.Count > 1) Then Exit Sub
If Range("A1").Value = "" Then
Call Unlockem
Else
Call Lockem
End If
End Sub
The two subroutines that lock / unlock the cells. These can go into a
module by themselves.
Sub Lockem()
'
' Lockem Macro
' Macro recorded 6/10/2006 by Maniac
'
'
For I = 2 To 6
A$ = "Sheet" + Format(I)
Sheets(A$).Select
ActiveSheet.Unprotect
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*'Sheet1+1'!R1C1"
Range("C1:C10").Select
Selection.FillDown
Selection.Locked = True
ActiveSheet.Protect
Next I
Sheets("Sheet1").Select
End Sub
Sub Unlockem()
'
' Unlockem Macro
' Macro created 6/10/2006 by Maniac
'
'
For I = 2 To 6
A$ = "Sheet" + Format(I)
Sheets(A$).Select
ActiveSheet.Unprotect
Range("C1").Select
ActiveCell.FormulaR1C1 = ""
Range("C1:C10").Select
Selection.FillDown
Selection.Locked = False
ActiveSheet.Protect
Next I
Sheets("Sheet1").Select
End Sub |
Request for Answer Clarification by
uniri-ga
on
12 Jun 2006 20:01 PDT
Dear Maniac,
Thanks so much! The macro works well, I adjusted it to fit my
spreadsheet and it locks and unlocks the cells when needed.
I have just one problem, when I open the spreadsheet I get a message
box that asks me to reduce the security level in order to activate the
macro. The message also says that ?a digitally signed Trusted
Certificate is not attached to the macros.?
Since this spreadsheet will be used by many different users (some are
not that familiar with excel) this becomes a problem.
When I reduce the level of security on my computer to medium, I?m
being asked to enable the macros each time the spreadsheet opens. This
is one other thing I would like to avoid.
What I?m trying to accomplish is that the user will not get any
messages regarding the macros. Is that possible?
Thanks again!
|
Clarification of Answer by
maniac-ga
on
13 Jun 2006 05:23 PDT
Hello Uniri,
The changes to macro security in Excel (as well as Word and
Powerpoint) were done to stop the spread of macro viruses a few years
ago. You've already found the primary work around I use (to set your
security level to medium).
There is however an alternative, which is to create your own security
certificiate and have yourself as a "publisher" be trusted on all the
systems you administer. I haven't worked through this myself, but
there appear to be several good explanations of this process at the
following sites. Note that adding a certificate will also help confirm
the macro has not been modified.
A user's group with several good links to programs and the "how to"
steps to follow at
http://www.excelsig.org/Tips/0409_digital_certificates.htm
some of these are low or no cost. One (using a Verisign certificate)
does cost some money but may be an alternative you want to pursue.
Some technical references at Microsoft:
http://support.microsoft.com/default.aspx?scid=kb;en-us;308983
http://support.microsoft.com/support/kb/articles/Q206/6/37.ASP
http://support.microsoft.com/?kbid=262876
http://office.microsoft.com/assistance/9798/signturs.aspx
An explanation of the process at Verisign
http://www.verisign.com/static/030996.pdf
There are several other references as well. Search with a phrase such as
excel macro digitally signed trusted certificate
excel macro digitally signed trusted certificate site:microsoft.com
to find out more information.
Good luck.
--Maniac
|
Request for Answer Clarification by
uniri-ga
on
13 Jun 2006 20:28 PDT
Dear Maniac,
Thank you very much for the information. I will look into this.
Meanwhile I have notice a different problem that you may be able to fix:
Once the macro is activated the cursor is placed on the range C1:C10
on each of sheets 2 through 6 (regardless of if those cells are locked
or not).
Is it possible to modify the macro so that the cursor will be located
on a certain different cell on those sheets (2 through 6) - for
example cell D5? And also on a specific cell on sheet 1, for example
F6.
I appreciate your help very much.
Thanks you!!!
|
Clarification of Answer by
maniac-ga
on
14 Jun 2006 06:48 PDT
Hello Uniri,
It is straight forward to change the current selection in a sheet. The
code has a few examples of this already or you could find it through
the Visual Basic help (search for select or selection). The code for
the specific cases you've asked for follow.
> Is it possible to modify the macro so that the cursor will be located
> on a certain different cell on those sheets (2 through 6) - for
> example cell D5?
Before the Next I statement in Lockem and Unlockem, add
Range("D5").Select
> And also on a specific cell on sheet 1, for example F6.
Before the End Sub statement in Worksheet_Change, add
Range("F6").Select
[Sheet1 was already selected as the last action in both Lockem and
Unlockem. This statement could go into those two places as well.]
As a side note, I also find the "object browser" in Visual Basic to be
extremely helpful in finding out how to do things in VB. When in
Visual Basic, use the menu
View -> Object Browser
to bring it up. Enter a phrase in the box in the upper left to bring
up a list of matching items. When I did this with
Select
it brought up a pretty extensive list of "select" and "selection"
items. You can then scroll down to
Excel | Range | Select
and select that line. The lower part of the window will show the other
functions and properties of a Range. At this point you can press the
question mark in the upper right (?) to go directly to the help on the
selected item. On my system, it gave me a brief explanation of a range
selection, links to examples, other references, and refers to an
alternative named "Activate".
I hope this helps.
--Maniac
|
Request for Answer Clarification by
uniri-ga
on
14 Jun 2006 20:03 PDT
Thank you so much Maniac! You've provided a great help.
I'll be sure to try the object browser in the future, it sounds very useful.
|
Clarification of Answer by
maniac-ga
on
15 Jun 2006 04:37 PDT
Hello Uniri,
Thanks for the kind words and good luck with your work.
--Maniac
|
,
0 Comments
)