I have this "library" type file where I have attached buttons on every row in Column A. Each one of these buttons does the same action but ends up in a slightly different viewpoint.
Once the button is clicked this code is ran for each button, (four examples below)
VBA Code:
Sub pressures19099ZN()
Sheets("PRESSURES").Select
Range("U2").Select
ActiveCell.FormulaR1C1 = "19099-zn"
End Sub
Sub pressures19115OSV()
Sheets("PRESSURES").Select
Range("U2").Select
ActiveCell.FormulaR1C1 = "19115-osv"
Range("U3").Select
End Sub
Sub pressures19159zn()
Sheets("PRESSURES").Select
Range("U2").Select
ActiveCell.FormulaR1C1 = "19159-zn"
End Sub
Sub pressures19169zn()
'SELECTS SHEET TO GO TO
Sheets("PRESSURES").Select
'SELECTS THE CELL
Range("U2").Select
'ENTERS IN WHATS INSIDE THE QUOTATION MARKS
ActiveCell.FormulaR1C1 = "19169-zn"
End Sub
- Switches from PARTS sheet to PRESSURES sheet
- Enters in the part number they clicked on from Column A into the search box on the second sheet
- The search bar uses conditional formatting to highlight the line.
- Uses the following code to scroll to the exact row that part number is on (Thanks to Akuini on MrExcel)
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$P$2" Then
Dim c As Range
Set c = Range("A:F").Find(What:=Target, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
c.Activate
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = c.Row
Else
MsgBox "Cant't find " & Range("P2") & " in Part Numbers"
End If
End If
End Sub
I was wondering If it was able to simplify the code I have for each button to one singular macro that could be applied to all the buttons? I'd greatly appreciate any help. If you need a copy of the excel file to work on, I got it ready on a google drive, just let me know.