Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Presentation box with dropdown menu
i want to make a presentation box that is linked with drop down menu i mean
when i choose any thing in the drop down menu its shows the details in the presentation box. Please help |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Presentation box with dropdown menu
What do you mean by 'Presentation box'?
"Anand vijay" wrote: i want to make a presentation box that is linked with drop down menu i mean when i choose any thing in the drop down menu its shows the details in the presentation box. Please help |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Presentation box with dropdown menu
I mean any object in which i can show my data i.e., financial figures.
"Mike" wrote: What do you mean by 'Presentation box'? "Anand vijay" wrote: i want to make a presentation box that is linked with drop down menu i mean when i choose any thing in the drop down menu its shows the details in the presentation box. Please help |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropdown Menu Hyperlink | New Users to Excel | |||
Create Dropdown menu without using the Validation on the Data Menu | Excel Worksheet Functions | |||
Dropdown Menu | Excel Discussion (Misc queries) | |||
dropdown menu or Jump menu | Excel Discussion (Misc queries) | |||
dropdown menu | Excel Discussion (Misc queries) |