Using ActiveCell to display cell value in MsgBox

[Darren_workforce]

Hello,

Is it possible with VBA to generate a Message Box that displays the cell data anytime a cell is clicked on without having to re-run the macro each time? I know you have to click on 'OK' to clear the message box but then after that, when the mouse is clicked into a different cell, can the cell data automatically generate within a new MsgBox? Or is that asking too much of VBA?

Regarding the actual Msg Box popup, is it able to generate the MsgBox without the 'OK' button? Allow the MsgBox to appear but then automatically after XX seconds, the MsgBox would just go away.

MsgBox ActiveCell.Text

EDIT:
I found this on the MS site but am not certain how to reference it to an active cell so the data would automatically be displayed.

CreateObject("WScript.Shell").PopUp "Your Message", 5

 

[Darren_workforce]

Oh I think I found something helpful and inserted into the worksheet I need it in. But can it be linked to my second question regarding the time to display and automatically disappearing after said time?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With ActiveCell
        MsgBox .Value & "/" & .Offset(, 1) & "/" & .Offset(, 2)
    End With
End Sub

 

[Joe4]

I cannot really help with the Shell script, but I think a "Worksheet_SelectionChange" event procedure, which is triggered any time a cell is selected, would be better than the "Worksheet_BeforeDoubleClick" event for what you want to do.

 

[Darren_workforce]

I cannot really help with the Shell script, but I think a "Worksheet_SelectionChange" event procedure, which is triggered any time a cell is selected, would be better than the "Worksheet_BeforeDoubleClick" event for what you want to do.

I attempted to change it and rerun. Now it's giving me the following popup: Compile error: Procedure declaration does not match description of event or procedure having the same name.

Nothing has the same name so that's not correct. Not sure what the issue would be.

 

[Tetra201]

Maybe this (placed in the worksheet code section):

Sub Worksheet_SelectionChange(ByVal Target As Range)
    CreateObject("WScript.Shell").PopUp Target(1).Value, 2, "Title", 4096
End Sub

If you want to have no OK button, you would need to use a MsgBox-like userform.

 

[Darren_workforce]

Maybe this (placed in the worksheet code section):

Sub Worksheet_SelectionChange(ByVal Target As Range)
    CreateObject("WScript.Shell").PopUp Target(1).Value, 2, "Title", 4096
End Sub

If you want to have no OK button, you would need to use a MsgBox-like userform.

OK that seemed to do it. One final inquiry: instead of applying the Worksheet_SelectionChange to the entire worksheet, how would you restrict it to only activate within a certain range?

 

[Tetra201]

Restricted to range A1:C3

Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, [A1:C3]) Is Nothing Then _
    CreateObject("WScript.Shell").PopUp Target(1).Value, 2, "Title", 4096
End Sub

 

[Darren_workforce]

Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, [A1:C3]) Is Nothing Then _ CreateObject("WScript.Shell").PopUp Target(1).Value, 2, "Title", 4096 End Sub

Bingo!! Many thanks to both of you for your help and suggestions!!

 

[Tetra201]

Glad to have helped.