How do I deal with several workbooks that are open in the ActiveWi

[Guest]

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

 

[David Lloyd]

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.


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

 

[Dave Peterson]

You're only going to ever have one worksheet (that belongs to one workbook) in
the Activewindow.

But maybe you could just be more specific:

Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")

becomes:

Set wks = workbooks("myworkbooknamehere.xls") _
.Worksheets("Part B - Coding Details")

or if the code belongs to the workbook that has the data...

Set wks = ThisWorkbook.Worksheets("Part B - Coding Details")