Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I dim a public variable above the module 2 Public Sub :

    Public wb as Workbook

    I then set the variable wb to refer to a workbook in a procedure in module 2:

    Set wb = Workbooks(strName10)

    I then want to refer to this variable in module 3:

    wb.Worksheets("DCI data").Range("$E$6").AutoFilter Field:=5, Criteria1:=lstText

    I get the "Object variable or with block variable not set" error.

    What am I doing wrong???

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dad,

    I'd check the value of strname10 to make sure it has a valid drive:\path\filename.ext.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by RetiredGeek View Post
    I'd check the value of strname10 to make sure it has a valid drive:\path\filename.ext.
    Which you can do with

    Code:
     If Dir(strname10 ) = "" Then
     msgbox "The workbook cannot be found"
    end if
    Regards
    John



  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I think you could also get the error if the autofilter has not been added to the range.

    Could you post an example workbook with the problem?

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    strName10 is fine; I run a watch window and it's working fine there. There's also a strPath10 that goes along with it that has the full path.

    Here's the procedure in Module 2 where wb is set. This is called from Module 3:

    Public wb as Workbook

    Public Sub WhichYear

    strName = "Mgrqueryv12.xls"
    strPath = "S:\WI\ACS\Mgrquery.xls"
    strName10 = "FY10.xls"
    strPath10 = S:\\WI\ACS\FY10.xls"
    strName11 = "FY11.xls"
    strPath11 = S:\\WI\ACS\FY11.xls"

    If worksheets("FrontPage").obtnFY10.value = true then
    Application.workbooks.open(strPath10)
    For each wb in Workbooks
    If wb.name = strName11 then
    Workbooks(strName11).close false
    end if
    Next wb
    Set wb = workbooks(strName10)
    Elseif Worksheets ("FrontPage").obtnFY10.value = False ten
    Application.workbooks.open (strPath11)
    For each wb in workbooks
    If wb.name = strName10 then
    Workbooks (strName10).close false
    End if
    Next wb
    Set wb = workbooks (strName11)
    End if

    Here's the procedure in Module 3 after the call where I want to refer to wb

    Public Sub SelectEmp()

    strName = "Mgrqueryv12.xls"
    strPath = "S:\WI\ACS\Mgrquery.xls"
    strName10 = "FY10.xls"
    strPath10 = S:\\WI\ACS\FY10.xls"
    strName11 = "FY11.xls"
    strPath11 = S:\\WI\ACS\FY11.xls"

    lstText = worksheets ("QueryEmp").lstEmployee.text
    Call WhichYear
    Workbooks (strName).Activate
    wb.worksheets ("DCI data").range ("$E$6").autofilter field:=5, criteria1: = lstText
    HERE'S WHERE I GET THE ERROR MESSAGE

    I typed this in, so if there are any typos they do NOT appear in the code.
    What I'm doing is determining from the value of a radio button whether or not the user wants to view FY10 data or FY11 data. WhichYear makes that determination. In SelectEmp the user selects an employee's data to look at. This is on either FY10 or FY11 and is copied and pasted into this program.

    Hope this helps.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    wb.Worksheets("DCI data").Range("$E$6").AutoFilter field:=5, Criteria1:=lstText
    When the line of code causes the error:
    what is the variable wb?
    What is the value of lstText?
    What is value of lstEmployee and what type of variable is it?
    Does the workbook named wb have a worksheet named "DCI Data"?
    At runtime what does the sheet "DCI DAta" in wb look like?

    It is tough to recreate / troubleshoot the problem and follow what you intend without the details.Could you attach a sample workbook which demonstrates the problem and detail what must be done to recreate it?

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All of the questions you ask are good, but I dont have a problem with any of those items. I'm modifying a program that's used those items for over a year without problems. My question now is why, if I set wb to either FY10 or FY11 in one module, and if it's a public variable, why can't I refer to it successfully in another module.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dad,

    What is your workbook setup, .i.e. where are the macros {VBA} located. I noticed that you are closing the non-selected workbook. If that is the workbook containing the Public variable it will go away. Just a shot in the dark here but with out being able to run your VBA that's all we can do at this point.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    All of the questions you ask are good, but I dont have a problem with any of those items.
    I would say you MUST be having a problem with one of them since you are getting the error message...


    I'm modifying a program that's used those items for over a year without problems. My question now is why, if I set wb to either FY10 or FY11 in one module, and if it's a public variable, why can't I refer to it successfully in another module.
    And our questions are meant to try and help you understand why...

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The program is too large to upload, and I'm also not allowed to upload it intact.

    The program is for a call center, and allows management to pull up employee monitorings ("This call may be monitored for quality assurance...") by employee, team, department or date. This workbook with the code, Mgrqueryv12.xls, consists of 5 tabs: FrontPage, QueryEmp, QueryTeam, QueryDept and QueryDate. The purpose of Module 2 is to fill the various listboxes on the spreadsheets with data either from FY10 or FY11. Module 3 is the code behind the QueryEmp tab, allowing management to pull data for individual employees. Also in this module are procedures that format the data and pull in the dates. The underlying workbooks FY10.xls and FY11.xls are identical, with the exception of the data itself, and contain the underlying data that Mgrqueryv12.xls is using. The various procedures in the modules may be called from short public procedures on the Sheets themselves.


    The immediate problem I'm having is setting the public variable "wb", in Module 2 "WhichYear", and referring to it in Module 3 as indicated.

    MODULE 2

    Option Explicit
    Private strName As String
    Private strPath As String
    Private strName10 As String
    Private strPath10 As String
    Private strName11 As String
    Private strPath11 As String
    Private AllCells As Range, cell As Range
    Private NoDupeDates As New Collection
    Private NoDupeTeams As New Collection
    Private NoDupeDept As New Collection
    Private NoDupeEmps As New Collection
    Public wb As Workbook
    Private wsdate As Worksheet
    Private wsteam As Worksheet
    Private wsemp As Worksheet
    Private wsdept As Worksheet
    Private i As Integer
    Private j As Integer
    Private Swap1, Swap2, Item

    '................................................. ................
    '................................................. ................

    'Called by Public Sub obtnFY10_Click() in Sheet 10
    'THIS BUTTON SETS THE FY BUTTON VALUES, CLEARS THE LISTBOXES AND CALLS THE FILLLISTBOXES PROCEDURE
    '................................................. ................
    '................................................. ................


    Public Sub btnFY10()

    On Error GoTo HandleError

    Worksheets("FrontPage").obtnFY10.Value = True
    Worksheets("FrontPage").obtnFY11.Value = False

    Worksheets("QueryEmp").lstEmployee.Clear
    Worksheets("QueryTeam").lstTeam.Clear
    Worksheets("QueryDept").lstDept.Clear
    Worksheets("QueryDate").lstFrom.Clear
    Worksheets("QueryDate").lstTo.Clear

    Call FillListboxes

    'Error Handling Routine

    ExitHere:
    Exit Sub
    HandleError:
    MsgBox ("Error in Sub obtnFY10_Click"), vbExclamation
    Resume ExitHere

    End Sub

    '................................................. ................
    '................................................. ................
    'Called by Public Sub obtnFY11_Click() in Sheet 10
    'THIS BUTTON SETS THE FY BUTTON VALUES, CLEARS THE LISTBOXES, AND CALLS THE FILLLISTBOXES PROCEDURE
    '................................................. ................
    '................................................. ................

    Public Sub btnFY11()

    On Error GoTo HandleError
    Worksheets("FrontPage").obtnFY10.Value = False
    Worksheets("FrontPage").obtnFY11.Value = True

    Worksheets("QueryEmp").lstEmployee.Clear
    Worksheets("QueryTeam").lstTeam.Clear
    Worksheets("QueryDept").lstDept.Clear
    Worksheets("QueryDate").lstFrom.Clear
    Worksheets("QueryDate").lstTo.Clear

    Call FillListboxes

    'Error Handling Routine

    ExitHere:
    Exit Sub
    HandleError:
    MsgBox ("Error in Sub obtnFY10_Click"), vbExclamation
    Resume ExitHere

    End Sub

    '''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''''
    'THIS MODULE IS TRIGGERED BY CLICKING ONE OF THE RADIO BUTTONS
    'AND FILLS ALL OF THE LISTBOXES WITH DATA
    '''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''''''''''

    Public Sub FillListboxes()

    strName = "Mgrqueryv12.xls"
    strPath = "S:\WI\ACS\Mgrqueryv12.xls"
    strName10 = "FY10.xls"
    strPath10 = "S:\WI\ACS\FY10.xls"
    strName11 = "FY11.xls"
    strPath11 = "S:\WI\ACS\FY11.xls"

    'Activate this workbook

    Workbooks(strName).Activate

    'Calls a procedure to determine which year you're looking for

    Call WhichYear

    'Make the collections

    On Error Resume Next

    For Each cell In wsemp.Range("$c$2:$c$250")
    NoDupeEmps.Add cell.Value, CStr(cell.Value)
    Next cell

    For Each cell In wsteam.Range("$a$2:$a$19")
    NoDupeTeams.Add cell.Value, CStr(cell.Value)
    Next cell

    For Each cell In wsdept.Range("$g$6:$g$1696")
    NoDupeDept.Add cell.Value, CStr(cell.Value)
    Next cell

    For Each cell In wsdate.Range("$B$6:$B$1696")
    NoDupeDates.Add cell.Value, CStr(cell.Value)
    Next cell

    On Error GoTo 0

    ' Sort the dates and departments collections

    For i = 1 To NoDupeDates.Count - 1
    For j = i + 1 To NoDupeDates.Count
    If NoDupeDates(i) > NoDupeDates(j) Then
    Swap1 = NoDupeDates(i)
    Swap2 = NoDupeDates(j)
    NoDupeDates.Add Swap1, before:=j
    NoDupeDates.Add Swap2, before:=i
    NoDupeDates.Remove i + 1
    NoDupeDates.Remove j + 1
    End If
    Next j
    Next i

    For i = 1 To NoDupeDept.Count - 1
    For j = i + 1 To NoDupeDept.Count
    If NoDupeDept(i) > NoDupeDept(j) Then
    Swap1 = NoDupeDept(i)
    Swap2 = NoDupeDept(j)
    NoDupeDept.Add Swap1, before:=j
    NoDupeDept.Add Swap2, before:=i
    NoDupeDept.Remove i + 1
    NoDupeDept.Remove j + 1
    End If
    Next j
    Next i

    ' Add the collections to the listboxes

    Workbooks(strName).Activate

    For Each Item In NoDupeEmps
    Worksheets("QueryEmp").lstEmployee.AddItem Item
    Next Item

    For Each Item In NoDupeTeams
    Worksheets("QueryTeam").lstTeam.AddItem Item
    Next Item

    For Each Item In NoDupeDept
    Worksheets("QueryDept").lstDept.AddItem Item
    Next Item

    For Each Item In NoDupeDates
    Worksheets("QueryDate").lstFrom.AddItem Item
    Worksheets("QueryDate").lstTo.AddItem Item
    Next Item

    End Sub

    '................................................. ..........
    '................................................. ..........
    'DETERMINES WHICH YEAR'S WORKBOOK IS WANTED BASED ON
    'THE BUTTON VALUE ON FRONTPAGE, CLOSES THE OTHER WORKBOOK
    'SETS VARIABLES TO POINT TO THE CORRECT WORKSHEETS
    '................................................. ..........
    '................................................. ..........

    Public Sub WhichYear

    strName = "Mgrqueryv12.xls"
    strPath = "S:\WI\ACS\Mgrqueryv12.xls"
    strName10 = "FY10.xls"
    strPath10 = "S:\WI\ACS\FY10.xls"
    strName11 = "FY11.xls"
    strPath11 = "S:\WI\ACS\FY11.xls"

    If Worksheets("FrontPage").obtnFY10.Value = True Then
    Application.Workbooks.Open (strPath10)
    For Each wb In Workbooks
    If wb.Name = strName11 Then
    Workbooks(strName11).Close False
    End If
    Next wb
    Set wb = Workbooks(strName10)

    ElseIf Worksheets("FrontPage").obtnFY10.Value = False Then
    Application.Workbooks.Open (strPath11)
    For Each wb In Workbooks
    If wb.Name = strName10 Then
    Workbooks(strName10).Close False
    End If
    Next wb
    Set wb = Workbooks(strName11)

    End If

    Set wsemp = wb.Worksheets("Lists")
    Set wsteam = wb.Worksheets("Managers")
    Set wsdept = wb.Worksheets("DCI data")
    Set wsdate = wb.Worksheets("DCI data")

    End Sub

    /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    MODULE 3

    Option Explicit

    Private strName As String
    Private strPath As String
    Private strName10 As String
    Private strPath10 As String
    Private strName11 As String
    Private strPath11 As String
    Private wb As Workbook
    Private ws As Worksheet
    Private ListIndex As Long
    Private list As String
    Private lstText As String
    Private rng As Range
    Private i As Integer

    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
    'THIS MODULE RUNS THE INPUT IN THE NAME LISTBOX AGAINST THE
    'SPREADSHEET AND RETURNS THE DATA FOR THE SELECTED EMPLOYEE.
    'IT THEN CALLS SEVERAL SUB MODULES TO FORMAT THE RETURNED
    'DATA, AND TO FILL THE DATE LIST BOXES
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''

    Public Sub SelectEmp()

    strName = "Mgrqueryv12.xls"
    strPath = "S:\WI\ACS\Mgrqueryv12.xls"
    strName10 = "FY10.xls"
    strPath10 = "S:\WI\ACS\FY10.xls"
    strName11 = "FY11.xls"
    strPath11 = "S:\WI\ACS\FY11.xls"

    ' On Error GoTo HandleError

    Application.ScreenUpdating = True

    'Select the employee

    lstText = Worksheets("QueryEmp").lstEmployee.Text

    'Calls a procedure to determine which year you're looking for

    ' Call WhichYear

    'Run the DCIdata query on the employee from the main workbook
    Workbooks(strName).Activate
    wb.Worksheets("DCI data").Range("$E$6").AutoFilter Field:=5, Criteria1:=lstText "This is the problem line"

    'Copy the query results to the Query tab in this workbook

    ActiveCell.CurrentRegion.Copy
    Set rng = ActiveSheet.AutoFilter.Range
    Workbooks(strName).Activate
    ActiveWorkbook.Sheets("QueryEmp").Select

    'The next line will generate an error message; press YES and continue.

    rng.Resize(rng.Rows.Count).Copy Destination:=Range("$a$16")

    'Remove the autofilter setting on the DCIdata query

    ws.Activate
    ws.Select
    Selection.AutoFilter 'Turns autofilter off on DCIdata
    Selection.AutoFilter 'Turns autofilter back on on DCIdata
    Activewindow.Visible = False
    Application.ScreenUpdating = True

    'Remove the selection highlight on the query

    Workbooks(strName).Activate
    ActiveWorkbook.Sheets("QueryEmp").Select
    ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select

    'Calls a procedure to format the pasted query

    Call FormatEmpQuery

    'Calls a procedure to fill in the dates in the listboxes

    Call EmpDates

    'Error Handling Routine

    ExitHere:
    Exit Sub
    HandleError:
    MsgBox ("Error in lstEmployee_click"), vbExclamation
    Resume ExitHere

    End Sub

    '''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''
    'THIS MODULE FORMATS THE DATA RETURNED FROM THE SPREADSHEET
    '''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''

    Private Sub FormatEmpQuery()

    On Error GoTo HandleError

    Application.ScreenUpdating = False

    'Format the query results

    Columns("a:a").Select
    Selection.HorizontalAlignment = xlCenter

    'Format column B below the listbox

    Range("$b$17").Select
    For i = 1 To 4
    Selection.HorizontalAlignment = xlCenter
    ActiveCell.Offset(0, 1).Select
    Next i
    Range("$b$17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "m/d/yyyy"

    'Format column C

    Range("$c$17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.HorizontalAlignment = xlCenter

    'Format columns D & E below the listbox

    Range("$d$17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.HorizontalAlignment = xlRight
    Selection.NumberFormat = "General"
    Range("$e$17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.HorizontalAlignment = xlLeft
    Selection.NumberFormat = "General"

    'Format columns F to T below the border

    Range("$f$17").Select
    For i = 1 To 10
    Range(Selection, Selection.End(xlDown)).Select
    Selection.HorizontalAlignment = xlCenter
    ActiveCell.Offset(0, 1).Select
    Next i
    Columns("P:T").HorizontalAlignment = xlCenter
    Range("b16").Select

    Application.ScreenUpdating = True

    'Error Handling Routine

    ExitHere:
    Exit Sub
    HandleError:
    MsgBox ("Error in FormatEmpQuery"), vbExclamation
    Resume ExitHere

    End Sub

    '''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''
    'THIS MODULE SORTS AND FILLS THE DATE LIST BOXES WHEN CALLED BY
    'SUB LSTEMPLOYEE_CLICK
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''

    Private Sub EmpDates()

    Dim wsq As Worksheet
    Dim EmpMonDates As New Collection
    Dim i As Integer
    Dim j As Integer
    Dim Swap1, Swap2, Item

    On Error GoTo HandleError
    Set wsq = ActiveSheet

    Me.Activate
    lstEmpFrom.Clear
    lstEmpTo.Clear

    'Make the EmpMonDates collection

    On Error Resume Next
    For Each cell In wsq.Range("$b$17:$b$1500")
    EmpMonDates.Add cell.Value, CStr(cell.Value)
    Next cell

    'Sort the EmpMonDates collection

    For i = 1 To EmpMonDates.Count - 1
    For j = i + 1 To EmpMonDates.Count
    If EmpMonDates(i) > EmpMonDates(j) Then
    Swap1 = EmpMonDates(i)
    Swap2 = EmpMonDates(j)
    EmpMonDates.Add Swap1, before:=j
    EmpMonDates.Add Swap2, before:=i
    EmpMonDates.Remove i + j
    EmpMonDates.Remove j + i
    End If
    Next j
    Next i

    On Error GoTo 0

    'Add the dates to the list boxes

    For Each Item In EmpMonDates
    ActiveSheet.lstEmpFrom.AddItem Item
    ActiveSheet.lstEmpTo.AddItem Item
    Next Item

    'Error Handling Routine

    ExitHere:
    Exit Sub
    HandleError:
    MsgBox ("Error in EmpDates"), vbExclamation
    Resume ExitHere

    End Sub

    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    'THIS MODULE RUNS AN AUTOFILTER ON THE DATA RETURNED
    'FROM THE SPREADSHEET TO FILTER FOR THE DATES SELECTED
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''

    Public Sub SelectEmpDateRange()

    Dim c As Range

    ' On Error GoTo HandleError

    ' Evaluate the input to the two listboxes

    If CDate(lstTo) < CDate(lstFrom) Then
    MsgBox "The 'To' date should not be before the 'From' date!"
    End If

    'Run the Query on the date range on this sheet

    Range("$b$17").Select
    Selection.AutoFilter Field:=2, Criteria1:=">=" & CDate(lstEmpFrom), Visibledropdown:=False, Criteria2:="<=" & CDate(lstEmpTo)

    'Error Handling Routine

    ExitHere:
    Exit Sub
    HandleError:
    MsgBox ("Error in btnEmpByDate_Click"), vbExclamation
    Resume ExitHere

    End Sub

    '''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''
    'THIS MODULE RUNS A CHECK ON THE QUERY OUTPUT AND REMOVES ALL
    'MONITORINGS WHICH DID NOT RESULT IN A 100% CALL
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''

    Public Sub Emp100PctCalls()

    Dim intRows As Integer 'Total rows on spreadsheet
    Dim intRowsDR As Integer 'Total rows in date range
    Dim intPerf As Integer 'Total perfect rows
    Dim dblPct As Double
    Dim oRange As Range

    On Error GoTo HandleError
    Application.ScreenUpdating = False

    'Remove all rows that do not represent 100% calls

    Set oRange = Range(Range("$a$17"), Range("$a$1500").End(xlUp))
    intRows = Application.WorksheetFunction.CountA(Intersect(oRa nge, Range("a:a")))
    intRowsDR = Application.WorksheetFunction.Subtotal(2, Range("A17:A1500"))

    Range("T17").Select
    For i = 1 To intRows
    If ActiveCell = "N" Then
    Rows(ActiveCell.Row).Select
    Selection.Delete shift:=xlUp
    ActiveCell.Offset(0, 19).Select
    Else: ActiveCell.Offset(1, 0).Select
    End If
    Next i

    'Show number of perfect calls for date range

    On Error Resume Next
    intPerf = Application.WorksheetFunction.Subtotal(2, Range("A17:A1500"))
    dblPct = (intPerf / intRowsDR) * 100
    dblPct = FormatNumber(dblPct, 2, vbUseDefault, vbUseDefault, vbTrue)
    lblEmp100.Visible = True
    If intPerf = 1 Then
    lblEmp100 = "There is " & intPerf & " perfect call out of " & intRowsDR & " for this date range, or " & dblPct & " %."
    Else: lblEmp100 = "There are " & intPerf & " perfect calls out of " & intRowsDR & " for this date range, or " & dblPct & " %."
    End If
    Range("$a$16").Select
    On Error GoTo 0

    Range("$a$16").Select
    Application.ScreenUpdating = True

    'Error handling routine

    ExitHere:
    Exit Sub
    HandleError:
    MsgBox ("Error in btnEmp100_click"), vbExclamation
    Resume ExitHere

    End Sub

    '''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''
    'THIS MODULE CLEARS THE QUERY WINDOW
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''

    Public Sub ClearEmpQuery()

    On Error GoTo HandleError

    Sheets("QueryEmp").Select
    AutoFilterMode = False
    ActiveSheet.Range("a16").Select
    Range("$a$16:$t$1500").Clear
    lstEmpFrom.Clear
    lstEmpTo.Clear
    lblEmp100 = ""
    lblEmp100.Visible = False
    lstEmployee.ListIndex = -1

    'Error handling routine

    ExitHere:
    Exit Sub
    HandleError:
    MsgBox ("Error in btnQueryEmpClear_click"), vbExclamation

    Resume ExitHere

    End Sub

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dad,

    Code:
    'Run the DCIdata query on the employee from the main workbook
    Workbooks(strName).Activate
    wb.Worksheets("DCI data").Range("$E$6").AutoFilter Field:=5, Criteria1:=lstText "This is the problem line"
    Have you tested if the strName value in the code above is the same workbook as that referenced by wb?
    You could change Workbooks(strName).Activate to wb.activate to test this theory or just insert a msgbox that outputs strName and wb.name and see if they are the same.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The wb variable will either come back to strName10 or strName11 but will never come back to strName, which is the workbook Mgrqueryv12.xls where the code is running. The only reason I activate strName then is because I'm copying/pasting from the wb workbook to strName and activated it then. I commented it out and it doesn't make a difference. I could activate strName at some other time but it doesn't seem to affect the next line of code where the wb reference is.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You still have not answered the question:
    When the error occurs what is the value of the wb variable? Is the workbook you expect, a different workbook, or nothing?

    Steve

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    At the top of module 3 you have:
    [pre]Private wb As Workbook[/pre]
    which is why your error is occurring.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Rory has it in a nutshell. Well spotted Rory!

    VBA has a precedence for variable declarations which in simple terms is "the nearest visible declaration applies". A variable declared in a function or sub overrides one declared in a module and one declared in a module overrides one declared public in another module (as you have here)

    Your code overall will be hard to maintain because you are splitting related code over several modules. Personally I would never declare a variable public, it is a basic no-no of programming for too many reasons to list here. Anywhere you have to declare a variable public you should ask yourself if your module structure is OK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •