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

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

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

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

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

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



Jacqui wrote:

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