MsgBox question

[ipbr21054]

Morning all,

I am using the code supplied below which works fine.
When the code operates i am taken to the worksheet where i currently see the message "LEADERBOARD" of which i need to click OK
What needs to be done so i dont have to click OK,i dont even need to see this message BUT i do need to see the message if the file is not found etc.

Please advise thanks.

Sub Openworkbook_Click()

    Dim xWb As Workbook
    Dim wbName As String
    On Error Resume Next
    Set xWb = Workbooks.Open("C:\Users\Ian\Desktop\REMOTES ETC\DR\HONDA SOLD ITEMS.xlsm")
    wbName = xWb.Name
    If Err.Number <> 0 Then
        MsgBox "This workbook does not exist!", vbInformation, "HONDA SOLD ITEMS"
        Err.Clear
    Else
        MsgBox "LEADERBOARD!", vbInformation, "HONDA SOLD ITEMS"
    End If
End Sub

 

[RoryA]

Remove these two lines:

    Else
        MsgBox "LEADERBOARD!", vbInformation, "HONDA SOLD ITEMS"

 

[ipbr21054]

Morning.

Removing that code gives me the same result as if i put Exit Sub after the file path.
The file opens up fine without the msgbox BUT if i move the file and run the code again i dont see a msgbox saying This workbook does not exist but a run time error 1004 instead.

 

[RoryA]

You don't need an error handler for this:

Sub Openworkbook_Click()

    Dim xWb As Workbook
    Dim sFile as String

    sfile = "C:\Users\Ian\Desktop\REMOTES ETC\DR\HONDA SOLD ITEMS.xlsm"
    if dir(sfile) <> "" then
        Set xWb = Workbooks.Open(sfile)
    else
        MsgBox "This workbook does not exist!", vbInformation, "HONDA SOLD ITEMS"
    End If
End Sub

 

[ipbr21054]

Perfect,works fine.

Thank you.

 

[ipbr21054]

Can you please advise what ive done incorrect to get 2 lines of text.

MsgBox "This workbook does not exist!" & vbNewLine "Line 2 here", vbInformation, "HONDA SOLD ITEMS"

 

[RoryA]

You're missing an ampersand:

& vbNewLine & "Line 2 here"

 

[ipbr21054]

Well spotted,many thanks....

 

[ipbr21054]

Afternoon,

Can you advise please.

I have in use the code mentioned above of which is.

Sub Openworkbook_Click()

    Dim xWb As Workbook
    Dim sFile As String


    sFile = "C:\Users\Ian\Desktop\REMOTES ETC\DR\HONDA SOLD ITEMS.xlsm"
    If Dir(sFile) <> "" Then
        Set xWb = Workbooks.Open(sFile)
    Else
        MsgBox "Honda Sold Items File Cant Be Found," & vbNewLine & "Check DR folder To Make Sure It`s There", vbInformation, "KEY REMOTE LEADER BOARD"
    End If
End Sub

I also have the code below of which i would like to also into the above but not sure how to do so.
Basically the button will copy values from one workbook & then paste to another workbook & then sort.
I must mention that its 2 separate workbooks so i might be incorrect with the path of where you see HONDA SOLD ITEMS

Sub LEADERBOARD()    '' leaderboard Macro'
    Worksheets("HONDA SHEET").Range("C1:D13").Copy Worksheets("HONDA SOLD ITEMS").Range("C2:D15")
    Worksheets("HONDA SOLD ITEMS").Range("E1:F13").Copy Worksheets("INFO").Range("C15:D27")
 
    ActiveWorkbook.Worksheets("HONDA SOLD ITEMS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("HONDA SOLD ITEMS").Sort.SortFields.Add Key:=Range("D2"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortTextAsNumbers
    With Worksheets("HONDA SOLD ITEMS").Sort
        .SetRange Range("C2:D27")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    With Worksheets("HONDA SOLD ITEMS").Range("C2:D27").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        End With
    Application.Goto Sheets("HONDA SOLD ITEMS").Range("A1")
End Sub

 

[RoryA]

Isn't that the same question you already posted here: https://www.mrexcel.com/forum/excel...s-open-results-different-way-post5099747.html ?