Retrieve Message Box content in excel cells using vba

[sanits591]

I would like to retrieve the content of the message box that appears on the screen into excel cells using VBA.

Request to help me with some suitable VBA code.

 

[sanits591]

Thanks, but how can i have the other 2 columns (time, and Keystroke) shall be there attached in the column? This is the 1st question have come to me.

Meanwhile i shall surf for what you have asked to me to convert the message box as a graphic image.

 

[Ruddles]

Yes, sorry, that's what I was trying to explain: the message box doesn't actually have text in it: any text will already have been rendered into a graphic image by the time the message box has appeared on your screen. So you can't capture the contents of the box in any form which will allow you to import the text into Excel in anything other than a graphic image, readable only by the human eye.

However, as I said before, this is to the best of my knowledge. If anyone wants to correct me, I'm quite happy for them to do that, and then I will have learned something as well.

 

[Jaafar Tribak]

Let us say any of the message box appears on the screen using some code of VBA which is not accessible, and as soon as any of the Message comes on the screen during punching data in sheet, the following action should take place:


Make a list of all those messages with the time indicated in the column against each displayed message , alongwith the keystroke in separate column which was punched due to which the message appeared.

Col A Col B Col C
This is Red 09:47 am a1
This Red & Green 09:49 am ap
Excellent 09:53 am aqwe
...
& so on

What kind of message box is it ? Is it a standard VBA Msgbox or something else like a userform ?

Does the Msgbox comes up when you finish enetring the text in a cell and press Enter or does it appear as you type in ie ( as you send the keystrokes) and before you press the Enter key ?

 

[sanits591]

1. This is a VBA msgbox.
2. It comes up after pressing enter in the cell.

 

[Jaafar Tribak]

Ok. If that is the case then we can take advantage of the worksheet Change event.

Here is a Workbook Demo to illustrate the code.


Here is the code for future reference :

1- Add a new Standard module to your project and place in it the code below :

Option Explicit

Public oTarget As Range

Private Declare Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" _
(ByVal idHook As Long, _
ByVal lpfn As Long, _
ByVal hmod As Long, _
ByVal dwThreadId As Long) As Long
 
Private Declare Function CallNextHookEx Lib "user32" _
(ByVal hHook As Long, _
ByVal nCode As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
 
Private Declare Function UnhookWindowsHookEx Lib "user32" _
(ByVal hHook As Long) As Long
 
Private Declare Function GetCurrentThreadId Lib "kernel32" _
() As Long
 
Private Declare Function GetClassName Lib "user32.dll" _
Alias "GetClassNameA" _
(ByVal hwnd As Long, _
ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long

Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function GetWindowText Lib "user32.dll" _
Alias "GetWindowTextA" _
(ByVal hwnd As Long, _
ByVal lpString As String, _
ByVal cch As Long) As Long

Private Declare Function FindWindowEx Lib "user32.dll" _
Alias "FindWindowExA" (ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
 
Private Const WH_CBT As Long = 5
Private Const GWL_HINSTANCE As Long = (-6)
Private Const HCBT_ACTIVATE = 5
Private lCBTHook  As Long


'===================='
'   Public routines. '
'===================='
Public Sub SetHook()

    lCBTHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, _
            GetAppInstance, GetCurrentThreadId)
End Sub



'===================='
'  Private routines. '
'===================='

Private Function CBTProc _
(ByVal idHook As Long, ByVal wParam As Long, _
 ByVal lParam As Long) As Long
 
    Dim sBuffer As String
    Dim lRetVal As Long
    Dim lStaticHwnd As Long
 
    Select Case idHook
    
        Case Is = HCBT_ACTIVATE
        ' Some Wnd has been activated within the excel process.
        sBuffer = Space(256)
        lRetVal = GetClassName(wParam, sBuffer, 256)
          'Is it our 'MsgBox' wnd ?
        If Left(sBuffer, lRetVal) = "#32770" Then
        'if so retrieve the Static window handle.
        lStaticHwnd = FindWindowEx(wParam, 0, "Static", vbNullString)
        'retrieve the Static text.
        sBuffer = Space(256)
        lRetVal = GetWindowText(lStaticHwnd, sBuffer, 256)
        'log the Msgbox text-time and key strokes.
        Call LogEntry(Left(sBuffer, lRetVal))
        End If
        'done with hook.
        UnhookWindowsHookEx lCBTHook
    
    End Select
    
    'Call next hook if any.
    CBTProc = CallNextHookEx _
    (lCBTHook, idHook, ByVal wParam, ByVal lParam)
 
End Function

Private Function GetAppInstance() As Long
 
    GetAppInstance = GetWindowLong _
    (FindWindow("XLMAIN", Application.Caption), GWL_HINSTANCE)
 
End Function


Private Sub LogEntry(ByVal Message As String)

        Application.EnableEvents = False
        Cells(Rows.Count, 1).End(xlUp).Offset(1) = Message
        Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = Format(Now, "hh:mm:ss")
        Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = oTarget
        Application.EnableEvents = True

End Sub

2- Place this code in the worksheet module where you are puching the data :

Option Explicit

Private Const InputCell = "F2" 'change this cell as needed.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim vMsgArray() As Variant
    
    If Target.Address = Range(InputCell).Address Then
        Set oTarget = Target
        Call SetHook
        vMsgArray = Array("hello", "Excellent", "Good", "This is Red", _
        "This Red & Green", "Hi", "stop it !!", "abcd", "1234", "bla bla bla")
        Randomize
        MsgBox vMsgArray(Int(Rnd * 10))
    End If
    
End Sub

 

[Ruddles]

Works perfectly. ray:

Copied for future use!

 

[sanits591]

Thanks! for something different put up differently. But, i feel that i need to explain my requirement a little more & in explicit manner.

There is a game (time bound - Winner is one who enters the right keystrokes in the cells) with my friend in the excel file, which is protected, and we can not view for the VBA codes also.


In the range("a1:f10"), there are cells, in which, if we enter some keystrokes (could be a combination of any chracters), then VBA message box appears on the screen, with some some texts. It comes differently dependng upon the keystrokes entered. Probably there are thousand of messages, which may be coming up due to different permutations and combinations of key strokes, which is related with some variable texts not visible anywhere and also everytime VBAmsgbox text is different, and can not be predicted that what would be the next VBAmessage.


Now my requirement is, i want to capture 3 things out of this while filling cells, in the 3 columns as i said earlier:

1. Text (from VBA Message box) that appears on the screen after entering text in each cell- In one column.

2. Time at which the box appeared on the screen - 2nd Column
3. Keystrokes that lead to the specific message box - 3rd Column


I apologize, if i was not able to explain my requirement earlier.


Thanks!

 

[Jaafar Tribak]

You can't view the code of the workbook file because it is pretected right ?

If so , you obviously can't add the code I posted to the workbook.

What excel version do you have ? xl 2003, 2007 or ... ?

The only thing I can think of is to put the code in an addin and run it from there. I think it should work fine.

Also, what are the exact columns where you want to put the data ?

One last question : What is the exact workbook name so we can refer to it from the addin ?

Please answer all the above exactly.

 

[Jaafar Tribak]

Edit :

Also, what is the worksheet name where you are entering the data ?

 

[sanits591]

Thanks! sequentially replies as:
1. Yes, this is correct
2. Excel 2010
3. Col A- Message Text from VBA message box, Col B- Time of Message box, Col C- Keystroke
4. Treasure
5. Hunt (as i remember)