Recreating scripts to include ThisWorkbook with all ranges and worksheet calls.

[la333]

I have created a directory and it works great until you have it open with other documents... So I need to limit it to only this workbook but with the different types of script below I'm not sure how... It's not as simple as just placing ThisWorkbook in front of it... Any assistance on getting the following scripts to only pull from within the directory workbook would be appreciated... I'm made the text bold on the ones I don't understand how to recreate to include ThisWorkbook. Theres A LOT more code than this, I didn't post duplicate types of the script and once I understand, I can go back and edit all the others.

Thank you.

Private Sub Userform_Initialize()
Me.cboSelect.List = [B][I][U]WorksheetFunction.Transpose(Sheet1.Range("B8:K8"))[/U][/I][/B]
Call cmdListAll_Click
End Sub

Private Sub cmdPrint_Click()




ActiveSheet.Unprotect Password:="Secret"


[U][I][B]    Sheets("PrintData").Select[/B][/I][/U]


    [U][I][B]Range("$A$3:$I$2000")[/B][/I][/U].AutoFilter Field:=1, Criteria1:="<>0", _
        Operator:=xlAnd
           
    Application.Dialogs(xlDialogPrint).Show
    
ActiveSheet.Protect "Secret", _
        UserInterFaceOnly:=True




End Sub

Private Sub cmdContact_Click()
        On Error GoTo errHandler:
[U][I][B]        Set DataSH = Sheet1[/B][/I][/U]
[U][I][B]            DataSH.Range("O8")[/B][/I][/U] = Me.cboSelect.Value
        Dim Ary As Variant
        Ary = Array("NAME", "DEPARTMENT", "TITLE", "UNIT", "SHIFT", "SUPERVISOR")
        If UBound(Filter(Ary, DataSH.Range("O8").Value, True, vbTextCompare)) >= 0 Then
[U][I][B]           DataSH.Range("O9") [/B][/I][/U]= "*" & Me.txtSearch.Text & "*"
        Else
[U][I][B]           DataSH.Range("O9")[/B][/I][/U] = Me.txtSearch.Text
        End If
[U][I][B]                DataSH.Range("B8").[/B][/I][/U]CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
                "phonelist!Criteria"), CopyToRange:=Range("phonelist!Extract"), Unique:= _
                False
                ListBox1.RowSource = Sheet1.Range("outdata").Address(external:=True)


Exit Sub
errHandler:
MsgBox "There was an error. Please check your entries and try again."
End Sub

Private Sub cmdClose_Click()
[U][I][B]Sheets("Interface").Select[/B][/I][/U]
Unload Me
ActiveWorkbook.Close SaveChanges:=False
Application.Quit
End Sub

Private Sub cmdDelete_Click()
On Error GoTo cmdDelete_Click_Error
If txtName = "" Then
Call MsgBox("Double click the contact so it can be deleted", vbInformation, "Delete Contact")
Exit Sub
End If


Select Case MsgBox("You are about to delete a contact." _
& vbCrLf & "Do you want to proceed?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Are you sure about this")
Case vbYes
Case vbNo
Exit Sub
End Select


[U][I][B]'Sheet1.Range("a1") =[/B][/I][/U] txtID.Value
Set findvalue = Sheet1.Range("K8:K10000").Find(What:=Me.txtID, LookIn:=xlValues)
findvalue.Value = ""
findvalue.Offset(0, -1).Value = ""
findvalue.Offset(0, -2).Value = ""
findvalue.Offset(0, -3).Value = ""
findvalue.Offset(0, -4).Value = ""
findvalue.Offset(0, -5).Value = ""
findvalue.Offset(0, -6).Value = ""
findvalue.Offset(0, -7).Value = ""
findvalue.Offset(0, -8).Value = ""
findvalue.Offset(0, -9).Value = ""
ClearList
SortIt
On Error GoTo 0
Exit Sub
'if error occurs then show me exactly where the error occurs
cmdDelete_Click_Error:


MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdDelete_Click of Form PhoneList"
End Sub

Sub sortNameAZ_Click()
[U][I][B]Worksheets("phonelist").Activate[/B][/I][/U]
On Error GoTo sortNameAZ_Click_Error


[U][I][B]    Range("Q8:Z8").Select[/B][/I][/U]
[U][I][B]    Range("Z8").Activate[/B][/I][/U]
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("phonelist").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("phonelist").AutoFilter.Sort.SortFields.Add Key:= _
[U][I][B]        Range("Q8")[/B][/I][/U], SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("phonelist").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
sortNameAZ_Click_Error:
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("phonelist").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("phonelist").AutoFilter.Sort.SortFields.Add Key:= _
[U][I][B]        Range("Q8"),[/B][/I][/U] SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("phonelist").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

Private Sub cmdEdit_Click()
'error handler
    On Error GoTo cmdEdit_Click_Error
'check that there is data to edit
If Me.txtID = "" Then
Call MsgBox("The fields are not complete", vbInformation, "Edit Contact")
Exit Sub
End If


Select Case MsgBox("You are about to edit a contact." _
& vbCrLf & "Do you want to proceed?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Are you sure about this")
Case vbYes
Case vbNo
Exit Sub
End Select


[U][I][B]Set findvalue = Sheet1.Range("K8:K10000")[/B][/I][/U].Find(What:=Me.txtID, LookIn:=xlValues)
'findvalue.Value = Me.txtID "we do not want to edit the ID"
findvalue.Offset(0, -1).Value = Me.txtSupervisor.Value
findvalue.Offset(0, -2).Value = Me.txtShift.Value
findvalue.Offset(0, -3).Value = Me.txtRoom.Value
findvalue.Offset(0, -4).Value = Me.txtBuilding.Value
findvalue.Offset(0, -5).Value = Me.txtUnit.Value
findvalue.Offset(0, -6).Value = Me.txtTitle.Value
findvalue.Offset(0, -7).Value = Me.txtDepartment.Value
findvalue.Offset(0, -8).Value = Me.txtExtension.Value
findvalue.Offset(0, -9).Value = Me.txtName.Value


Call MsgBox("The contact has been updated", vbInformation, "Edit Contact")
ThisWorkbook.Save


'reset error


    On Error GoTo 0
    Exit Sub
'if error occurs then show me exactly where the error occurs
cmdEdit_Click_Error:


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdEdit_Click of Form PhoneListAdmin"


End Sub

 

[davesexcel]

Normally when you use "ThisWorkbook", it would be for the workbook that is running the code. If you are opening a workbook, with code, that workbook becomes the "ActiveWorkbook", "ThisWorkbook" still remains the workbook you started the code with.

 

[la333]

Normally when you use "ThisWorkbook", it would be for the workbook that is running the code. If you are opening a workbook, with code, that workbook becomes the "ActiveWorkbook", "ThisWorkbook" still remains the workbook you started the code with.

My issue is, I only want this code to work with and refer to this workbook. If I open another excel file, it seems to break my code and it won't work at all anymore until I close the other excel file(s). So I thought I needed to modify my code to only refer to this workbook or I guess activeworkbook.. I'm just not sure how to modify my code to do so.

 

[davesexcel]

You would have to set the workbook for each code.

 

[Fluff]

As long as the "directory workbook" is the active workbook, your code should be ok.