ExcelBanter - Presentation box with dropdown menu

Hi Anand,

Do you mean a textbox that appears in the vicinity of a clicked on cell
that has a data validation dropdown.

You could try the following...

1. Fill a textbox with your financial data (not too big I hope), then
click on its thick border, where the resizing handles are.

2. Click in the namebox, which is on the left side of the formula bar,
type "MyData" without the speech marks, then press enter. This changes
the textbox's name to the name that the code uses to manipulate its
position and visibility.

3. Copy the following worksheet selection change event code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shpP As Shape
Set shpP = Me.Shapes("MyData")
If Not Intersect(ActiveCell, _
Range("A31,E13,K1,N45")) Is Nothing Then '<<<edit the address string
Dim Pw As Single, Ph As Single
Dim Cw As Single, Ch As Single, Ct As Single, Cl As Single
Dim VRw As Single, VRh As Single
Dim VRt As Single, VRl As Single
Dim blnRoomAbove As Boolean
Dim blnRoomToLeft As Boolean
Dim blnRoomToRight As Boolean
With shpP
Pw = .Width: Ph = .Height
End With
With ActiveCell
Cw = .Width: Ch = .Height: Cl = .Left: Ct = .Top
End With
With ActiveWindow.VisibleRange
VRw = .Width: VRh = .Height: VRl = .Left: VRt = .Top
End With
blnRoomAbove = Ct - VRt = Ph
blnRoomToLeft = Cl - VRl = Pw
blnRoomToRight = Cl + Pw - VRl <= _
Range(Cells(1, ActiveWindow.ScrollColumn), _
Cells(1, ActiveWindow.ScrollColumn + _
ActiveWindow.VisibleRange.Columns.Count _
- 2)).Width
Select Case blnRoomAbove
Case False
shpP.Top = Ct
If blnRoomToLeft Then
shpP.Left = Cl - Pw
Else: shpP.Left = Cl + Cw + _
12 * 100 / ActiveWindow.Zoom
End If

Case True
shpP.Top = Ct - Ph
If Not blnRoomToRight Then
shpP.Left = Cl + Cw - Pw
Else: shpP.Left = Cl
End If
End Select
shpP.Visible = True
Else: shpP.Visible = False
End If
End Sub

4. Right click on the worksheet tab and choose "View Code" from the
popup menu.

5. Paste the code into the Worksheet code module that appears.

6. I don't know which cells you want to trigger the MyData textbox
visible, so you will have to edit the address string...

Range("A31,E13,K1,N45")) Is Nothing Then '<<<edit the address string

found close to the top of the code.

If you leave it as is then MyData will only be visible when you click
on any of the four cells A31, E13, K1 or N45. If you require ranges
A2:A50 and D4:H4 to be the triggering cells then you would change the
line to...

Range("A2:A50,D4:H4")) Is Nothing Then '<<<edit the address string

7. Press Alt+F11 to get back to the Excel user interface.

8. The code operation requires that Workbook Security be at Medium and
that "Enable Macros" be the chosen button on the Security Warning
dialog that appears when the workbook is opened. If Security needs to
be adjusted then go Tools|Macro|Security...|check
Medium|OK|Close|Open|Enable Macros on the Security warning dialog.

Ken Johnson