Temporary Sheet with Cell Selection
 

Hello Everyone!

I'm trying to get something to happen that I have seen
before but have no idea what it's called or what the code
is, so can't search for a solution.

I have sheet in which I input parameters. Usually I will
just key them in, but some people might not know the full
list so will want to see what's available.

To check what they want to see they will double click a
cell and this will download values from a SQL table. I
would like these values to come up as a seperate sheet
(two columns wide). When the user clicks on one of the
cells on this sheet I need the value selected to populate
the original cell double-clicked.

The code I have so far is below
(On the worksheet I will be double clicking on)
Double clicking cells B4, C4, B8 or C8 will activate the
code.

As below

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As
Range, Cancel As Boolean)

Call PopUpMenu(Target)

End Sub

The rest of the code is as below here. Sorry about the
novel, but I'm struggling to get this any shorter

TIA

Sophie
xx


Public Sub PopUpMenu(ByVal Target As Excel.Range)

On Error GoTo errHandler

Dim StrRanges As Variant
Dim lngItem, lngClickRow As Long
Dim strThisCell As String
strThisCell = Target.Address

'List of ranges that can be double-clicked (I would
prefer to use named ranges but don't know how)
StrRanges = Array( _
"$B$4", "$C$4", "$B$8", "$C$8")

For lngItem = 0 To UBound(StrRanges)
If strThisCell = (StrRanges(lngItem)) Then

lngClickRow = FindRow(StrRanges(lngItem))
'MsgBox lngClickRow
Call DownloadData(lngClickRow)
Exit Sub

End If
Next


errHandler:
'ensure that events have been turned back on
Application.EnableEvents = True

End Sub
Function FindRow(ClickAddress)
'Which row is clicked will determine which data set is
downloaded
'In this example 4 is account code, 8 is Department
FindRow = Application.WorksheetFunction.Find("$",
ClickAddress, 3) + 1
FindRow = Mid(ClickAddress, FindRow, 99)

End Function
Function DownloadData(DataSet)
'Sample of data to be downloaded
'This is what I would like to see in the popup sheet
Dim X

Sheets("Sheet2").Cells.Clear
Sheets("Sheet2").Select

Select Case DataSet

Case 4
For X = 1 To 10
Range("A" & X).Value = "Account Code " & X
Range("B" & X).Value = "Account Code Name
for " & X
Next X
Case 8
For X = 1 To 20
Range("A" & X).Value = "Department " & X
Range("B" & X).Value = "Department Name for "
& X
Next X
End Select

Columns("A:B").EntireColumn.AutoFit
Range("A1").Select

End Function