question on a constant + loop on a range

[choubix]

hello, I have the 2 subs herebelow:


in the 1st one I'd like to use the constant fpassword instead of the password:=...

in the 2nd sub I have a problem looping correctly through the range

[VBA]
'used to locate and open the relevant workbook
Public Const fpath = "N:\BRSS\Alex\"
Public Const fname = "Copy of 02 Output.xls"
'Public Const fpassword = "password"


Sub openMyFile()

On Error Resume Next
Workbooks(fname).Activate

'If Excel cannot activate the book, then it's not open, which will
'In turn create an error not of the value 0 (no error)

If Err = 0 Then
Exit Sub 'Exit macro if no error
End If

Err.Clear 'Clear erroneous errors
Workbooks.Open fpath & fname, password:="password"

Application.Run ActiveWorkbook.Name & "!UpdatePrices"

Call ELN_Restructuring

End Sub

Sub ELN_Restructuring()
Dim rCl As Range
'Dim ws As Worksheet
Dim lColor As Long

'Set the cell color that you want to protect
lColor = 38 'pink

Sheets("R_ELN").Select

'Cycle through each worksheet in the workbook
For Each rCl In Range("G:G")
'Change colored cell to unlocked, and
'all other cells to locked
If rCl.Interior.ColorIndex = lColor Then
rCl.Locked = True
Else
rCl.Locked = False
End If
Next rCl

End Sub

[/VBA]

can someone give me a hand with that please?
I'll tweak the code within the loop later on as I need to store info in an array but only for data of a certain color in range G:G.

thanks

[Norie]

Why can't you just replace "password" with fpassword?

And as for the loop try this.
[vba]
For Each rCl In Sheets("R_ELN").Range("G:G")
[/vba]

[Bob Phillips]

[vba]

rCl.Locked = rCl.Interior.ColorIndex = lColor
[/vba]

[choubix]

my mistake: I tried to replace the whole block: password:="hhhh" by fpassword when the correct syntax is: password:=fpassword ...


nerverthelessI have a slight problem:
let's say the password is "hello": I run the macro, I get a msgbox telling the the file is open by another user and I am asked for the password again. I type "hello1" and select "read only" this time and it works (the workbook was set up that way...)

now, in my macro, if I used "hello1" directly (instead of "hello"), the amcro doesnt open the workbook at all...

is there anything I missed???