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]

Also attaching a link to a sample file, which shall elaborate what i am looking for.

http://www.box.net/shared/8niaqbqg31kh8lmfkggm

Thanks

 

[sanits591]

An important note:The event for capturing the messagebox text is to be triggered after the display of vba messagebox.

 

[Jaafar Tribak]

If you are going to use the uploaded workbook instead of the initilal file ie : ( the one whose VB Project is protected) then there is no need to capture the MsgBox text. You can easily retrieve the MsgBox text without the need to set a Windows hook.

Your Workbook Edited.

Code in the Hunt worksheet :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim oLookUpRange As Range
    Dim sText As String
      
    If IsEntryValid(Target) Then
        Set oLookUpRange = Sheets("Data").Range("Data_Table")
        sText = BuildString(Target, oLookUpRange)
        If sText <> vbNullString Then
            Call LogEntry(Target, Sheets("CC"), sText)
        End If
    End If
  
End Sub

Private Function IsEntryValid(Target As Range) As Boolean

    Dim oInputRange As Range
    
    If Target.Cells.Count > 1 Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
        MsgBox "You can only edit one cell at a time.", vbCritical
        Exit Function
    End If
    
    Set oInputRange = Sheets("Hunt").Range("Input_Table")
    
    If Union(Target, oInputRange).Address = oInputRange.Address Then
        IsEntryValid = True
    End If

End Function

Private Function BuildString(Target As Range, oLookUpRange As Range) As String

    Dim I As Long
    Dim sKey As Variant
    Dim sText As String
    
    With Application.WorksheetFunction
        For I = 1 To Len(Target)
            sKey = IIf(IsNumeric(Mid(Target, I, 1)), Val(Mid(Target, I, 1)), Mid(Target, I, 1))
            Randomize
            On Error GoTo ErrHandler
            sText = sText + Chr(32) & _
            .VLookup(sKey, oLookUpRange, .RandBetween(2, oLookUpRange.Columns.Count), False)
            BuildString = sText
        Next
    End With
    Exit Function
    
ErrHandler:
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
    MsgBox "Char: '" & sKey & "' not found in the LookUp Table.", vbCritical
    BuildString = vbNullString

End Function



Private Sub LogEntry(Target As Range, Sh As Worksheet, Text As String)

        Application.EnableEvents = False
         With Sh.Cells(Rows.Count, 1).End(xlUp)
            .Offset(1) = Text
            .Offset(1, 1) = Format(Now, "hh:mm:ss")
            .Offset(1, 2) = Target.Address
            .Offset(1, 3) = Target
         End With
        Application.EnableEvents = True

End Sub

Note that ,in order to look up values easily, I gave the Input table in the Hunt worksheet the Range Name of Input_Table and the Data table in the Data worksheet the Range Name of Data_Table .

 

[sanits591]

Thanks a Lot! for it. But, i shall iterate it, that can we look for something which shall RETRIEVE message box text and then copy in the CC (Col A) [May be using some Hooks].

Right now, the VBA message box is coming just after the CC(Col A).

Anticipatory thanks!

 

[Jaafar Tribak]

Thanks a Lot! for it. But, i shall iterate it, that can we look for something which shall RETRIEVE message box text and then copy in the CC (Col A) [May be using some Hooks].

Right now, the VBA message box is coming just after the CC(Col A).

Anticipatory thanks!

I am not sure I understand. The CC (COL A) is updated as required. You can still add a Msgbox to the Hunt worksheet event as follows :

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim oLookUpRange As Range
    Dim sText As String
      
    If IsEntryValid(Target) Then
        Set oLookUpRange = Sheets("Data").Range("Data_Table")
        sText = BuildString(Target, oLookUpRange)
        If sText <> vbNullString Then
            [B][COLOR=Red]MsgBox sText[/COLOR][/B]
            Call LogEntry(Target, Sheets("CC"), sText)
        End If
    End If
  
End Sub

Are you going to be using the workbook example that you uploaded or the original file whose code you can't access ? If the latter then Yes you will have to use the Windows Hook but in an addin .

 

[sanits591]

Yes, i did that earlier as directed for msgbox.

But, we need to capture this sText before the value gets punched in the CC (Col A).

And the major point is we are unable to take reference from the Data sheet, as we do not know, what value shall be taking up next from the data sheet (in case of original file)

 

[Jaafar Tribak]

Yes, i did that earlier as directed for msgbox.

But, we need to capture this sText before the value gets punched in the CC (Col A).

And the major point is we are unable to take reference from the Data sheet, as we do not know, what value shall be taking up next from the data sheet (in case of original file)

Ok. I am still not clear.

Are you going to be using the original file over which you don't have control because its VB Project is protected ? If so are you happy to create an addin to run the code from ?

 

[sanits591]

Yep, shall be using the original file, and if the add-in can be made compatible with the project.

But we can wait, till i procure the original file from my friend, if it is required for exceution of the code or making some add-in (for which the coding can be seen - wishing to see how we shall be making it possible) for that.

If the original file is not required, then we can further proceed for this.

 

[Jaafar Tribak]

Yep, shall be using the original file, and if the add-in can be made compatible with the project.

But we can wait, till i procure the original file from my friend, if it is required for exceution of the code or making some add-in (for which the coding can be seen - wishing to see how we shall be making it possible) for that.

If the original file is not required, then we can further proceed for this.

Yes. If you can upload the original file so I can see its layout and what actually happens .

 

[sanits591]

Thanks! I shall get back to it, as soon as i get the original file.