change the default size on MsgBox.

[diamantebonita]

how do i change the default size of the MsbBox so i can display a list? i know msgbox character display is limited but the list isnt long. Or is there another way I can display a list using another userform?

 

[Smitty]

Welcome to the Board!

You can use VbCrLf to create hard returns:

<font face=Calibri>    MsgBox "This is a message" _<br>            & vbCrLf & vbCrLf & _<br>        "Line 1" _<br>            & vbCrLf & _<br>        "Line 2" _<br>            & vbCrLf & _<br>        "Line 3", _<br>            vbInformation + vbOKOnly, "Message Title"<br>        </FONT>

HTH,

 

[diamantebonita]

Private Sub Workbook_Open()

Dim TodayDate As String

TodayDate = Format(Date, "Long Date")

Msg = "Hello Genese!" & vbCrLf & vbCrLf
Msg = Msg & "Today is " & TodayDate & "." & vbCrLf
Msg = Msg & "These are the bills that need to paid this week." & vbCrLf & vbCrLf


MsgBox Msg


End Sub

i copied this format from a book, but whenever I type any additional code after the last msg line, it doesnt get displayed when the msgbox pops up. Basically the last line that the msgbox displays is "These are the bills that need to be paid this week" no other code that i type after that line gets displayed.

 

[Smitty]

This works fine for me:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    <SPAN style="color:#00007F">Dim</SPAN> TodayDate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> msg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    TodayDate = Format(Date, "Long Date")<br><br>    msg = "Hello Genese!" & vbCrLf & vbCrLf<br>    msg = msg & "Today is " & TodayDate & "." & vbCrLf<br>    msg = msg & "These are the bills that need to paid this week." & vbCrLf & vbCrLf<br>    msg = msg & "Next line 1" & vbCrLf & vbCrLf<br>    msg = msg & "Next line 2" & vbCrLf & vbCrLf<br>    msg = msg & "Next line 3"<br><br>    MsgBox msg, vbInformation + vbOKOnly, "Message Title"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[diamantebonita]

Oh ok, then I think there was something wrong with the variable that I was trying to display on the additional lines.
thanks for you help!

 

[diamantebonita]

How can I put a print button in my message box? so that I can print the list that the msgbox displays.

 

[Smitty]

Unfortunately, you don't. If the list items will be changing you can always write them to a sheet and print that.

If you need to add a button to the MsgBox to do that, then you'll need to create a User Form that mimics one.

 

[Ruddles]

How can I put a print button in my message box? so that I can print the list that the msgbox displays.

You can't... but you can end the message with: "Do you want to print?" and put the buttons "Yes" and "No" on the message box.

[FONT=Fixedsys]Dim iReply As VbMsgBoxResult[/FONT]
 
[FONT=Fixedsys]iReply = MsgBox("This is a message" & vbCrLf & vbCrLf & _[/FONT]
[FONT=Fixedsys]  "Line 1" & vbCrLf & _[/FONT]
[FONT=Fixedsys]  "Line 2" & vbCrLf & _[/FONT]
[FONT=Fixedsys]  "Line 3" & vbCrLf & vbCrLf & _[/FONT]
[FONT=Fixedsys]  "Do you want to print?", _[/FONT]
[FONT=Fixedsys]  vbQuestion + vbYesNo, "Message Title")[/FONT]
 
[FONT=Fixedsys]If iReply = vbYes Then[/FONT]
[FONT=Fixedsys] ' code to do printing[/FONT]
[FONT=Fixedsys]End If[/FONT]

 

[Submeg]

This works fine for me:

Private Sub Workbook_Open()
Dim TodayDate As String
Dim msg As String

TodayDate = Format(Date, "Long Date")

msg = "Hello Genese!" & vbCrLf & vbCrLf
msg = msg & "Today is " & TodayDate & "." & vbCrLf
msg = msg & "These are the bills that need to paid this week." & vbCrLf & vbCrLf
msg = msg & "Next line 1" & vbCrLf & vbCrLf
msg = msg & "Next line 2" & vbCrLf & vbCrLf
msg = msg & "Next line 3"

MsgBox msg, vbInformation + vbOKOnly, "Message Title"

End Sub

Hi, I have tried to do this in my own code, but it doesn't expand the msgbox? I have trialed the code above, and that worked fine; but when I tried to use it on my own, it wouldn't expand the box? What am I missing?

Private Sub InsertRisk_Click()
Dim rngFound As Range
Dim rngSearch As Range
Dim NSERisk, EarthRisk, ElecClearRisk, SF6Risk, ArcContRisk, ConfineSpaceRisk, AboveLiveRisk, StageLiveRisk, LayDownRisk As String
Dim IndoorLightRisk, EmergExitRisk, FireRisk, OilContRisk, VentRisk, NoiseRisk, DrainRisk, FallRisk As String
Dim ElectroRisk, TrafficRisk, InRisk, GasRisk, DemRisk, AsbestosRisk, FenceRisk, SoilRisk, OtherRisk As String
Dim strMsg As String
Sheets("Hazard Identification").Select
Set rngSearch = Range("E:E")
Set rngFound = rngSearch.Find(What:="NON STANDARD EQUIPMENT", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    NSERisk = "N"
Else
    NSERisk = "Y"
End If

Set rngFound = rngSearch.Find(What:="EARTHING", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    EarthRisk = "N"
Else
    EarthRisk = "Y"
End If

Set rngFound = rngSearch.Find(What:="ELECTRICAL CLEARENCES", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    ElecClearRisk = "N"
Else
    ElecClearRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="SF6", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    SF6Risk = "N"
Else
    SF6Risk = "Y"
End If
Set rngFound = rngSearch.Find(What:="ARC CONTAINMENT", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    ArcContRisk = "N"
Else
    ArcContRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="CONFINED SPACE", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    ConfineSpaceRisk = "N"
Else
    ConfineSpaceRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="ABOVE LIVE", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    AboveLiveRisk = "N"
Else
    AboveLiveRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="NEAR LIVE", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    StageLiveRisk = "N"
Else
    StageLiveRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="LAY DOWN", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    LayDownRisk = "N"
Else
    LayDownRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="LIGHTING", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    IndoorLightRisk = "N"
Else
    IndoorLightRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="EMERGENCY EXIT", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    EmergExitRisk = "N"
Else
    EmergExitRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="FIRE", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    FireRisk = "N"
Else
    FireRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="OIL CONTAINMENT", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    OilContRisk = "N"
Else
    OilContRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="VENTILATION", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    VentRisk = "N"
Else
    VentRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="NOISE", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    NoiseRisk = "N"
Else
    NoiseRisk = "Y"
End If
Set rngFound = rngSearch.Find(What:="DRAINAGE", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    DrainRisk = "N"
Else
    DrainRisk = "Y"
Set rngFound = rngSearch.Find(What:="FALLS", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    FallRisk = "N"
Else
    FallRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="ELECTROCUTION", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    ElectroRisk = "N"
Else
    ElectroRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="TRAFFIC", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    TrafficRisk = "N"
Else
    TrafficRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="INHALATION", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    InRisk = "N"
Else
    InRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="TOXIC GASES", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    GasRisk = "N"
Else
    GasRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="DEMOLITION", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    DemRisk = "N"
Else
    DemRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="ASBESTOS", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    AsbestosRisk = "N"
Else
    AsbestosRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="FENCING", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    FenceRisk = "N"
Else
    FenceRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="SOIL CONTAMINATION", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    SoilRisk = "N"
Else
    SoilRisk = "Y"
    
End If
Set rngFound = rngSearch.Find(What:="OTHER", LookIn _
                :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False)
If rngFound Is Nothing Then
    OtherRisk = "N"
Else
    OtherRisk = "Y"
    
End If

End If

strMsg = "Standard Risks Already Included:" & vbCrLf
strMsg = strMsg & "NON STANDARD EQUIPMENT                           : " & NSERisk & vbCrLf
strMsg = strMsg & "EARTHING                                                        : " & EarthRisk & vbCrLf
strMsg = strMsg & "ELECTRICAL CLEARENCES                               : " & ElecClearRisk & vbCrLf
strMsg = strMsg & "SF6                                                                   : " & SF6Risk & vbCrLf
strMsg = strMsg & "ARC CONTAINMENT                                        : " & ArcContRisk & vbCrLf
strMsg = strMsg & "CONFINED SPACE                                            : " & ConfineSpaceRisk & vbCrLf
strMsg = strMsg & "WORKING ABOVE LIVE EQUIPMENT                : " & AboveLiveRisk & vbCrLf
strMsg = strMsg & "STAGING & WORK NEAR LIVE CONDUCTORS : " & StageLiveRisk & vbCrLf
strMsg = strMsg & "LAY DOWN AREA                                             : " & LayDownRisk & vbCrLf
strMsg = strMsg & "INDOOR LIGHTING                                           : " & IndoorLightRisk & vbCrLf
strMsg = strMsg & "EMERGENCY EXIT LOCATIONS                         : " & EmergExitRisk & vbCrLf
strMsg = strMsg & "FIRE/EXPLOSION                                              : " & FireRisk & vbCrLf
strMsg = strMsg & "OIL CONTAINMENT                                           : " & OilContRisk & vbCrLf
strMsg = strMsg & "VENTILATION FOR INDOOR SWITCHROOMS   : " & VentRisk & vbCrLf
strMsg = strMsg & "NOISE                                                                : " & NoiseRisk & vbCrLf
strMsg = strMsg & "DRAINAGE                                                         : " & DrainRisk & vbCrLf
strMsg = strMsg & "FALLS                                                         : " & FallRisk & vbCrLf
strMsg = strMsg & "ELECTROCUTION                                           : " & ElectroRisk & vbCrLf
strMsg = strMsg & "TRAFFIC MANAGEMENT                                : " & TrafficRisk & vbCrLf
strMsg = strMsg & "INHALATION OF FUMES/DUST                : " & InRisk & vbCrLf
strMsg = strMsg & "TOXIC GASES / VAPOURS / CHEMICALS    : " & GasRisk & vbCrLf
strMsg = strMsg & "DEMOLITION                                                 : " & DemRisk & vbCrLf
strMsg = strMsg & "ASBESTOS                                                         : " & AsbestosRisk & vbCrLf
strMsg = strMsg & "FENCING (SECURITY)                                : " & FenceRisk & vbCrLf
strMsg = strMsg & "SOIL CONTAMINATION                                 : " & SoilRisk & vbCrLf
strMsg = strMsg & "OTHER                                                         : " & OtherRisk & vbCrLf
'MsgBox (strMsg)
If MsgBox(strMsg, vbOKCancel, "Standard Risks Already Included - ") = vbCancel Then
    
    Exit Sub
End If
    
    
AddRisk_Initialize
AddRisk.Show
End Sub

 

[Smitty]

I don't see anything outwardly wrong with the Message Box code. Does it display the message?