how to put dialog box to open a file ?

[fluffyvampirekitten]

I want a dialog box to prompt user to open MISSINGUSERNAMEDEPT file .

any ideas?
Thanks in advance



Here are my codes below:

Private Sub Simpat4_Missing_UserName_Dept()
'
' Update User Name and Dept from Missing User name & dept xlsx file
' Step 29
'
Dim MaxRowNum, RowNum As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
        
    'Activate the SimPat ws
    Sheets("SimPat").Select
    'Find the max number of row - Column M - Creation ID
    MaxRowNum = Range("M" & Rows.Count).End(xlUp).Row
    
    'Find the row with the USERNAME & DEPT - "NOT INDICATED" and use a vlookup Function
    RowNum = 1
    
    For RowNum = 1 To MaxRowNum
        If UCase(Cells(RowNum, 16)) Like "*NOT INDICATED*" Then
                                  
            'Vlookup User Name from the file MISSINGUSERNAMEDEPT.xlsx
            Range("P" & RowNum).FormulaR1C1 = "= IF(ISNA(VLOOKUP(RC[-3],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!R1:R1048576,2,0)), ""NOT INDICATED"",(VLOOKUP(RC[-3],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!R1:R1048576,2,0)))"
                                        
        End If
        
        If UCase(Cells(RowNum, 17)) Like "*NOT INDICATED*" Then
        
            'Vlookup Dept from the file MISSINGUSERNAMEDEPT.xlsx
            Range("Q" & RowNum).FormulaR1C1 = "= IF(ISNA(VLOOKUP(RC[-4],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!R1:R1048576,3,0)), ""NOT INDICATED"",(VLOOKUP(RC[-4],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!R1:R1048576,3,0)))"
        
        End If
        
            'Fill only the "NOT INDICATED" Cells
            'Don't use Autofill as it will overwrite every row
            Range(Cells(RowNum, "P"), Cells(RowNum, "Q")).Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues
        
        
        'Highlight the cells - "NOT INDICATED" in 'red' means not found in MissingUserNameDept file
        If UCase(Cells(RowNum, 16)) Like "*NOT INDICATED*" Then
            Cells(RowNum, 16).Interior.Color = RGB(255, 0, 0)
        End If
        
        If UCase(Cells(RowNum, 17)) Like "*NOT INDICATED*" Then
            Cells(RowNum, 17).Interior.Color = RGB(255, 0, 0)
        End If
    Next
                   
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
    
End Sub

 

[wrightyrx7]

You could get code to open the other workbook when this workbook is opened?

With something like

Private Sub Workbook_Open()
Workbooks.Open Filename:=ThisWorkbook.Path & "\MISSINGUSERNAMEDEPT.xlsx"
End Sub

in the code for 'ThisWorkbook'

 

[fluffyvampirekitten]

You could get code to open the other workbook when this workbook is opened?

With something like

Private Sub Workbook_Open()
Workbooks.Open Filename:=ThisWorkbook.Path & "\MISSINGUSERNAMEDEPT.xlsx"
End Sub

in the code for 'ThisWorkbook'

Do i need to declare the workbook path ?

 

[wrightyrx7]

yes, unless the 'MISSINGUSERNAMEDEPT.xlsx' file is in the same folder as the file with your code in. Then the above code will work.

 

[fluffyvampirekitten]

yes, unless the 'MISSINGUSERNAMEDEPT.xlsx' file is in the same folder as the file with your code in. Then the above code will work.

is there other way that i dont have to declare the path ?