Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Has anyone made a Deal or No Deal Gamebord | Excel Discussion (Misc queries) | |||
When I open Excel, workbooks open automatically. How can I stop t | Excel Discussion (Misc queries) | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Workbooks.Open closes other workbooks | Excel Programming | |||
Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming |