Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with "floating pop up menus" (if that is correct)
Good morning. i need help with the following:
I have a sheet in one of my workbooks that contains some amount of data (it is just too much to include it in a drop down list). what I eventually want to do is when in teh workbook i select a cell it should automatically create a pop up menu where teh person would only be able to select the item required (from the list mentioned previously) and when selected it sould copy the info to the worksheet I am currently working on. Also when the cell in the workbook is selected (and want to have populated) the pop up menu should automatically jump to the list sheet for the particular option to be selected only. hope you understand what I am trying to achieve. Kind regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with "floating pop up menus" (if that is correct)
Hi there,
this might give you something to go on... Put this bit in the sheet code module. ------------------------------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rRangeIfSelectedLaunchPU As Range Set rRangeIfSelectedLaunchPU = Sheet1.Range("C2:F2") 'If you select one of these cells you will lanuch your combo etc. If Not Intersect(Target, rRangeIfSelectedLaunchPU) Is Nothing Then PopupDropDownLanuch ActiveCell End If End Sub Sub PopupDropDownLanuch(SelectedCell As Range) Dim rListOfItems As Range Set rListOfItems = Sheet1.Range("A1:A10") 'This range will hold the items you want to populate the combo with. U could also use an array... UserForm1.Show UserForm1.ComboBox1.RowSource = rListOfItems.Address 'putting this line in here is not very good practice but I did it anyway :) End Sub -------------------------------------------------------------------------------------- Make a userform and put a combobox on it. Make it a little user form just big enough to hold your combobox Put this in the userform code module -------------------------------------------------------------------------------------- Private Sub ComboBox1_Change() SelectRange ComboBox1.Value End Sub -------------------------------------------------------------------------------------- Put this anywhere..... Sub SelectRange(ComboSelectedItem As String) Select Case ComboSelectedItem Case Is = XYZ Sheet(Xy).Range(Z).Select Case Is = ABX etc End Select End Sub -------------------------------------------------------------------------------------- Hope that helps! :) Kind regards, Bernie Russell |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with "floating pop up menus" (if that is correct)
Here is an example
Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("myToolbar").Delete On Error GoTo 0 End Sub Private Sub Workbook_Open() Dim oCB As CommandBar Dim oCtl As CommandBarControl On Error Resume Next Application.CommandBars("myToolbar").Delete On Error GoTo 0 Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True) With oCB Set oCtl = .Controls.Add(Type:=msoControlButton) With oCtl .BeginGroup = True .Caption = "savenv" .OnAction = "savenv" .FaceId = 27 End With Set oCtl = .Controls.Add(Type:=msoControlButton) With oCtl .Caption = "savemyprog" .OnAction = "savemyprog" .FaceId = 28 End With Set oCtl = .Controls.Add(Type:=msoControlButton) With oCtl .Caption = "macro4" .OnAction = "macro4" .FaceId = 29 End With Set oCtl = .Controls.Add(Type:=msoControlButton) With oCtl .Caption = "dater" .OnAction = "dater" .FaceId = 30 End With .Visible = False .Position = msoBarFloating End With End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "mySheet" And Target.Address = "$B$2" Then Application.CommandBars("myToolbar").Visible = True Else Application.CommandBars("myToolbar").Visible = False End If End Sub 'To add this, go to the VB IDE (ALT-F11 from Excel), and in 'the explorer pane, select your workbook. Then select the 'ThisWorkbook object (it's in Microsoft Excel Objects which 'might need expanding). Double-click the ThisWorkbook and 'a code window will open up. Copy this code into there, 'changing the caption and action to suit. 'This is part of the workbook, and will only exist with the 'workbook, but will be available to anyone who opens the 'workbook. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "wally_sa" wrote in message ... Good morning. i need help with the following: I have a sheet in one of my workbooks that contains some amount of data (it is just too much to include it in a drop down list). what I eventually want to do is when in teh workbook i select a cell it should automatically create a pop up menu where teh person would only be able to select the item required (from the list mentioned previously) and when selected it sould copy the info to the worksheet I am currently working on. Also when the cell in the workbook is selected (and want to have populated) the pop up menu should automatically jump to the list sheet for the particular option to be selected only. hope you understand what I am trying to achieve. Kind regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how can i save "floating cells" in an excel document?? | New Users to Excel | |||
"Start a new group" coding possible for a commandbar (floating toolbar)? | Excel Programming | |||
save and restore "Workbook Menu Bar" & "Cell" menus | Excel Programming |