MsgBox question

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
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.



Code:
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Remove these two lines:

Code:
    Else
        MsgBox "LEADERBOARD!", vbInformation, "HONDA SOLD ITEMS"
 
Upvote 0
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.
 
Upvote 0
You don't need an error handler for this:

Code:
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
 
Upvote 0
Can you please advise what ive done incorrect to get 2 lines of text.

Code:
MsgBox "This workbook does not exist!" & vbNewLine "Line 2 here", vbInformation, "HONDA SOLD ITEMS"
 
Upvote 0
You're missing an ampersand:

Rich (BB code):
& vbNewLine & "Line 2 here"
 
Upvote 0
Afternoon,

Can you advise please.

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

Code:
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


Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top