Using Message boxes to display cell content

[CalumJames]

Hi all.

Am i correct in thinking I'm missing something here? I'm trying to find a guide that will text me how to create VBA for a message box which displays the content of an active cell (if clicked once) in Column J of my sheet... however as much as I've searched multiple sites there is very little on this that can assist me but instead talks of comment boxes which I don't want because the message box needs to lead onto two user forms via buttons.

Can anyone confirm if this is possible? I can't see why it isn't? And does anyone possibly have some sample code?

Greatly appreciate any assistance because I'm not getting very far

Thanks

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 10 Then MsgBox Target.Value, vbInformation, Target.Address(False, False)
End Sub

 

[CalumJames]

Thanks - a little further forward. I now need to amend this "box" to add two further buttons.... how do I do this as I don't know where it's pulling these alerts from?

 

[VoG]

Thanks - a little further forward. I now need to amend this "box" to add two further buttons....

Two buttons to do what?

how do I do this as I don't know where it's pulling these alerts from?

What does that mean? Did you look at the title of the message box - it has the cell address.

 

[CalumJames]

Apologies I wasn't very clear. The two Buttons are:

1. to Cancel / close the messagebox
2. The second needs to close the message box & open a userform called: transitonupdates

Ignore the titles I since sorted that.

Thanks again!!

 

[CalumJames]

Does anyone know how to amend these messagebox buttons? I need to work out how to point it to the userform if they select ok

Code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 10 Then MsgBox Target.Value, vbInformation, Target.Address(False, False)
End Sub

And I need to merge this with


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then
If Target = "completed" Then
Application.EnableEvents = False
nxtRow = Sheets("Completed Major Tab").Range("I" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Completed Major Tab").Range("A" & nxtRow)
Target.EntireRow.Delete
End If
If Target = "On Hold" Then
Application.EnableEvents = False
nxtRow = Sheets("IA Register").Range("I" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("IA Register").Range("A" & nxtRow)
Target.EntireRow.Delete
End If
Application.EnableEvents = True
End If
End Sub

 

[VoG]

Try something like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 10 Then
    If MsgBox(Target.Value & vbNewLine & "show form", vbInformation + vbOKCancel, Target.Address(False, False)) = vbOK Then TransitionUpdates.Show
End If
End Sub

 

[CalumJames]

Thanks... that doe help though the button is labelled ok - i can't see a way of changing this easily without having to make a custom message box, but then from my knowledge of VBA I'd loose the expanding menu to fit the content?

Is there an easy way to label the buttons?

 

[VoG]

With a VBA message box you are limited to Yes, No, OK, Cancel. If you need more you would need to create a userform or use an add-in like ktMsgBox http://www.h3.dion.ne.jp/~sakatsu/ktMsgBox_Series_English.htm