Help with "floating pop up menus" (if that is correct)

[wally_sa]

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

 

[imageswords.br]

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

 

[Bob Phillips]

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)