Show modal to False works, but now....How to specify a specific workbook in VBA

[rreifs68]

OK, this should be pretty basic (I hope), and I feel kind of silly for not knowing how do to this....

My wife busted my chops about my latest and greatest userform, because she cannot use any other spreadsheets when it is running. I did some research and found the showmodal to false in the properties, and that does seem to let me play in other sheets. The only problem is that if one does not click back on the specific spreadsheet for the userform, my macros crash. I am 100% certain that it is happening because of the way I am defining where to find my data:

Set ws = ActiveWorkbook.Worksheets("#assperwk")

The way it seems to work is that if I click on the right workbook, to make it "active", everything is perfect. How do I tell it to not look in the active workbook, but to always look in the #assperwk sheet of the BobMCCommentPrep.xlsm?

I will also be writing to other sheets in the same workbook.

Thank you in advance, and happy New Year.

BobR

[TMS]

ThisWorkbook is the workbook running the macros.

Set ws = ThisWorkbook.Worksheets("#assperwk")

Regards, TMS

[rreifs68]

Thank you TMS.

I tried BobMCCommentPrep.xlsm.worksheets... and BobMCCommentPrep.worksheets.... and both are giving me a 424 object required error. This is what I used:

Set ws = BobMCCommentPrep.Worksheets("#assperwk")

What did I do wrong?

[TMS]

That wasn't what I said. Did you try copying and pasting what I posted? "ThisWorkbook" always refers to the workbook with the macros in it. If you need to fully qualify the workbook, you'd use:

Set ws = Workbooks("BobMCCommentPrep.xlsm").Worksheets("#assperwk")

[rreifs68]

Sorry, I misunderstood.

I tried the new code, and it is back to giving me a run time error #9 - Subscript out of range, if I am not actively in the BobMCCommentPrep workbook. Anything else I might be missing? Here is the relevant part of the code:

'looks up the student data from the monthly call/mailmerge spreadsheet
Private Sub DataLookup_Click()
    Dim yellow As String
    Dim purple As String
    Dim LtGreen As String
    Dim LtBlue As String
    Dim lColumn As Long
    

    Set ws = Workbooks("BobMCCommentPrep.xlsm").Worksheets("#assperwk")
    'Set ws = BobMCCommentPrep.Worksheets("#assperwk")
    'Set ws = ActiveWorkbook.Worksheets("#assperwk")
    lColumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column
   
   
 
   
   'The following vlookups pull student data from weekly monitoring or monthly call spreadsheet
   
   contact = Me.StudentName.Value
    Me.CurrentGrade.Value = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 12, False)
    Me.PercentComp.Value = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 10, False)
    Me.LastAss.Value = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 27, False)
    Me.WeeksBehind.Value = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 21, False)
    Me.VSAComments.Value = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 26, False)
    yellow = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 22, False)
    purple = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 23, False)
    LtGreen = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 24, False)
    LtBlue = Application.WorksheetFunction.VLookup(contact, Sheets("CurrentWk").Range("A1:Bl400"), 25, False)

When I debug, it is highlighting the first VLookup.

[TMS]

Then it's not to do with ws. Which workbook is Sheets("CurrentWk") in? That's the only thing that's indexed.

[rreifs68]

Doh!

OK, Same workbook. That is only one section of my lookups. Looking at it now, I see that I am looking through about 6 different sheets. Is it ok to just do the set ws multiple times?

[rreifs68]

OK, I got it. I just needed to add the workbook to the vlookup explicitly

'looks up the student data from the monthly call/mailmerge spreadsheet
Private Sub DataLookup_Click()
    Dim yellow As String
    Dim purple As String
    Dim LtGreen As String
    Dim LtBlue As String
    Dim lColumn As Long
    
    'Dim wkb As Workbook
    'Set wkb = Workbooks.Open("BobMCCommentPrep.xlsm")
    Set ws = Workbooks("BobMCCommentPrep.xlsm").Worksheets("#assperwk")
    'Set ws = BobMCCommentPrep.Worksheets("#assperwk")
    'Set ws = ActiveWorkbook.Worksheets("#assperwk")
    lColumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column
   
   
 
   
   'The following vlookups pull student data from weekly monitoring or monthly call spreadsheet

   contact = Me.StudentName.Value
    Me.CurrentGrade.Value = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 12, False)
    Me.PercentComp.Value = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 10, False)
    Me.LastAss.Value = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 27, False)
    Me.WeeksBehind.Value = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 21, False)
    Me.VSAComments.Value = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 26, False)
    yellow = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 22, False)
    purple = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 23, False)
    LtGreen = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 24, False)
    LtBlue = Application.WorksheetFunction.VLookup(contact, Workbooks("BobMCCommentPrep.xlsm").Sheets("CurrentWk").Range("A1:Bl400"), 25, False)

Thank you very much

[TMS]

You're welcome.

[Izandol]

If I may suggest version for easier maintaining:

'looks up the student data from the monthly call/mailmerge spreadsheet
Private Sub DataLookup_Click()
   Dim yellow                      As String
   Dim purple                      As String
   Dim LtGreen                     As String
   Dim LtBlue                      As String
   Dim lColumn                     As Long
   Dim wkb                         As Workbook
   Dim wsCurrent                   As Worksheet
   Dim MatchRow

   ' set workbook object
   Set wkb = Workbooks("BobMCCommentPrep.xlsm")

   ' set worksheet variables
   Set ws = wkb.Worksheets("#assperwk")
   Set wsCurrent = wkb.Sheets("CurrentWk")

   lColumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column
   contact = Me.StudentName.Value

   ' using With block avoids repetition of worksheet object
   ' note periods before Cells calls - .Cells() instead of simple Cells()
   With wsCurrent
      'The following vlookups pull student data from weekly monitoring or monthly call spreadsheet

      ' we may perform lookup once and re-use the value
      MatchRow = Application.Match(contact, .Range("A1:A400"), 0)
      ' test for data match
      If Not IsError(MatchRow) Then
         Me.CurrentGrade.Value = .Cells(MatchRow, 12).Value
         Me.PercentComp.Value = .Cells(MatchRow, 10).Value
         Me.LastAss.Value = .Cells(MatchRow, 27).Value
         Me.WeeksBehind.Value = .Cells(MatchRow, 21).Value
         Me.VSAComments.Value = .Cells(MatchRow, 26).Value
         yellow = .Cells(MatchRow, 22).Value
         purple = .Cells(MatchRow, 23).Value
         LtGreen = .Cells(MatchRow, 24).Value
         LtBlue = .Cells(MatchRow, 25).Value
      End If
   End With

[rreifs68]

That is awesome. Thank you.

The problem with being completely self taught, is that the teacher does not ever know any better.

I figured out the vlookup thing last year and have been using the heck out of it. I will look further into the with blocks, as it appears to tighten things up considerably. I am still in brute force and ignorance mode, and hope to graduate to a little finesse next year.