MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do i make VB pop-up box display the answer to a formula


Posted by Phil on June 17, 2001 4:03 AM

Hi

Im trying to get it so that when i press a cell it displays the array details that made up the figure in that cell.

Below is the array

{=SUM(IF(PLAN!$H$17:$H$133=$C8,IF(PLAN!$X$17:$X$133>F$5,IF(PLAN!$X$17:$X$133<I$5,PLAN!$J$17:$J$133,0),0),0))}


Cheers

Phil


Posted by Joe Was on June 18, 2001 11:56 AM

You need to use VB code!
Three ways:
1. build a hot-key macro so you input data and hit the hot key to run the pop-up;

Sub MyBox()
Set x = Worksheets("sheet1").Range("A1")
x.Value = 234567.8
x.NumberFormat = "$#,##_);($#,##0)"
MsgBox x.Value
MsgBox x.Text
End Sub

This will take your input and display the same value first as a number in its own pop-up then when you hit OK. A new box pops-up showing the same data as a text item. To get your data in to this Set your formula to x.

2. Build an on screen button and attach similar code.

3. Change the way your sheet functions by adding; Tab Sheet Code: Right-click the Tab Sheet name and select View Code.

The VB code will need to be attached to the worksheet event. Search this board or its archives for color, caps, underlining, tab or increment for examples. Hit Ctrl-f to pull up the search utility. JSW