none
Macros - Double-click cell to open another excel worksheet and filter content RRS feed

  • Question

  • Hi

    Is anybody able to help me with the below code, I am trying to generate the Project Names from sheet 1 under column E in a table on sheet 2 but cannot seem to stop it from coming up under column D.

    Sheet 1: Column B lists "Project Name 1", "Project Name 2" etc

    Sheet 2: Column E - Is where I want the projects to list in the table; BUT

    They keep showing up under Column D instead.


    Option Explicit


    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim strCellVal As String
    Dim i As Long

        On Error Resume Next
        
        If Intersect(Target.EntireRow, [Projects[Project]]) Is Nothing Then Exit Sub
        If Target.Cells.Count <> 1 Then Exit Sub
        
        strCellVal = ActiveCell.Value
        
        With Sheet2
            .ListObjects(1).Range.AutoFilter 4, Criteria1:=Intersect(Target.EntireRow, [Projects[Project]])
            .Activate
            i = .ListObjects(1).Range.Columns(1).SpecialCells(xlCellTypeVisible).Count
         If i = 1 Then
            .ListObjects(1).Range.End(xlDown).Offset(1, 2).Value = strCellVal
         End If
            .[A1].Select
        End With
            
        Cancel = True

    End Sub

     Thanks


    • Edited by esoramme Sunday, July 24, 2016 12:01 AM
    Saturday, July 23, 2016 9:47 PM

Answers

  • The main problem was not offsetting sufficient columns to right with following line. Should be Offset(1, 3)

    .ListObjects(1).Range.End(xlDown).Offset(1, 2).Value = strCellVal

    A few more pointers.

    No need to assign the value in the cell that was double clicked to a variable. Target.Value contains the value.

    Best to Cancel the Editing mode that is normally invoked with DoubleClick ASAP after confirming that the Double Clicked cell is within the correct range.

    Bad programming to Activate another worksheet from a module that is attached to a worksheet and continue with further processing. While your program does not specifically reference cells on the Activesheet after Activating Sheet2, if this is done then Excel becomes confused about which is the ActiveSheet. It is best to specifically reference the worksheet and the ranges and if you want the worksheet activated then do so at the end of the code. In the modified code below, I have used Application.GoTo which activates the worksheet in the With statement and selects the required range.

    If you copy the following edited code into the VBA editor you will find it easier to see the comments in green.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     'Dim strCellVal As String  'Not required. Use Target.Value
     Dim i As Long

         'On Error Resume Next      'Doubt that this is required
        
         If Intersect(Target.EntireRow, [Projects[Project]]) Is Nothing Then Exit Sub
         If Target.Cells.Count <> 1 Then Exit Sub
         Cancel = True
        
         'strCellVal = ActiveCell.Value     'Not required. Use Target.Value
        
         With Sheet2
             .ListObjects(1).Range.AutoFilter 4, Criteria1:=Intersect(Target.EntireRow, [Projects[Project]])
             '.Activate     'Activate after other processing finished
             i = .ListObjects(1).Range.Columns(1).SpecialCells(xlCellTypeVisible).Count
          If i = 1 Then
             .ListObjects(1).Range.End(xlDown).Offset(1, 3).Value = Target.Value
          End If
                'Activate worksheet in With statement and select required cell
                Application.Goto .[A1]
             '.[A1].Select
         End With
        
         'Cancel = True     'This line moved up to immediately after confirming processing to take place

     End Sub


    Regards, OssieMac

    Sunday, July 24, 2016 11:28 AM

All replies

  • Can't follow without a copy of the workbook. Please upload an example workbook to OneDrive and I will have a look at it for you. If the workbook contains sensitive data then make a copy and replace the sensitive data with dummy data.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click Copy the link and Paste into your reply on this forum.

    Regards, OssieMac

    Sunday, July 24, 2016 1:05 AM
  • Hi OssieMac

    Thank you for your help and instructions; I have copied the link here:

    https://1drv.ms/u/s!Av2-GN6OHPEDgx24lc3XFrYHf9W2 

    Thank you

    Sunday, July 24, 2016 8:57 AM
  • The main problem was not offsetting sufficient columns to right with following line. Should be Offset(1, 3)

    .ListObjects(1).Range.End(xlDown).Offset(1, 2).Value = strCellVal

    A few more pointers.

    No need to assign the value in the cell that was double clicked to a variable. Target.Value contains the value.

    Best to Cancel the Editing mode that is normally invoked with DoubleClick ASAP after confirming that the Double Clicked cell is within the correct range.

    Bad programming to Activate another worksheet from a module that is attached to a worksheet and continue with further processing. While your program does not specifically reference cells on the Activesheet after Activating Sheet2, if this is done then Excel becomes confused about which is the ActiveSheet. It is best to specifically reference the worksheet and the ranges and if you want the worksheet activated then do so at the end of the code. In the modified code below, I have used Application.GoTo which activates the worksheet in the With statement and selects the required range.

    If you copy the following edited code into the VBA editor you will find it easier to see the comments in green.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     'Dim strCellVal As String  'Not required. Use Target.Value
     Dim i As Long

         'On Error Resume Next      'Doubt that this is required
        
         If Intersect(Target.EntireRow, [Projects[Project]]) Is Nothing Then Exit Sub
         If Target.Cells.Count <> 1 Then Exit Sub
         Cancel = True
        
         'strCellVal = ActiveCell.Value     'Not required. Use Target.Value
        
         With Sheet2
             .ListObjects(1).Range.AutoFilter 4, Criteria1:=Intersect(Target.EntireRow, [Projects[Project]])
             '.Activate     'Activate after other processing finished
             i = .ListObjects(1).Range.Columns(1).SpecialCells(xlCellTypeVisible).Count
          If i = 1 Then
             .ListObjects(1).Range.End(xlDown).Offset(1, 3).Value = Target.Value
          End If
                'Activate worksheet in With statement and select required cell
                Application.Goto .[A1]
             '.[A1].Select
         End With
        
         'Cancel = True     'This line moved up to immediately after confirming processing to take place

     End Sub


    Regards, OssieMac

    Sunday, July 24, 2016 11:28 AM