when i use msgbox in VBA, the msgbox can only hold up to 1024 chars in the string..
What if the string is more than 1024 chars? Is there a way to display a message box that has more than 1024 chars?
I have a string that contains approx 3000 chars.
when i use msgbox in VBA, the msgbox can only hold up to 1024 chars in the string..
What if the string is more than 1024 chars? Is there a way to display a message box that has more than 1024 chars?
I have a string that contains approx 3000 chars.
Why not use a simple userform?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I like to use the Script Popup for long messages when I don't feel like using a userform. Also the timeout can be handy
I put a wrapper around it to (IMHO) make it easier. Also added the ability to use ^ and ` for a CR and 'sort of' a tab
The Enum's are probably not required, but I like that they act like the Intellisense of VBA and prompt me, instead of just wanting a number
[vba]
Option Explicit
Option Private Module
Public Enum cMsgButton
cOkOnly = vbOKOnly
cOkCancel = vbOKCancel
cAbortRetryignore = vbAbortRetryIgnore
cYesNoCancel = vbYesNoCancel
cYesNo = vbYesNo
cRetryCancel = vbRetryCancel
End Enum
Public Enum cMsgIcon
cInformation = vbInformation
cCritical = vbCritical
cQuestion = vbQuestion
cExclamation = vbExclamation
End Enum
Public Enum cMsgDefault
cButton1 = vbDefaultButton1
cButton2 = vbDefaultButton2
cButton3 = vbDefaultButton3
End Enum
Public Enum cResponse
cNoResponse = -1
cOK = vbOK
cCancel = vbCancel
cAbort = vbAbort
cRetry = vbRetry
cIgnore = vbIgnore
cyes = vbYes
cNo = vbNo
End Enum
Public Function DisplayMessage(MsgText As String, _
Optional MsgTitle As String = "", _
Optional MsgIcon As cMsgIcon = cInformation, _
Optional MsgButtons As cMsgButton = cOkOnly, _
Optional NumSeconds As Long = -1, _
Optional MsgButtonsDefault As cMsgDefault = cButton1) _
As cResponse
Dim sMsg As String, sTitle As String
Dim cUser As cResponse
Dim objShell As Object
With Application.WorksheetFunction
'replace ^ in a string with Carrage Return = chr(10)
sMsg = .Substitute(MsgText, "^", vbCrLf)
'replace ` in a string with a CR and 4 spaces
sMsg = .Substitute(sMsg, "`", vbCrLf & " ")
End With
If MsgTitle = "" Then
sTitle = ThisWorkbook.FullName
Else
sTitle = MsgTitle
End If
DisplayMessage = CreateObject("WScript.Shell").Popup(sMsg, NumSeconds, _
sTitle, MsgIcon + MsgButtons + MsgButtonsDefault)
Set objShell = Nothing
End Function
Sub drv()
Dim cAns As cResponse
Dim s As String
s = "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv wxyzabcdefghijklmnopqrstuvwxyz^^abcdefghijklmnopqrstuvwxyz^"
s = s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s
MsgBox Len(s)
cAns = DisplayMessage(s, , cExclamation, cOkOnly, 5, cButton1)
End Sub
[/vba]
Paul