S
Sophie
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
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