Macro goto specific cell

Topic

New Reply

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

Reply | Quote

Replies

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

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

Reply | Quote

OH MY! ! !

That is AWESOME! Better solution than I had imagined.

Thank you.

Ken

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

Reply | Quote

OH MY! ! !

That is AWESOME! Better solution than I had imagined.

Thank you.

Ken

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote