PDA

View Full Version : question on a constant + loop on a range



choubix
06-02-2008, 09:40 PM
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


'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



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
06-03-2008, 06:56 AM
Why can't you just replace "password" with fpassword?:confused:

And as for the loop try this.

For Each rCl In Sheets("R_ELN").Range("G:G")

Bob Phillips
06-03-2008, 07:04 AM
rCl.Locked = rCl.Interior.ColorIndex = lColor

choubix
06-03-2008, 10:02 PM
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???