Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Line break in VBA message box (MsgBox)

  1. #1
    Join Date
    19th October 2005
    Posts
    21

    Line break in VBA message box (MsgBox)

    Hi,

    How do I insert a line break in the VBA message box? (I tried ^p, but does not work).

    Besides, it is possible to make a bulleted list in VBA message box? How?

    Thanks!

    Jiri

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,505

    Re: Line break in VBA message box (MsgBox)

    Try this
    VB:
     
    Msgbox ("This is Line #1" & vbNewLine & "This is Line #2" & vbNewLine & vbNewLine & "There are two lines above this one") 
    
    
    Last edited by royUK; October 27th, 2005 at 17:18.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    19th October 2005
    Posts
    21

    Re: Line break in VBA message box (MsgBox)

    Thanks Roy, what about the dotted list?

    Thanks!

    Jiri

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Line break in VBA message box (MsgBox)

    No built in list but you can create your own.
    VB:
    Sub x() 
         
        Dim strMsg As String 
         
        strMsg = "Crude bullet list" & vbNewLine & vbnewline 
        strMsg = strMsg & " Item 1" & vbNewLine 
        strMsg = strMsg & " The dot is CHR(149)" & vbNewLine 
        strMsg = strMsg & Chr(149) & " Item 3" & vbNewLine 
        strMsg = strMsg & Chr(149) & " More Items" & vbNewLine 
        strMsg = strMsg & Chr(149) & " In my list" 
         
        MsgBox strMsg, vbInformation 
         
    End Sub 
    
    

    Cheers
    Andy


  5. #5
    Join Date
    13th May 2006
    Location
    India
    Posts
    1,026

    Re: Line break in VBA message box (MsgBox)

    I have tweaked the macro "test". i have mad many of your code statements non operable with single apostrophe in the beginning of the line.

    now you can try even between 11:00 and 11:30

    VB:
    Sub test() 
        Dim sttarget, cfind As Range, sttime 
        Worksheets("Sheet1 (3)").Activate 
        With Range("B4:B99") 
            .Clear 
            .UseStandardHeight = True 
        End With 
        sttarget = InputBox("Start Time (Must be 24hr time with Colon eg 01:30, 12:15, 23:45...)") 
        myRole = InputBox("Job Role") 
        myJob = InputBox("Job Name") 
        fttarget = InputBox("Finishing Time (Must be 24hr time with Colon eg 01:30, 12:15, 23:45...)") 
        sttime = TimeValue(sttarget) 
        Set cfind1 = Range(Range("A4"), Range("a4").End(xlDown)).Find(what:=sttime, lookat:=xlWhole) 
         '    If Not cfind Is Nothing Then
         '        cfind.Offset(0, 1).Select
         '        Do While Not IsEmpty(ActiveCell)
         '        ActiveCell.Offset(1, 0).Select
         '        Loop
         '
         '        ActiveCell = sttarget
         '        ActiveCell.Offset(1, 0).Select
         '        ActiveCell = myRole
         '        ActiveCell.Offset(1, 0).Select
         '        ActiveCell = myJob
         '    Else
         '        MsgBox "not available"
        If cfind1 Is Nothing Then 
            MsgBox "not avilable" 
            Exit Sub 
        End If 
        fttime = TimeValue(fttarget) 
        Set cfind2 = Range(Range("A4"), Range("a4").End(xlDown)).Find(what:=fttime, lookat:=xlWhole) 
         '    If Not cfind Is Nothing Then
         '        cfind.Offset(0, 1).Select
         '        Do While Not IsEmpty(ActiveCell)
         '        ActiveCell.Offset(1, 0).Select
         '        Loop
         '
         '        ActiveCell.Offset(-1, 0).Select
         '        ActiveCell = fttarget
         '    Else
        If cfind2 Is Nothing Then 
            MsgBox "not available" 
            Exit Sub 
        End If 
         'Range(ActiveCell, ActiveCell.Offset(-4, 0)).Merge
        Range(cfind1, cfind2).Offset(0, 1).Select 
        Selection.Clear 
        Selection.Merge 
        With Selection 
            .Value = WorksheetFunction.Text(sttime, "hh:mm") & Chr(10) & myRole & Chr(10) & myJob & Chr(10) & WorksheetFunction.Text(fttarget, "hh:mm") 
             
            .HorizontalAlignment = xlCenter 
            .VerticalAlignment = xlCenter 
            .Orientation = 0 
            .AddIndent = False 
            .IndentLevel = 0 
            .ShrinkToFit = False 
            .ReadingOrder = xlContext 
             '.MergeCells = True
             '.Value = WorksheetFunction.Text(sttime, "hh:mm") & Chr(10) & myRole & Chr(10) & myJob & Chr(10) & WorksheetFunction.Text(fttarget, "hh:mm")
            .EntireRow.AutoFit 
        End With 
         
    End Sub 
    
    
    those non operable code statements can be deleted. they have been kept for you to understand.
    after you park this modified macro (removing old "est") and save the file.
    you again save this file with the code modified by me above in a separate folder and then start deleting the non operable statements and also you want to modify

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 15 users browsing this thread. (0 members and 15 guests)

Possible Answers

  1. line break
    By vixenyx in forum Excel and/or Word Help
    Replies: 6
    Last Post: July 2nd, 2010, 23:23
  2. Replace Text String With New Line. Line Break.
    By chatguy in forum EXCEL HELP
    Replies: 10
    Last Post: February 2nd, 2008, 11:32
  3. Blank Line Break In Message Box
    By coolhandphil in forum EXCEL HELP
    Replies: 5
    Last Post: October 10th, 2007, 23:19
  4. Multiple Line Msgbox
    By MikeBrough in forum EXCEL HELP
    Replies: 3
    Last Post: September 21st, 2007, 18:57
  5. New Line In Msgbox
    By SerenityNetwork in forum EXCEL HELP
    Replies: 3
    Last Post: October 19th, 2006, 11:46

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno