Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel sheet: selection of cell or rows not visible. | Excel Worksheet Functions | |||
copy a sheet in same workbook temporary block excel | Excel Worksheet Functions | |||
accessing last cell in selection on sheet | Excel Programming | |||
Cell selection in non-active sheet | Excel Programming | |||
Capture listbox selection to a cell in a different sheet | Excel Programming |