Macro question...

[Kelvin Beaton]

I have this macro.
-----------------------------------------------
Sub LockEm()
Dim Wk As Worksheet
For Each Wk In ActiveWorkbook.Worksheets
Wk.Protect ("password")
Next
End Sub
-----------------------------------------------
It protects all the sheets in my workbook. I want to add an additional
feature, but can't seem to get it to work.
I want the cursor to move the "B5" on each sheet along with protecting the
sheet.
I tried this....
+++++++++++++++++++++++++++++++
Sub LockEm()
Dim Wk As Worksheet
For Each Wk In ActiveWorkbook.Worksheets

Range("B5").Select
Wk.Protect ("password")
Next
End Sub
+++++++++++++++++++++++++++++++
and this....
+++++++++++++++++++++++++++++++
Sub LockEm()
Dim Wk As Worksheet
For Each Wk In ActiveWorkbook.Worksheets

Wk.Protect ("password")
Range("B5").Select
Next
End Sub
+++++++++++++++++++++++++++++++

But neither seem to work. What am I missing?

Would someone be so kind as to help me out?

Thanks

Kelvin

 

[Jonathan Blitz]

I find that the easiet way to do things like this is to run "record macro"
and then do whatever actions I want the macro to do.
Then save the macro and see what is in there.


--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Harald Staff]

Hi Kelvin

1) You can't select anything on a not-selected sheet. You must activate each
Wk in your loop before each selection.
2) Range("B5") points to the active sheet. As stated, it has to be active
for you to select it, but for other things (like entering a formula or a
value) you must address it properly, like

Wk.Range("B5").Value = "Yo"

HTH. Best wishes Harald

 

[Kelvin Beaton]

Thanks Harald for the input. Your solution works as far as typing the text
in the same cell on all spreadsheets, but, what I what is doesn't do is
leave the focus on that cell after it goes to the next sheet.

What I'm trying to accomplish is really just for asthetic reasons. I just
want the cursor to be in the same place on each sheet when I give it out to
the users, so no matter where I left the focus the macro will move it (and
leave it there) to "B5" or what ever cell. Your solution works in that it
moves to "B5" and types the text, but if the focus had been on "B10" the
cursor returns there after typing in "B5".

If you have any other ideas, I'd like to hear them.

Kelvin

 

[Don Guillett]

You could use a sheet activate event in the ThisWorkbook module.

 

[Harald Staff]

Other ideas ????
I told you how to do it and what was initially wrong. But copu-paste-run is
probably more fun than reading and understanding, so here's a pre-digested
version:

Sub LockEm()
Dim Wk As Worksheet
Dim sC As String
sC = ActiveCell.Address
For Each Wk In ActiveWorkbook.Worksheets
Wk.Activate
Wk.Range(sC).Select
Wk.Protect ("If you have any other ideas, I'd like to hear them.")
Next
End Sub

Best wishes Harald

 

[Kelvin Beaton]

Thanks for your help!

If I had understood or known how to fix it, I wouldn't have wasted your time
or mine....

There are those of us aren't programmers but appreciate those that are.
Thanks for your help, your solution is better then I would have come up with
and is a better solution then I was anticipating.

Thanks again!

Kelvin