excel vba- loop through each cell

[Irenerz]

Hello,

Recently I've been updating some input sheets for our department. The last
person that was here before me created it using some vba code. My vba
knowledge is very primitive at best. Please help!

I have set up an input sheet where you can enter information on the white
spaces (no fill). The other parts of the sheets are shaded a dark grey.

My vba code is suppose to look through each cell in the Range to check if
its colored white, if the cell is white it will unlock it. At the end I want
to protect the sheet and while the white cells that have been unlocked can
have information entered into it.

I have some vba code already and it seems to make sense but it doesn't work
it keeps on giving me errors.. few pointers from the experts would help.
Thanks very much!

Private Sub LockButton_Click()

ActiveWorkbook.Unprotect "password"

Application.ScreenUpdating = False

Dim ws As Worksheet, cell As Range

For Each cell In ws.Range("A:AK")

If cell.Interior.ColorIndex = 1 Then
Exit For
ElseIf cell.Interior.ColorIndex = xlNone Then
cell.Locked = False
End If

Next

For Each ws In ActiveWorkbook.Worksheets
ws.Visible = True
ws.Protect "puddles"
Next

Application.ScreenUpdating = True

Unload Me

End Sub

---

 

[Patrick Bielen]

Hi Irenerz,

I have some vba code already and it seems to make sense but it doesn't
work
it keeps on giving me errors.. few pointers from the experts would help.
ActiveWorkbook.Unprotect "password"
ws.Protect "puddles"

Well it's hard for us to say what exactly is wrong cause you are
not telling us what exactly the error is, but i think it's in the
code above. As you can see there are two different passwords
used. If that is not the problem provide us with the error you get.

Best Regards,

Patrick
MCP / SCJP

 

[Tushar Mehta]

In addition to Patrick's comment, I am not sure what role VBA plays
here. Unless you have other code that locks the cells unlocked by this
code, why not just use the GUI to set the cell property to Unlocked
(select the cells of interest, then Format | Cells... | Protection tab)
and then lock the worksheet?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jurgen De Bruyne]

For Each cell In ws.Range("A:AK")

before this line u need to assign a worksheet to "ws". U declare it as a
variable of type Worksheet, but it doesn't point to an exisiting sheet in your
workbook.
e.g.
Set ws = ActiveSheet
or
set ws = ActiveWorkbook.Sheets("Sheet1")

regards

 

[Martin Seelhofer]

Hey Irenerz

Private Sub LockButton_Click()
ActiveWorkbook.Unprotect "password"
Application.ScreenUpdating = False
Dim ws As Worksheet, cell As Range

So far so good. You have unlocked the workbook,
switched off sreenupdating and declared a variable
of type Worksheet and one of type Range. Both of
them are uninitialized.

For Each cell In ws.Range("A:AK")

Okelidokeli, here's the mistake: while you are correctly
using cell together with the For Each-type of loop (which
lets cell point to every cell in the given range, one after
another), the object variable ws is still uninitialized (points
to nowhere). This causes an error regardless of the active
workbook and/or worksheet. You should have added a
line on which you initialize ws to e.g. the active worksheet:

...
set ws = ActiveSheet
For Each cell In ws.Range("A:AK")
...

An alternative would have been to directly use ActiveSheet
in the For Each-header:

For Each cell In ActiveSheet.Range("A:AK")



Cheers,
Martin

 

[Paul Oulton]

Hi Irenerz,
It seems that you should also include the line
ws.Unprotect "puddles"
after
For Each cell In ws.Range("A:AK")

That's as well as the Set ws= etc. mentioned in the previous reply.
Regards, Paul Oulton

 

[Irenerz]

Thanks very much everyone for helping. My input sheets are actually working
correctly now!