Jacqui:
You do need to explicitly set your workbook. For example:
Dim wkb As Workbook
Set wkb = Workbooks("TestTarget.xls")
Set wks = wkb.Worksheets("Part B - Coding Details")
or similarly:
Dim wkb As Workbook
Set wkb = Workbooks("TestTarget.xls")
wkb.Activate
Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com
This response is supplied "as is" without any representations or warranties.
"Jacqui" wrote in message
...
Can anyone help. The following code falls over if I have other excel files
open in the activewindow. I used to code quite regularly 18 months ago and
I
seem to remember there was something to cater for this but I've forgotten
how. Can anyone remind me. Is it that I need to explicitly select my
workbook? See syntax below.
Thanks
Jacqui
Sub Qualifiers_Check()
Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")
With wks
Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
Set myRng = myRng.Resize(myRng.Count - 1)
For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 5)
If Application.CountA(myRngToCheck) < myRngToCheck.Cells.Count
Then
Beep
Msgbox "You have not supplied all the relevant information
for this Segment type in Row " _
& myCell.Row & " on the Coding Details Sheet -
PLEASE ENTER ALL DETAILS" _
, 48, "Change Request Form Error Checks"
myCell.Select
Exit For
End If
End If
Next myCell
End With
End Sub