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
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