Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I don't know if this is possible or has been asked before, so if it has been asked I apologize.

    I have users that have Excel, but no Microsoft Access. They have given me a spreadsheet with two sheets (Sheet1 and SpecSheet) to dress up/modify. Sheet1 has 130 columns (something to do with a regulatory agency reporting requirement), three header rows and unlimited detail rows (well not really unlimited, but unlimited within limitations of max rows allowed in Excel).

    SpecSheet has three columns and a row for every column in Sheet 1. Each row contains the Field number from Sheet One, the field name, and description.

    If they get to a column in Sheet 1 and need a better understanding of what should be entered in the column, they want to click, double click, right click or some such on the column heading which would put them on the appropriate row on the SpecSheet.

    I have attached a copy of a stripped down version of the spreadsheet which may better explain the above.

    I can do this relatively easily in Adobe Acrobat, but they prefer and interactive spreadsheet. Is there a way to accomplish this via VBA/macro?

    Thank you for your ideas and consideration.

    Ken
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='kwvh' post='782999' date='04-Jul-2009 11:45']I don't know if this is possible or has been asked before, so if it has been asked I apologize.

    I have users that have Excel, but no Microsoft Access. They have given me a spreadsheet with two sheets (Sheet1 and SpecSheet) to dress up/modify. Sheet1 has 130 columns (something to do with a regulatory agency reporting requirement), three header rows and unlimited detail rows (well not really unlimited, but unlimited within limitations of max rows allowed in Excel).

    SpecSheet has three columns and a row for every column in Sheet 1. Each row contains the Field number from Sheet One, the field name, and description.

    If they get to a column in Sheet 1 and need a better understanding of what should be entered in the column, they want to click, double click, right click or some such on the column heading which would put them on the appropriate row on the SpecSheet.

    I have attached a copy of a stripped down version of the spreadsheet which may better explain the above.

    I can do this relatively easily in Adobe Acrobat, but they prefer and interactive spreadsheet. Is there a way to accomplish this via VBA/macro?

    Thank you for your ideas and consideration.

    Ken[/quote]
    Try Ctrl + j on the attached file. It contains the following macro.
    Code:
     Sub GetHelp()
     '
     ' GetHelp Macro
     ' Macro recorded 7/4/2009 by W. Donald Wells
     '
     ' Keyboard Shortcut: Ctrl+j
     '
     Dim ColNo As Long
     Dim i As Long
     
    	 If ActiveSheet.Name <> "Sheet1" Then Exit Sub
    	 ColNo = ActiveCell.Column
    	 
    	 Sheets("Specs").Select
    	 Cells.Interior.ColorIndex = xlNone
    	 For i = 3 To 1 Step -1
    		 Cells(ColNo, i).Select
    		 With Selection.Interior
    			 .ColorIndex = 6
    			 .Pattern = xlSolid
    		 End With
    	 Next i
     End Sub
    Attached Files Attached Files
    Regards
    Don

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is an other possibility:
    - Right-click the sheet tab of Sheet1.
    - Select View Code from the popup menu.
    - Paste the following code into the module that appears:

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim intNum As Integer
      Dim rngFound As Range
      If Not Intersect(Rows(3), Target) Is Nothing Then
    	Cancel = True
    	intNum = ActiveCell.Offset(-2, 0).Value
    	Set rngFound = Worksheets("Specs").Columns(1).Find(What:=intNum, _
    	  LookIn:=xlValues, LookAt:=xlWhole)
    	If Not rngFound Is Nothing Then
    	  Worksheets("Specs").Select
    	  rngFound.Offset(0, 2).Select
    	End If
      End If
    End Sub
    The user can double-click the column header in row 3 to switch to the Specs sheet, with the description selected.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    OH MY! ! !

    That is AWESOME! Better solution than I had imagined.

    Thank you.

    Ken

    [quote name='wdwells' post='783008' date='04-Jul-2009 12:24']Try Ctrl + j on the attached file. It contains the following macro.
    Code:
     Sub GetHelp()
     '
     ' GetHelp Macro
     ' Macro recorded 7/4/2009 by W. Donald Wells
     '
     ' Keyboard Shortcut: Ctrl+j
     '
     Dim ColNo As Long
     Dim i As Long
     
    	 If ActiveSheet.Name <> "Sheet1" Then Exit Sub
    	 ColNo = ActiveCell.Column
    	 
    	 Sheets("Specs").Select
    	 Cells.Interior.ColorIndex = xlNone
    	 For i = 3 To 1 Step -1
    		 Cells(ColNo, i).Select
    		 With Selection.Interior
    			 .ColorIndex = 6
    			 .Pattern = xlSolid
    		 End With
    	 Next i
     End Sub
    [/quote]

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='kwvh' post='783018' date='04-Jul-2009 13:50']OH MY! ! !

    That is AWESOME! Better solution than I had imagined.

    Thank you.

    Ken[/quote]
    As a third option:
    - Right-click the sheet tab of Specs.
    - Select View Code from the popup menu.
    - Paste the following code into the module that appears:
    Code:
    Private Sub Worksheet_Activate()
    '
    Dim ColNo As Long
    Dim i As Long
    	Application.EnableEvents = False
    	Application.ScreenUpdating = False
    	Sheets("Sheet1").Select
    	ColNo = ActiveCell.Column
    	Sheets("Specs").Select
    	Rows("2:" & Cells.Rows.Count).EntireRow.Hidden = True
    	Rows(ColNo).EntireRow.Hidden = False
    	ActiveWindow.LargeScroll Down:=-1
    	Application.ScreenUpdating = True
    	Application.EnableEvents = True
    	Range("A" & Cells.Rows.Count).Select
    End Sub
    Select the Specs sheet to run the macro.

    Code revised Range("A" & Cells.Rows.Count).Select command moved
    Regards
    Don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Please note that column C on the Specs sheet contains CrLf as line breaks instead of just the Lf that Excel expects. This causes box characters to be displayed. To get rid of these:
    - Activate the Specs sheet.
    - Activate the Visual Basic Editor (Alt+F11).
    - Activate the Immediate window (Ctrl+G).
    - Type or copy/paste the following line:

    Cells.Replace Chr(13), "", xlPart

    - With the insertion point anywhere in the line, press Enter.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Hans. I don't see the box character on my end, but as a precaution, I will run it to make sure to run the code.

    Thanks Don and Hans. Your ideas and help are incredibly valuable.



    [quote name='HansV' post='783029' date='04-Jul-2009 13:56']Please note that column C on the Specs sheet contains CrLf as line breaks instead of just the Lf that Excel expects. This causes box characters to be displayed. To get rid of these:
    - Activate the Specs sheet.
    - Activate the Visual Basic Editor (Alt+F11).
    - Activate the Immediate window (Ctrl+G).
    - Type or copy/paste the following line:

    Cells.Replace Chr(13), "", xlPart

    - With the insertion point anywhere in the line, press Enter.[/quote]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •