Macro Confusion

Question

In Excel VBA we have a spreadsheet which stores information and have one of each of our sites. The workbooks are identical but each contains info for a specific sites. These are in constant use and we have too many users to use a single book.

Since an update to office 365, if a user has more than one sheet open then the scripts are writing the data to the wrong sheet. The scripts are contained in a module and it appears that excel, on rare occasions runs the script (of the same name) from the wrong workbook. Is there a simple way to make the code only run in the workbook it is contained in? One thought was if we put it in the "this workbook" container rather than in a module might have this effect.

The spreadsheet is very long but in essence this is what it does:

Private Sub cmdSave_Click()
    Set ws = Sheets("Repairs Log")
    lr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(lr, 3).Value = txtsite
    ws.Cells(lr, 4).Value = lbxblock
    ws.Cells(lr, 5).Value = txtflat
    ws.Cells(lr, 6).Value = txtroom
    ws.Cells(lr, 7).Value = txtdescription
    ws.Cells(lr, 9).Value = lbxtype
    ws.Cells(lr, 8).Value = lbxassigned.Text
End Sub

Any ideas

It sounds like your ranges are not qualified? Hard to know since you did not include any code. — braX, Oct 07 '21 at 13:01
Read carefully the provided answers of https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba. It explains the ideas how to address the correct workbook, sheet and range. — FunThomas, Oct 07 '21 at 13:04
You are correct the ranges are not qualified as we use the same code in each of the work books eg ws.Cells(lr, 3).Value = txtsite where ws is the sheet name lr the new row and txtsite a value from a form. — David Brand, Oct 07 '21 at 13:06
Most likely `ws` implicitly references the `ActiveWorkbook`, not `ThisWorkbook`. — BigBen, Oct 07 '21 at 13:07
Checked the reference, I though to restrict the ranges with thisworkbook.range but not sure if this picks up the workbook that is in use or the workbook that contains the code. As the book that contains the code has some site specific values this won't solve the problem I want to ensure that the books only run code from within them and not a sub of the same name elsewhere — David Brand, Oct 07 '21 at 13:09
Side note: please create a [reprex] so one doesn't have to guess what your code is. Most likely you've got `Set ws = Worksheets("...")`, which is problematic because it is implicitly `Set ws = ActiveWorkbook.Worksheets("...")`, and the active workbook might not be the one you want. — BigBen, Oct 07 '21 at 13:11
The spreadsheet is very long but in essence this is what it does: Private Sub cmdSave_Click() Set ws = Sheets("Repairs Log") lr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1 ws.Cells(lr, 3).Value = txtsite ws.Cells(lr, 4).Value = lbxblock ws.Cells(lr, 5).Value = txtflat ws.Cells(lr, 6).Value = txtroom ws.Cells(lr, 7).Value = txtdescription ws.Cells(lr, 9).Value = lbxtype ws.Cells(lr, 8).Value = lbxassigned.Text end sub — David Brand, Oct 07 '21 at 13:16
Please don't post code in comments. Instead [edit] your question. Also as I guessed, you've got an implicit `ActiveWorkbook` in `Set ws = Sheets("Repairs Log")`. — BigBen, Oct 07 '21 at 13:20
I think you are entirely right BigBen but how do I prevent this without hardcoding the workbook name into all the individual books. — David Brand, Oct 07 '21 at 13:21
Put `ThisWorkbook.` in front of `Sheets`? Or [use the sheet code name](https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename). — BigBen, Oct 07 '21 at 13:22

score 1 · Answer 1 · answered Oct 07 '21 at 15:04

`ThisWorkbook`

ThisWorkbook on Microsoft Docs

Option Explicit

' Being Careless
Sub AWB()
    Dim wb As Workbook: Set wb = ActiveWorkbook ' workbook you are looking at
    ' Several possibilities: could be the wrong one!
    
    ' continue...
End Sub

' Memorize this.
Sub TWB()
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' One possibility: can't be the wrong one!
    
    Dim ws As Worksheet: Set ws = wb.Worksheets("Repairs Log")
    ' continue...
End Sub

' Since it is simple enough, you can use the 'With' statemnt:
Private Sub cmdSave_Click()
    With ThisWorkbook.Worksheets("Repairs Log")
        Dim lr As Long: lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(lr, 3).Value = txtsite
        .Cells(lr, 4).Value = lbxblock
        .Cells(lr, 5).Value = txtflat
        .Cells(lr, 6).Value = txtroom
        .Cells(lr, 7).Value = txtdescription
        .Cells(lr, 9).Value = lbxtype
        .Cells(lr, 8).Value = lbxassigned.Text
    End With
End Sub

Macro Confusion

1 Answers1

ThisWorkbook

`ThisWorkbook`